StuffThatJustWorks.com

How To Use Conditional Aggregates In T-SQL

Your DisplayName here! — Thu, 29 Mar 2012 15:00:31 GMT

I found this neat little T-SQL trick recently that was really useful. It is essentially a way to perform a conditional aggregate (i.e. SUM, COUNT, AVG, etc) in MS SQL Server 2008 using a nested CASE statement. Here is an example of how to use it based on the old Northwind database (yes ... Northwind, keepin' it real).


SELECT  SUM(UnitsInStock) AS TotalInventory,
        SUM(CASE
		WHEN UnitPrice > 20 THEN 1
		ELSE 0
	    END) AS TotalInventoryOfItemsWithPriceOver20
FROM	Products

Just in case you don't remember what the Northwind products data looked like, here is an example of the underlying data this query is running against:

How To Pass Multivalue Report Parameters To Subreports In Report Builder

Your DisplayName here! — Wed, 28 Mar 2012 23:57:48 GMT

I was stuck on this for a while, because a Google search didn’t really turn up anything relevant for this. I essentially had a report containing many subreports. The base report had a multivalue parameter that I wanted to pass down to each of the subreports, so they displayed the data to match the selected parameter.

Here is how the multivalue report parameter was configured on the top-level report:

Passing standard, single-value parameters to subreports is typically very straight-forward. You can just drop the subreport on the page, right-click on it, go to Subreport Properties…, and click on the Parameters section. Here is a basic example of how you would pass two simple date parameters to the subreport (they have identical names on both the parent and subreport):

However, you can’t do that with a multi-value parameter. I eventually learned the trick is related to how the values are serialized and so they are presented to the subreport in the expected format. Essentially it must be passed as an array of strings if you want the subreport to understand it, and you need to write a short expression to do that:

=Split(Join(Parameters!RegionIDs.Value,","),",")

We will break down what the script is doing and why it works:

Parameters!RegionIDs.Value = By default this call by itself will result in an error (yeah, not what you might expect). If you tried something like Parameters!RegionIDs.Value(0) it would work, but you would only get the first value selected and if none were selected you would get an error. Apparently at this level the value is pretty useless, but stick with me.
Join(--Results From #1--,",") = This will essentially convert whatever the original data type was into a comma-separated string of the selected values. Since my example was a bunch of integers, the value of this statement might produce something like “1,2,6”.
Split(--Results From #2--,",") = This will convert convert that comma-separated string (e.g. “1,2,6”) back out into an array, but this time it is an array of strings. I’m not sure what the data type was of the original array, but apparently it wasn’t something the subreport report was expecting. But by converting it to an array of strings, the subreport should work as expected now.

Here is a good resource for more info on using parameters: http://technet.microsoft.com/en-us/library/dd207127(v=sql.100).aspx

How To Fix: SQL 2008 Error - Saving changes is not permitted. You have enabled the option to Prevent saving changes that require the table to re-created.

Your DisplayName here! — Tue, 21 Dec 2010 20:15:57 GMT

I ran into this issue a few times, and finally decided to figure out what was going on. Apparently in SQL Server 2008 they made some changes to the default behavior of the designer, so that it didn't apply any changes that required a table to recreated. This was primarily changed because when a change is made that does require the table to be recreated, SQL has to copy all of the data already in that table to a temporary location, recreate the table with the new changes applied, and then copy the data back over from the temporary location. If the table contains a large amount of data this can take some time, and essentially freezes up management studio while it executes. So their fix was to just make that behavior disabled by default, and require users to opt-in if they needed it.

Here is the error message window it shows when this is your problem:

Saving changes is not permitted. The changes you have made require the following tables to be dropped and re-created. You have either made changes to a table that can't be re-created or enabled the option Prevent saving changes that require the table to be re-created.

The fix is to just go disable that option, and to do that you just go to Tools > Options in SQL Management Studio. Then click on the Designer section.

Unselect the checkbox with the text "Prevent saving changes that require table re-creation" as shown above, and click OK. Try to re-save the changes to your table, and it should work.

How To: Control Your ViewState in ASP.NET 4.0 (EnableViewState vs. ViewStateMode)

Your DisplayName here! — Fri, 23 Jul 2010 20:06:36 GMT

One of the biggest drawbacks of using ASP.NET is the way it can really bloat a page size with ViewState. That is because http and the web in general are stateless by nature, but when creating applications developers often need to track what changed on a form ... hence ViewState was introduced to essentially let the development code as if the web was stateful. Although some left-wing purist might argue, my more pragmatic nature causes me to think this was a good thing overall because of the dramatic increase in productivity it offers. However, the downside was bloated pages to the client ... but now ASP.NET 4.0 offers a better way to control that.

Pre 4.0, ASP.NET offered the EnableViewState property which you could put on a page or control. If you set that value to false, it disabled all viewstate for that control and any child controls. The problem was you didn't have the ability to override that behavior in any of the child controls. So this was more of a system where it had to be enabled at a high level, but then disabled in an opt-out fashion where you didn't really need it. The result was that people just didn't opt-out. That isn't because they were lazy (well, not completely) ... its just that pattern doesn't encourage good practices.

ASP.NET 4.0 includes a new ViewStateMode property that is completely different than EnableViewState. It allows you to set the overall viewstate for a site off by default, but that can be overridden at any level below that. This creates an opt-in pattern. When I first tried to confige this setup, I ran into a few problems because I was trying to mix EnableViewState with ViewStateMode.EnableViewState always trumps the ViewStateMode setting. I think it is much simpler to not mix the two and just avoid the old school EnableViewState property all together, and solely rely on ViewStateMode to control your ViewState.

The ViewState Opt-In Pattern Using ViewStateMode

Set the site's masterpage(s) to have a ViewStateMode setting of "Disabled". Unfortunately you can't set that property at the page level in the web.config, but disabling at the master page level should have a similar site-wide effect.

<%@ Master Language="C#" ViewStateMode="Disabled" ...

You could optionally set the ViewStateMode="Disabled" on one or more of the content placeholders in your master page:

<asp:ContentPlaceHolder ID="ContentPlaceHolder1" ViewStateMode="Disabled" ...
Then, when you need viewstate on a certain page or control ... just set ViewStateMode="Enabled" at that location. It is usually very obvious when you need this when you are developing the site from scratch, but this type of pattern can be more difficult to retro-fit into an existing app. Essentially the things that typically need ViewState are controls that you will need to access the values of on a PostBack to the server (e.g. textboxes, drop downs, checkboxes, etc).

One tricky thing I have ran into is that you can't set the ViewStateMode at the page level in the Page directive like this (at least when using master pages):

<%@ Page Title="Dynamic Schedule" ViewStateMode="Enabled" ... // WON'T WORK

Instead you should preferrably set it for the individual controls that need it that way only the ViewState data that is required would be sent to/from the client, and the page wouldn't be bloated with unnecessary ViewState data.

<asp:CheckBoxList ID="cblProducts" ViewStateMode="Enabled" ...

If you have many controls that you need to enable ViewState on, and you didn't want to have to set the property on all of them ... you could optionally just enable viewstate for an entire content placeholder section like this:

<asp:Content ID="Content1" ContentPlaceHolderID="ContentPlaceHolder1" ViewStateMode="Enabled" ...

Here are a few more helpful resources on the new ViewStateMode functionality:

http://msdn.microsoft.com/en-us/library/system.web.ui.viewstatemode.aspx
http://weblogs.asp.net/sreejukg/archive/2010/04/06/viewstatemode-in-asp-net-4-0.aspx
http://www.mostlylucid.net/archive/2009/01/28/1312.aspx

How To: Search Definitions of All SQL Procedures, Functions, & Views Using T-SQL

Your DisplayName here! — Tue, 13 Apr 2010 12:45:29 GMT

This is a T-SQL code snippet I find myself using regularly to find all references to a particular field or table, or search for comments from a particular date. It essentially allows you to search for a keyword in all of the definitions for stored procedures, functions (scalar or table-valued), and views. It uses some of the built-in methods SQL Server provides for obtaining metadata, which are essentially simplified views of the data contained in system tables like sysobjects and syscolumns. I know this T-SQL script works on SQL Server 2005 & SQL Server 2008, but don't know if the methods it is dependent on were available on previous versions.

DECLARE @Keyword varchar(64)
SET @Keyword = '%keyword%'

(
  SELECT  [ROUTINE_TYPE] AS 'Type', [SPECIFIC_NAME] AS 'Name'
  FROM  INFORMATION_SCHEMA.ROUTINES
  WHERE  [ROUTINE_DEFINITION] LIKE @Keyword
UNION
  SELECT  'VIEW' AS 'Type', [TABLE_NAME] AS 'Name'
  FROM  INFORMATION_SCHEMA.VIEWS
  WHERE  [VIEW_DEFINITION] LIKE @Keyword
)
ORDER BY [Type], [Name]

Here is an example of the results of the script:

Default Excel Chart Colors - Easy Colors To Tell Apart

Your DisplayName here! — Tue, 06 Apr 2010 12:50:28 GMT

During my career, I have written a few components that used colors to visually encode data. Recently I found myself looking at a color chart and trying to choose colors that were easy to distinguish from one another ... again. It seems like I have done that a few times during my career, so this time I wanted to put it to bed for good. Instead of relying on my unscientific approach, I figured this had to be a problem that someone solved before ... and Excel came to mind.

When you create a chart in Excel, it automatically assigns each series a different color. Microsoft almost certainly poured a ton of research/money into this same problem I was trying to solve:

Which set of colors are easiest to differentiate from one another?

They probably also took visual disabilities into account, like color blindness (which I have) and poor vision. So why reinvent the wheel with this stuff? Here is a pie chart that displays default color series Excel 2007 automatically assigned to a simple pie chart, which includes the HEX and RGB values for each series color:

A few things to keep in mind

Here is an helpful excerpt from Information Dashboard Design (a very research driven book) on this topic: "When organizing data into distinct groups using different expressions of any preattentive attribute, you should be careful not to exceed five distinct expressions. ... When using hue, keep in mind that even though we can easily distinguish more than five hues, short-term memory can't simultaneously retain the meaning of more than about nine in total."
When using colors to visual encode information, think about what it would look like if printed in black and white. Would you still be able to distinguish between the different series or groups, or would that encoding be lost without color? Although the project you are working on may never be printed black and white, almost 10% of males have some type of color blindness ... so thinking through this scenario can help you understand their perspective. Usually adding a label (like the numbers in the example above) or patterns (e.g. stripes) in addition to color would be enough to enable someone to make sense of the encoding, even if they aren't able to process it preattentively like the color coding.

Here is a C# code snippet that I use, which returns a list of the related colors outlined above:

/// <summary>
/// Returns a list of colors derived from the defaults Excel uses for chart series.
/// These colors should be very easy to differentiate from one another.
/// </summary>
public static List<Color> DefaultExcelColors
{
    get
    {
        return new List<Color>()
            {
                Color.FromArgb(69,114,167),
                Color.FromArgb(170,70,67),
                Color.FromArgb(137,165,78),
                Color.FromArgb(113,88,143),
                Color.FromArgb(65,152,175),
                Color.FromArgb(219,132,61),
                Color.FromArgb(147,169,207),
                Color.FromArgb(209,147,146),
                Color.FromArgb(185,205,150),
                Color.FromArgb(169,155,189)
            };
    }
}

How To: Fix LINQ to SQL DBML Issue "The connection property of the web.config file is missing or incorrect"

Your DisplayName here! — Wed, 20 Jan 2010 15:22:50 GMT

A few weeks ago, I started noticing that every time I opened a LINQ to SQL .dbml file in Visual Studio 2008 I would get an alert message that said "The connection property in the web.config file is missing or incorrect. The connection string from the .dbml file has been used in its place."

The dbml file was configured to reference a connection string in the web.config file, but after I got this alert and clicked OK, Visual Studio would check out the project and add a new Settings.settings file under the Properties folder and generate a new connection string there, and then change the dbml file to reference that instead of the web.config.

This would probably be the behavior you would expect if the web.config was really missing the connection string, but in my case the referenced connection string was there and well-formed. I know this, because the related code was under source control and other developers could open the same dbml file (from the shared code base) and they wouldn't get the alert and everything worked flawlessly.

To me, it seemed like the LINQ to SQL designer on my machine was simply having trouble reading from the configuration file ... so I thought it might be an issue with a particular dll or registry entry. Another thing that convinced me of this was the fact that when you were in the dbml editor ... under the Connections property on my computer, none of the connection strings from the web.config were included in the drop down to select from. But, when another developer would look at this on their machine (using the same exact code base and files), their Visual Studio environment would find those connection strings in the web.config and include them in the list to select from.

I figured out that I could just undo the changes to add the new file, and then view the differences of the dbml file's XML and undo the reference change to make it once again reference the web.config instead of the settings file it created ... and that worked fine for a couple weeks, but eventually jumping through those extra hoops that "should just work" got pretty old.

So I first tried to repair my Visual Studio install, but that didn't work. Next, I completely uninstalled Visual Studio and then re-installed it and the related service packs ... but that didn't work either. I was about to just format my machine and re-install Windows ... but I thought I would give Microsoft Support a chance, hoping they could help me. I knew this was an obscure enough problem that they probably wouldn't be much help, but I really hate paving my machine ... so it was worth a little time to give them a chance. After explaining what was going on to a technician, we tried to use ProcMon to figure out what was going on behind the scenes. We noticed several registry errors, and that further convinced me that the problem was corrupted or missing registry keys. The support technician said he would have to look into it more, but I wasn't going to count on them as my only hope for a solution ... so I kept investigating myself. Apparently when you uninstall Visual Studio, it doesn't remove all of the registry keys. It also doesn't overwrite them all when you run a repair. It doesn't even remove them all when you remove all the other software that might have hooks or add-ins related to Visual Studio. So these corrupted or missing registry keys could have persisted through the repair and re-install.

Here are some of the issues we noticed in ProcMon (it is a snapshot of what was happening when I would open the dbml file and the pop-up alert would appear, and then I would click OK). The one highlighted is the what I was guessing was the culprit, because that missed registry key occurred right between the read of the dbml file and the opening the related project (which I am guessing was to create the new settings file). You can ignore the "Fast IO Disallowed" stuff, because that is unrelated.

So, although the support technician had actually explicitly advised me not to do this ... I decided to uninstall Visual Studio (and any other software add-ins that might be related to it), manually delete the related registry keys contained in any folder that appeared related to Visual Studio, and then do a fresh install. I never think hacking in the registry is a good solution to a problem, but this was my last ditch effort ... and I was resolved to do a full re-install of Windows if it didn't work. To my surprise ... it actually worked.

I can't guarantee this will work for you, or even recommend it ... but here are the registry folder locations I manually deleted:

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\VisualStudio
HKEY_CURRENT_USER\SOFTWARE\Microsoft\VisualStudio
HKEY_CURRENT_USER\SOFTWARE\Microsoft\VSCommon
HKEY_CURRENT_USER\SOFTWARE\Microsoft\VSTAHost

Hopefully if you are still reading, this is the exact problem you are having and this will work for you as well. The only thing I can say is ... it worked on my machine, and it saved me from having to completely re-install Windows to fix the problem.

How To: Fix Issue With Excel Not Automatically Updating Formula Calculations

Your DisplayName here! — Thu, 14 Jan 2010 16:32:59 GMT

We recently ran into a problem up at work where people were opening a particular spreadsheet from a network fileshare, which had a lot of formulas that were tied to a few inputs. You would typically change one input cell, and a lot of the other related cells would be recalculated and updated automatically. However, a wierd issue started occuring where some users would change the input and nothing would happen. But ... when they saved the worksheet, all of the related calculated cells would be updated at that time.

I think the issue was related to one person opening the spreadsheet on a Mac that was running Office 2008, and when they saved it we noticed it changed a lot of subtle things like the colors or formatting ... but I think it may have also changed the formulas to be manually updated and only automatically updated when the file is saved, which is an Excel Option. Regardless of how this issue occurred ... here is the fix:

Open Excel, and click on the icon in the top left of the window, then go to Excel Options.
Then go to the Formulas tab, and change the Calculation options to Automatic, and hit OK to apply the changes.

How To: Format Dates & Times in SSRS Report Builder

Your DisplayName here! — Tue, 29 Dec 2009 21:06:08 GMT

I was recently using SQL Server Report Builder, and needed to format a date and time in a specific way. I went to edit the related expression, and looked for built-in functions to help me work with dates under Common Functions > Date & Time ... where I found FormatDateTime:

This is the example shown for how to use it:

=FormatDateTime(Fields!BirthDate.Value, DateFormat.ShortDate)

This is helpful, because the 2nd argument is obviously a DateFormat enumeration ... but what are the other members besides ShortDate? I searched (like you might have) and couldn't come up with anything. The only thing I can assume, is that the DateFormat enumeration used in SSRS expressions is the same as the DateFormat enumeration in Visual Basic, which is:

GeneralDate
LongDate
ShortDate
LongTime
ShortTime

None of these "canned" formats matched the way I wanted to format the date, so I kept looking and noticed some MSDN samples that used the Format method instead of the FormatDateTime method. In the expression edittor, that function can be found under Common Functions > Text:

The Format method is very flexible, and allows you to use any of the standard string-based format patterns you are probably already familar with. Here are a few common examples:

=Format(Fields!myDateTime.Value, "M/d/yy") ... 6/15/09
=Format(Fields!myDateTime.Value, "M/d/yyyy h:mmtt") ... 6/15/2009 2:45PM
=Format(Fields!myDateTime.Value, "MM/dd/yy HH:mm") ... 06/15/09 14:45
=Format(Fields!myDateTime.Value, "MMM d, yyyy") ... Jun 15, 2009
=Format(Fields!myDateTime.Value, "Short Date") ... 6/15/2009
=Format(Fields!myDateTime.Value, "Long Date") ... Monday, June 15, 2009

Of course, the standard format strings like "Short Date" and "Long Date" may vary depending on the culture set on your system. The examples above reflect the "en-US" configuration.

Then, here is a slight variation of the 2nd sample. It is my personal favorite and how I usually format all dates that have time values related to them, because I think the lowercase AM/PM designator makes the value easier for someone to scan, and requires less conscience effort to process. If it is all uppercase, the AM/PM designator seems to blend in with the rest of the text and you have to pay a little closer attention to interpret the related value (even if it is just a tenth of a second).

=LCase(Format(Fields!myDateTime.Value, "M/d/yy h:mmtt")) ... 3/15/2009 2:45pm

How To: Link Report Item To Dataset in SSRS Report Builder

Your DisplayName here! — Mon, 28 Dec 2009 17:36:38 GMT

I was working on a report in Report Builder 2.0 for SQL Server 2008 Reporting Services, and added a textbox to an existing report. I wanted to display some summary info related to the data shown in the report. I added a textbox, then went to edit the “Expression” property of that report item, but wasn't able to select any fields because it said “Report item not linked to a dataset.”

So I tried to find a property on the textbox related to a "Dataset" (like the "DataSetName" property on Tablix elements), but the only thing I found was a “DataElementName” property … which I tried, but didn’t work.

I then learned you can link to a dataset by defining it’s name directly in the expression, like this:

=Count(Fields!Address.Value,"DatasetName")