SSRS Cascading Parameters Refresh: Solved

There is a long-standing issue with cascading parameters in SSRS – when changing the selection of the “parent” parameter the default selection of the dependent parameter does not always get refreshed. This is closed as “By Design” by Microsoft in the corresponding Connect item:

https://connect.microsoft.com/SQLServer/feedback/details/268032/default-does-not-get-refreshed-for-cascading-parameters

The reason given is that since the users may have changed the selection in the dependent parameter drop-down we do not want to overwrite their changed with the default values every time they select something else in the parent parameter. This is a valid reason, but in some cases it is actually desirable and expected for the defaults to change. Since the refresh behaviour cannot be controlled in SSRS the only way to deal with a requirement specifying a complete refresh was to tell the users it was not possible.

However, after playing a little bit around the various options I found a hack/workaround/solution which actually works and allows us to do a complete refresh – hence this post. The actual trick comes from the fact that the dependent parameter gets refreshed only when its values are invalidated by the selection in the first parameter. In example, if a user de-selects a value in the parent parameter and selects another value which prompts a complete change in the second (dependent) parameter, SSRS will actually apply the default selection by default. This got me thinking that if we can invalidate the dependent parameter every time a parameter changes we will enforce a complete refresh. An easy way to do this is to attach a value such as a GUID obtained with the NEWID() T-SQL function, or the output from GETDATE(). An old workaround I have provided in the Connect item was an idea based on GETDATE(). However, in this post I will show how we can use NEWID() with the same outcome.

Firstly, let’s assume we have the following tables in SQL Server:

Parameter (e.g. dimension) table pTable


Fact table fTable


p1k is for parameter 1 key, p1l for parameter 1 label

In preparation for creating our report we can create the following three stored procedures (script is attached in the end of the post):

There are a few things that may need clarification.

  • The first stored procedure is a standard procedure to retrieve the distinct values for the parent parameter from a typical dimension table;
  • The TvFMVParamSplit function splits strings in the format ‘a,b,c’ to a table containing a, b and c on each row. We need to use such a function to split multi-select parameter value strings which SSRS returns when dealing with multi-selectable parameters;
  • In the second stored procedure we attach a GUID to all parameter keys. Every time this stored procedure gets executed the GUIDs are different and the output from the stored procedure is different, as well. We use this to invalidate the available and default values for the dependent parameter;
  • When we use the key+guid strings in the third stored procedure we need to strip the GUID out of the string. We know that the output from NEWID() has LENgth of 36, so we REPLACE the last 36 characters in each selected value with an empty string, thus eliminating the redundant manually attached part and we are left with the key only;
  • The string which SSRS constructs for the multiple selections gets quite much longer than when passing ints – we get 36 extra characters per value. Therefore, this is less efficient and cumbersome than passing straight keys. It could be better to have a flag in SQL Server which we can toggle every execution to 0 or 1 instead of using a GUID as it would be shorter but would introduce a slight extra bit of complexity I avoided for the purposes of this post.

The overall goal is to cause a complete refresh of all values in the second parameter whenever its stored procedure is called by SSRS (after change in the parent parameter).

Now we are ready to use these datasets in SSRS:

  1. Create a new Data Source pointing to the database containing out tables and stored procedures;
  2. Create a Data Set p1 for the parent parameter;
  3. Create a @p1 report parameter (multi-select, text) with available values obtained from the usp_p1 stored procedure (p1k as Value, p1l as Label);
  4. Create a Data Set p2 for the dependent parameter, which accepts @p1 as a parameter;
  5. Create a @p2 report parameter (multi-select, text) with available and default values obtained from the usp_p2 procedure (p2k as Value, p2l as Label).

An interesting thing to note is that this setup will actually not quite work. If we just perform these 5 steps and test we will notice that the values for @p2 do not always get refreshed on change of @p1. Half by chance I made @p2 Internal. Then I added an extra @p3, which is then used on the report and did this the trick. Therefore, the actual steps required to complete the creation of our report are:

  1. Set the @p2 report parameter to be Internal;
  2. Create a @p3 report parameter (multi-select, text), with Available Values Value expression of =Parameters!p2.Value and Label of =Parameters!p2.Label. The Default Values should be =Parameters!p2.Value;

  1. Create a Data Set main from the usp_main stored procedure and make sure that its parameter is populated by @p3, not @p2;
  2. Add a table in the report with two columns, which show p2l and amt from the main data set.

Now if we deploy (also in the Preview window) we can test the outcome by changing selections of p1. Since there are quite a few fiddly steps involved in getting this to work I am attaching the working rdl (SQL Server 2008 R2) and a T-SQL script (SQL 2008) which creates the tables, populates them with data and creates the stored procedures (in Adventure Works) required for this workaround to work as described here. Please note that the same should work in SSRS 2005 but I have not tested it in that environment. Please add a comment to this post if you test it and it indeed works.

[listyofiles folder=”wp-content/CascadingParameters”]

Advertisements

16 thoughts on “SSRS Cascading Parameters Refresh: Solved”

  1. I like the example as it is a problem I have to deal with as well. Since I am a novice, could you point me to an example that does not include stored procedures but queries instead to solely be able to focus on Cascading Parameters Refresh. The use of the function to split multi-select parameter value strings is confusing and distracts from the solution for how to invalidate the dependent parameter every time a parameter changes. Many Thanks

    Like

  2. Hi Patrick,

    I have provided the stored procedures example as it is the preferred method for getting data in SSRS. It provides some benefits and I am a strong proponent of using sprocs instead of scripts directly in SSRS.

    In SSRS T-SQL behaves somewhat differently as you don’t need to split the parameter and you can use:

    SELECT columns
    FROM table
    WHERE somecolumn IN (@p)

    SSRS splits it for you automatically.

    I would strongly suggest, however, to get used to using stored procedures as you can separate the T-SQL code from the report, reuse it, change it without fiddling with the report, etc.

    Like

  3. Brilliant solution, thanks for posting!

    This definitely works for the first cascaded parameter, but unfortunately nothing I try works for more than one. I’m doing the internal parameters and adding a time stamp to the parameter values to invalidate them.

    e.g.
    Division (primary, not cascaded)
    > Department (secondary, based on division)
    > > Location (based on department)

    I can get department to update/refresh correctly, but not location.

    Any ideas?

    Like

  4. Hi,
    It was a wonderful post, the solutions works excellent.
    Here i have a scenario:
    Available Values are 1-10 and Default Values are 3-5 based on the preceded parameter selected. The above work around is not working for my scenario. can you please let me know is there any other alternative so fix this issue.

    thanks in advance
    Durga

    Like

  5. Durga :
    Hi,
    Hi,

    It was working fine on local machine. when I run this report from report viewer control(deployed in staging) not working properly.

    Thanks
    Durga

    Like

  6. Hello,

    I tried your workaround solution in case one needs to refresh cascading parameters.Actually in my case, refresh parameters should be dates and I found out that if I use some “available values” , I do have the refresh based on a parent param, but then I loose the calendar option in my date param and that’s not what I want.
    Did you alreday deal with that similar case?

    Thank you in advance for your response
    Rgds
    E.Fery

    Like

  7. Great solution Boyan!

    It helped me to resolve one part of my problem. I have quarter-month-week cascading parameters BUT it is also required to default to current month. Any suggestion how to resolve this is much appreciated

    Like

Comments are closed.