Posted by beakersoft | Posted in SQL Reporting Services | Posted on 23-01-2008
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.