CSS SQL Server Engineers

This is the official team Web Log for Microsoft Customer Service and Support (CSS) SQL Support. Posts are provided by the CSS SQL Escalation Services

How to have a Select All Multi-Value Cascading Parameter

How to have a Select All Multi-Value Cascading Parameter

Rate This
  • Comments 11

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.

image

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?

image

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.

image

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

image

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.

image

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.

image

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

image

image

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:

image

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

Leave a Comment
  • Please add 1 and 8 and type the answer here:
  • Post
  • Found a much easier way to do that.

  • hI

    Please let us know your easy way to do this, as I am looking for the same issue...

  • 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

  • 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.

  • After changing selection in the parent parameter, some multivalue parameters select all components. I wish there was a consistent behavior. Any idea why?

  • 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

  • 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

  • I am providing complete solution on my blog

    neerajsoft.blogspot.in/.../multilevel-cascading-with-select-all.html

  • How to add cascading parameters to Reports using Sharepoint in SSRS???

  • 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.

  • 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

Page 1 of 1 (11 items)