One of the big advantages SQL reporting services 2005 has over 2000 is the ability to use Muil-Value parameters (I cant really believe it was missing from 2000)

The only problem with them is showing the values of the parameters you have selected on the report, or showing and ‘ALL’ value if the user has selected all available ones. This is a walk through of how to do it, although I’m sure there is a better way out there.

First of all were are going to assume you have a big lift of parameters (say 20+), if you selected them all then you would get a huge ugly list where you show them on the report probably looking something like this:

Now, this does not look very nice on the report, will probably make the report go onto 2 pages when it would fit on one etc. What we want to do in this situation is just output the word ‘ALL’. In order to do this you need to create a new dataset based on the number of possible values in the parameter, and add some logic to the text box that displays the values

Creating the data source

First of all create a new dataset and call it data_param_count or similar. Then copy the sql from what you use to get your parameter option, and instead of selecting all the fields use a SQL select count query to just return the number of rows, eg:

Select Count (*) from tbl_params

This should return you just one row from your table that contains the number of parameters that you can select. We can then use this in our text box to find out the number of parameters we are dealing with.

Displaying the parameter Value

Now that you know how many parameters you are dealing with, you are ready to output them. Drop an text box onto your report, at the top of the body section (you cant drop it into the header as when the header is rendered the data sources have not yet ran). Right click on the new text box and select Expression to open up the expression editor.

You now need to tell the text box to display the contents of the parameter, something if there are too many to display (such as ‘Cannot display all’) and something for if they are all selected (such as ‘ALL’). In order to do this you need to use a couple of functions built into reporting services IIF and Join, these will allow us to check the number of parameters and output what we want. Here’s a code example:

= “Param: ” & IIF(Sum(Fields!ID.Value, “data_param_count”) = Parameters!par_List.Count,”All”,Join(Parameters!par_List.Value, “,”))

So, what’s happening in this line of code?

• First of all we use an IIF to see if the overall number of parameters available to select is equal to the number the user has chosen. We can get how many have been selected using the count property of the parameter object
• Next if the part of the IIF that will run if the statement is true. So here if the count of selected params is the same as the number available we output the string ‘ALL’
• The final part is what happens if the statement is not true. We use the Join function to output a list of all the parameters the user has selected, separated by a comma. Now instead of a long list if you select all the available values, you will get this:

That’s far neater than just outputting a long list. If you wanted to show something else in a case where lots of values(but not all) have been selected, you could just add another IIF statement into the above in the place of the false string that could check for something like more than 10, and display ‘To many to Display’ or something similar.

Explorer.exe, the shell we spend most of our lives using. Normally (remember this qualifier) it tends to be quite stable, but yesterday a colleague of mine started to have strange problems with it on his XP SP2 machine.

If you clicked on Start -> Run and started to type in the UNC path to a server (as you do 100 times a day) after the two slashes ‘\\’ explorer would inexplicably crash and restart. As is the case with such thing, there didn’t seem to be a logical explanation as to what was going on.

The only new software that we had been recently messing about with was from http://portableapps.com and as that gets installed onto a portable drive (thats kind of its point) we are pretty sure it wasn’t that.

When I got back in the following day, it was working again. What was the solution? Well, clearing up the History and Cache on the machine. That was it. There must have been something the the most recently used list of run, that was causing the problem. As it only occurred after typing in the leading slashes, it must have been a computer name. Unfortunately as the list was cleared we have no idea what the hell caused it.

This what what was logged into the event log on the pc:

We had a Google around for the error but didn’t really come up with anything, so if all else fails, delete your history and clear your cache!

As Microsoft are trying to still push the world onto Vista, most of use continue to use Windows XP. I hadn’t really heard much about it, but XP service pack 3 is now at the release candidate stage. You can download it and read more about it here

As you’d expect, it’s not got very much new functionality (you need vista for that). It seams mainly to be a roll up of all the fixes since SP2, there are however a couple of new bits that might be useful in the enterprise:

• Black Hole Router detection (routers that are silently dropping packets)
• Network Access protection (NAP) support (quite a cool idea, never heard of it before)
• Ability to run a Windows install without entering the product key. It will prompt later as part of the genuine advantage stuff (you would have to be installing of an image with SP3 on it to do this)

I’ve not downloaded it yet, so I don’t know personally how well it works, but if you have a look on http://exo-blog.blogspot.com/2007/11/update-re-testing-vista-w2gb-ram-office.html there is a suggestion that it might actually make things run a bit faster, but ill believe this when I see it.

If you are not part of the Microsoft universe, on Friday (the 11th) there is a new version of the KDE desktop manager being released. It will be version 4 and has some cool new features, check out http://www.kde.org/ for more info.

There is a rather nifty piece of open source software called Ghostscript, that allows you to do various cool things with postscript files.

Working at a newspaper we use postscript files a lot, so I have used Ghostscript for various purposes in the past, along with the printer port redirection application Redmon. Redmon lets you create a new printer port, then re-directs the output of the printer to where ever you want. Using this you can call Ghostscript and create a PDF printer. There’s a good how to of this at http://www.stat.tamu.edu/~henrik/GSWriter/GSWriter.html

If you follow this walk through, it will work fine for most situations. The only problem is it prompts for a file name after every file is printed, so if you have a process that generates a lot of print files one after each other, you want it to name the files on its own. This was the situation I was in.