Reporting Services Cscading Parameter refresh

Question

I have googled it a lot and found that usually it cannot be done. I came across one of the hacks here:

http://www.bp-msbi.com/2011/04/ssrs-cascading-parameters-refresh-solved/

But its not working for me in ssrs 2005. Just wondering if anyone else tried it in 2005. Or is there any other hacks that can be tried.

As per this article the dependent parameter gets refreshed only when its values are invalidated by the selection in the first parameter. 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.

So basically we want to introduce a fake parameter in between two real parameters. This fake parameter is supposed to return new values everytime as the storedproc behind it will add a guid to the resultset everytime that proc is called. So it forces complete refresh of the other parameters.

Now the main issue I am facing is :

Setting the default value of this fake parameter. For the available values the storedproc behind the fake param runs and it returns data in the format say : result1,result2_GUIDFROMSQL

Now it looks like the same storedproc is called again to set the defult value if i ask it to get the default value from query. But as the storedproc is run again new guid comes and so the old value cannot be found so its not being set as desired.

I just need to figure out a mechanism to pass this guid from introduced param to the next param.

Thats where I am failing.

My issue can simply be replicated by creating a param whose datasource is this querystring.

select getdate() id, @name nid 

So in this case how to set a default value for this param.

Solution 1

Finally I was able to resolve this. This link was a helpful start. http://www.optimusbi.com/2012/07/16/multilevel-cascading-select/

Basically what it does is : Writing parameter query in a manner so that the dependent parameter changes its value every time you change its parent parameter.

The query adds rownumber with a "_" preceeding it. So every time user selects other values the rownumber changes and hence the query resultset.

Then when using the resultset remove the text after the underscore to get the real codes.

Solution 2

Below I'm going to present a detailed scenario and then I'll show the example implementation based on Visser and Abbi's answers.

Imagine that I have a report where each row is a project. A project has a Status column with values "In Progress" or "Complete" and a Project Manager column whose values are a person's name. Here are the rows in the table:

• Project A (Status = In Progress, Project Manager = Bob)
• Project B (Status = In Progress, Project Manager = Tom)
• Project C (Status = Complete, Project Manager = Jack)
• Project D (Status = Complete, Project Manager = Tom)
• Project E (Status = Complete, Project Manager = Jill)

I want to have 2 parameters on my report

1. Show Completed Projects? - This is a boolean parameter
   • When false will only show "In Progress" projects A & B
   • When true will show "In Progress" projects A & B in addition to "Complete" projects C, D, & E.
2. Project Manager - This is a multi-value text parameter whose options and default values will need to change based on the Show Completed Projects? parameter upon which it is dependent.
   • If Show Completed Projects? is set to false then only "Bob" and "Tom" options will show up because they are the project managers for the in progress projects Project A & B respectively.
   • If Show Completed Projects? is set to true then in addition to "Bob" and "Tom" you will also have "Jack" and "Jill" show up as options because they are project managers for the inactive projects Project C & Project E respectively.

Now for the implementation:

1. Show Completed Projects? parameter

   

2. Project Managers dataset query (See Visser and Abbi's answers for details on how this generates a key that will change based on the independent parameter and will force SSRS to reload the default values)

   SELECT 
       [ProjectManager_Key] = 
           pOuterAlias.[ProjectManager_Key] + '_' + 
           CAST(ROW_NUMBER() OVER(ORDER BY pOuterAlias.[ProjectManager_Key] DESC) AS NVARCHAR(MAX)),
       [ProjectManager] =  pOuterAlias.[ProjectManager]
   FROM 
       (
           SELECT 
               [ProjectManager_Key] = 
                   pInnerAlias.ProjectManager + '_' + 
                   CAST(ROW_NUMBER() OVER(ORDER BY pInnerAlias.ProjectManager ASC) AS NVARCHAR(MAX)),
               [ProjectManager] = pInnerAlias.ProjectManager
           FROM 
           (
               SELECT 
                   [ProjectManager] 
               FROM 
                   [dbo].[Project]
               WHERE
                   Status = 'In Progress' OR
                   @ShowCompletedProjects = 1
           ) pInnerAlias
       ) pOuterAlias
   ORDER BY 
       pOuterAlias.[ProjectManager]
   

3. Project Manager parameter

   • General

     

   • Available Values

     

   • Default Values

     

4. Projects dataset

   • Query

     SELECT 
         *
     FROM 
         [dbo].[Project]
     WHERE
     (
         Status = 'In Progress' OR
         @ShowCompletedProjects = 1
     ) AND
     Project Manager IN (@ProjectManager)
     

   • Parameters (Make sure to note the [@ProjectManager.Label] portion which will make it match the project on the actual project manager value from the database and not the key that we generated.