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 iReturn 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 iReturn 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