Showing Reporting Services Multi-Value Parameters

Posted by beakersoft | Posted in SQL Reporting Services | Posted on 23-01-2008

27


Param Header

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:

Lots of Params

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

Note: If you are hard coding in your parameter values you cant do this step. You will just have to hard code the number of parameters into the next section

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, “,”))

Note: If you have hard codded the parameters into the list, replace the section of code using the data list with your own number.

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:

Example after codding

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.

Comments (27)

I want to get parameter value in the Field. It is complaining that I can not use parameter value in the fields, My question is Is there any way that I can use parameter value in Field.

Thanks

Jhon, if i’m understanding your question, i think it is down to where on the report you are putting the field. If you are putting the info into a text box and are using actual data from the report you need to put the text box in the main report body, not the header.

The report header is rendered before any actual data is read, so it doesn’t have access to it. Drop the text box onto the main report body and it should work

Hello,

I found the same solution, but when i want to put this value in a textbox in the header, i got the following error : The Value expression for the textbox ‘textbox20’ uses a scope parameter for an aggregate function. The scope parameter cannot be used in page headers or footers.

Did you met the same behaviour ?

Erwan

hi Erwan,
i think your problem is the same as Jhon’s. If the parameter contains data that isn’t available until the report is actually ran you cant put it in the header of the report, it will have to go in the body

Cheers
Luke

That’s great. Thanks Luke. You can then manipulate report items such as hiding a table if the user has not selected a particular parameter value:
=IIF(INSTR(Join(Parameters!StatementName.Value, “,”),”x”)0,False,True)

Cheers

That is great! Took me a minute to figure out that I need a dataset for the value – but exactly what I was looking for! You rock

Whenever “Select All’ is selected, RS generates a list of parameter values to go inside IN clause of underlying SQL query.
I was wondering if we could use the same technique you pointed to set the variable holding the values of parameter selected to be NULL? Because my SQL handles NULL ( :variable IS NULL), setting the null will avoid the expensive operaiton of searching for all the parameter instead of ignoring the filter.
your thoughts?

If you like the ‘All’ code to work in the header you can create a second parameter with the same properties as the par_List. The diffrence is that this parameter should be hidden and have default values set from query.

IIF(Parameters!par_ListTotalCount.Count = Parameters!par_List.Count,”All”,Join(Parameters!par_List.Value, “,”))

Hi,

Have you listed out an interger in a parm display textbox in a header. You can use ‘join’ to show several values selected in a multi value selection parameter, but you can’t for an integer.

If you have any help hints on this, that would be great! Using SRSS2005

Muffntuf, not tried using an integer in the header so i’m not sure how it works. I will have a look and let you know if i come up with anything
Cheers
Luke

Thanks Luke for answering – I did take your code and I got it to work for that integer field. A little finagaling had to happen, but it works and I am very happy with it.

I’ve bookmarked your site, keep up the good work!

Cheers!
muffntuf

I’m not sure about SSRS05, but in SSRS08 this expression will work:
iif(Parameters!myParameter.Count=count(Fields!myfield.Value, “myParameterDataset”), “All”, join(Parameters!myParameter.Label, “, “))

And this will prevent you from having to create a seperate dataset to return the count of parameters.

Thank you Dragonpixiefire for your comment, it is working with SSRS2005 also.

For those of you with working with SSAS:

Public Function PresentParameterValues(ByVal strValue As String) As String
Dim varParams, varParam
Dim strReturnValue

strValue = strValue & “”

If Len(strValue) > 0 Then

varParams = Split(strValue, “,”)

For Each varParam In varParams
If Len(strReturnValue) > 0 Then strReturnValue = strReturnValue & “,”

strReturnValue = strReturnValue & DeriveParameterValue(varParam)

Next
Else
strReturnValue = “None”
End If

PresentParameterValues = strReturnValue
End Function

Public Function DeriveParameterValue(ByVal strValue As String)
Do While InStr(1, strValue, “[“)
strValue = Right(strValue, Len(strValue) – InStr(1, strValue, “[“))
Loop

strValue = Left(strValue, InStr(1, strValue, “]”) – 1)

DeriveParameterValue = strValue
End Function

It worked great! Thanks for publishing

Thank you!

Thanks, that was very useful.

Thanks beakersoft and dragonpixiefire. This is exactly what I needed!

Ah very cool solution. Works great. Thanks for posting.

It took me about half an hour to this to work. Eventually I broke down each of the parameters of your IIF statement and put them into the footer of my table. Once I was able to get each parameter working, I recombined them into a working IIF statement. Then I copied the working IIF statement into a new row above my table header, and Presto! your code was working as my new report header!

TYVM!

hi,

in chart (reporting service) . in title text i need to write expression: if parameter is 1, then title will be “some texts”, if 2 “another test” … how can i write this expession?

For more diagrams and examples of using multivalue parameters, see the blog at http://www.wiseowl.co.uk/blog/s187/multivalue-parameters.htm.

In T-SQL you can also use something like:

(@Param1=” OR
Col1 IN (CASE @param1 WHEN ” THEN ””” ELSE @Param1 END)
)

In SSRS 2008 R2, I want to display all the parameters and its values in a Tablix(Table) in the body area. I have 10 paramters in a report and i want to display that parameter details as 2 columns in a Table (for example columns names of Table – ParameterName and ParameterValue). Each parameters (Multivalued also) as a record in that Table. Is it possible ?

@sudeesh, not sure about that. It kind of sounds like you want to handle the parameters as if they are in a database table.

I have a multi-value parameter in SSRS 2008R2 which has the possibility of having a list of over 1,000 selections in it, depending on cascading parameter selections. I found that selecting over 1,045 items causes the report to return a blank page via the Web URL. Looking at SQL profiler, no data gets sent to SQL Server in this case. As soon as you select 1,045 items or less, the report works perfectly. Also, the report works either way while doing a preview in Report Designer.

Any solution as to how to render a report with more than 1,045 multiple values selected. Also, once you reach this threshold, you cannot access the count property of the parameter either. Is there a way to even determine if Select All is chosen without doing a count comparison??

Thanks.

Thanks its working

Write a comment

Comments links could be nofollow free.