How to have a Select All Multi-Value Cascading Parameter

I’ve seen several cases that involved Multi-Value Cascading Parameters and how the behavior of those work.  Let me start with the default (Out of the box) behavior.  The example report I’ll be using will make use of the AdventureWorksDW sample database.  I will also be using SQL 2008 R2 November Release, but the same will apply to SQL 2008 Reporting Services as well.

In my report, I have three multi-value parameters.  Each one dependent on the parameter before it (cascading parameters).  The parameters are Category, Subcategory and Product, in that order.

As you can see above, I have Bikes selected for the category which will select all by default as that is my first selection.  But what happens if I now add in another category?

Only the first three are selected after adding Components to the category list.  We have seen where people feel this is a bug, but that was actually intended.  We will only maintain what your current selection is.  On the first selection, we didn’t have anything, we actually didn’t have a dataset yet because I didn’t have it loading by default.  But that initial selection will be Select all.  After that, it will be based on what you have selected.  Removing entries will make it seem that we still select all, but that is because the items left were still selected previously, so nothing really changes.  But adding items to the mix will cause them to not be selected by default.  Only the original selection will remain.

So, then the question comes in, what if i want them to all be selected whenever it gets refreshed?  We currently do not provide for that ability out of the box, but I have a way you can add the functionality into your report. 

I will add a disclaimer here.  The code that you will see in this blog has not been fully tested and should not be used in production.  Please be sure to review it first and determine how it will affect performance within your report.

Subcategory

Lets start with the Subcategory parameter as there is nothing we need to do with Category.  To start, you will want to add the following into the code section of the report.  This can be found by going to Report on the Menu bar and selected Report Properties.

Function EncodeParamValue(ByVal ParentParam As Object(), ByVal ChildParam As Integer) As String

    Dim i As Integer
Dim EncodedChildParam As String = ""

    'Concatenate the ParentParam as the prefix
For i = 0 To ParentParam.Length - 1
If i = 0 Then
EncodedChildParam = CStr(ParentParam(i))
Else
EncodedChildParam = EncodedChildParam + "-" + CStr(ParentParam(i))
End If
Next i

    'Add the ChildParam at the end
EncodedChildParam = EncodedChildParam + "_" + CStr(ChildParam)

    Return EncodedChildParam

End Function

Function DecodeSubcatParamValue(ByVal SubCategoryIDs As Object()) As Object()

    Dim i As Integer
Dim SubCategoryID As String

    'Remove the prefix on the parameter values to restore the original value
For i = 0 To SubCategoryIDs.Length - 1
SubCategoryID = SubCategoryIDs(i)
SubCategoryIDs(i) = SubCategoryID.Substring(SubCategoryID.IndexOf("_") + 1)
Next i

    Return SubCategoryIDs

End Function

These two functions will be what we use to alter the parameter values.  Essentially saying that the value includes everything.  But we strip it out when we actually want to use it.

The next step is to modify the dataset for Subcategory to make use of the EncodeParamValue function.  We will add a Calculated Field.  You can do this by going to the properties of the DataSet, selecting the Fields section and click on Add, select Calculated Field.

We will call this field ModifiedSubcategoryKey.  This name is based on what the value field was we were using for the Parameter itself which was the ProductSubcategoryKey field.  So, we are just modifying that field a bit.  The expression for that field will be the following:

=Code.EncodeParamValue(Parameters!Category.Value,Fields!ProductSubcategoryKey.Value)

We are using the EncodeParamValue to take in the Category (1st Parameter) list and tack it onto the SubCategoryKey. We then need to adjust the Available Values and Default Values to use the ModifiedSubcategoryKey instead of ProductSubcategoryKey.  This is done in the Parameter Properties for Subcategory.  You will want to change the Value Field to be ModifiedSubcategoryKey.

The last thing we will do for Subcategory is to change the refresh value to “Always Refresh”.  This is done in the Parameter Properties for Subcategory under the Advanced section.

That is all you need to get the Subcategory parameter drop down to work correctly.

Product

Now we need to make sure Product works with the SubcategoryKey, as it expects that as a parameter for its dataset.  It expects an Integer and right now we are giving it bit string. Without changing anything, you will see the following error:

We need to make use of our Decode function as the Parameter Value expression for the Product Dataset.  This is done in the Product Dataset properties under the Parameters section.  The expression we will use is the following:

=Code.DecodeSubcatParamValue(Parameters!Subcategory.Value)

This will take in the big string we created, and break it up to just give us the value we really want.  This allows the Product Drop down to show values, but in order for the Select all behavior to work, we need to use the Encode method as well.  So, we will add a Calculated Field to the Product Dataset called ModifiedProductKey.  The expression for this field will be the following:

=Code.EncodeParamValue(Parameters!Subcategory.Value,Fields!ProductKey.Value)

We then want to change the Available Values and Default Values for the Product Parameter.  This is done in the Parameter Properties for Product.  We want to change the Value Field to ModifiedProductKey.  This will get the Product Drop down to have the Select All behavior we are looking for.

This will introduce two levels of encoding on the product value, so I created a second Decode method which will strip it twice for use in the Report Dataset.

Function DecodeProductParamValue(ByVal ProductIds As Object()) As Object()

    Dim i As Integer
Dim ProductId As String

    'Remove the prefix on the parameter values to restore the original value
For i = 0 To ProductIds.Length - 1
ProductId = ProductIds(i)
'The first one removes the Category encoding
ProductId = ProductId.Substring(ProductId.IndexOf("_") + 1)
'The second one removes the SubCategory encoding
ProductIds(i) = ProductId.Substring(ProductId.IndexOf("_") + 1)
Next i

    Return ProductIds

End Function

Report Dataset

The last thing to take into account is the Dataset for the report itself.  You will need to use the Decode methods for the parameters within the Report Dataset like we did on the Product Dataset for the parameter listing. You will need to do this for both the SubCategory Parameter value as well as the Product Parameter Value using DecodeSubcatParamValue and DecodeProductParamValue respectively.

 

Hopefully this will help you to get the desired behavior you are interested in.

Adam W. Saxton | Microsoft SQL Server Escalation Services

Comments

• Anonymous
  March 01, 2010
  Found a much easier way to do that.

• Anonymous
  March 10, 2010
  hI Please let us know your easy way to do this, as I am looking for the same issue...

• Anonymous
  March 16, 2010
  I haven't tested it yet. But SQL 2008 RTM + CU#5 or SP1 + CU#3 and above seems to fix the issue as per the forum: http://social.msdn.microsoft.com/Forums/en-US/sqlreportingservices/thread/a55b7203-556b-4d63-9f5c-539c29539f41

• Anonymous
  September 02, 2010
  I am using this - it works on some blank reports i have. Thank you.  However, on the report I really need to put this on the filter(that is dependent on the previous filter) keeps getting disabled when I use the calculate field in the parameter properties.  Do you know why that would happen? Thank you.

• Anonymous
  October 14, 2011
  The comment has been removed

• Anonymous
  April 11, 2012
  Hi, Please help me out in finding the solution to my problem. This is very urgent. I am creating a AX 2009 SSRS report. In the report I have two parameters, based on the first parameter which is a drop down , the second parameter needs to be filtered with specific values. For eg AccountNum is the first parameter which displays all the CustomerId. Based on the AccountNum selected, the second parameter should display the AppointmentNumber. Can you let me know how to achieve this scenario. Is this scenario possible in AX 2009 SSRS? Thanks & regards, Ankita

• Anonymous
  April 11, 2012
  Hi Ankita, For second parameter to be cascaded on first one you simply need to add a where clause in your query. For eg: Select AppointmentNumber from <<table_name>> where CustomerId in (@parameter1) Now,

1. Place this query in the dataset.
2. Use this query for your second parameter. Hope this helps. Thanks

• Anonymous
  July 16, 2012
  I am providing complete solution on my blog neerajsoft.blogspot.in/.../multilevel-cascading-with-select-all.html

• Anonymous
  September 24, 2012
  How to add cascading parameters to Reports using Sharepoint in SSRS???

• Anonymous
  June 02, 2013
  This is a good article, concise and a good solution in lieu of an SSRS feature that would provide additional properties to do this without the need for additional code. NB

1. SSRS/Report Builder need to make the designer more powerful so multiselect tree structures are easier to do.
2. MSDN documentation needs to make their articlesas good as this one i.e. concise, real example, pictures step by step, cut and paste code that works.
3. Hire me  to sort out the MSDN documentation. It would be an easy job to improve on such bad work.

• Anonymous
  August 22, 2013
  Hi, I tried it all and still can not made PARENT parameter work selecting Multi Values ( more then 1), no problem with with child selection, error message says: <An error occured druing local report processing... An expression of non-boolean type specified in a context where a condition is expected, near ','.> I made it work in Query Designer OK with multiple Values, putting string into table delim by ',' and I have <WHERE parent_category in (Select * from #temp1).  I think SSRS still can't interpret  IN and want   AND or OR after each value). Ufff... tried it all, can anybody from MS advice, I'm on 2008 SQL Tx V

• Anonymous
  September 14, 2015
  Parameters are getting disabled can anyone please provide the solution.