Insufficient data from Andrew Fryer

The place where I page to when my brain is full up of stuff about the Microsoft platform

SQL Server 2008 Reporting Services cascading prompts.

SQL Server 2008 Reporting Services cascading prompts.

  • Comments 5
  • Likes

Here's' a practical example of how Reporting Services (SSRS) in SQL Server 2008 has been modified.  Cascading prompts in SSRS 2005 re-query if the middle of the three parameters changes.  In case you aren't familiar with the term this is where you select a country in a drop-down box and this in turn determines which states/regions appear in the region drop-down box for the user to select (i.e. those in the selected country)

For example using  these three cascading prompts in AdventureWorksDW:

  • Parameter @Country based on dataset dsCountry = select distinct EnglishCountryRegionName from DimGeography
  • Parameter @State based on dataset dsState = select distinct StateProvinceName from DimGeography where EnglishCountryRegionName=@country
  • Parameter @City based on dataset dsCountry = select distinct city from DimGeography where EnglishCountryRegionName=@country and StateProvinceName=@state

When a Country is selected then the State drop-down list is populated which is as expected, but when a state is selected the queries for both Country and City are both re-executed which is not necessary.  This situation worsens if you have  a separate dataset for the default value for each of these parameters as this will also cause more re-querying and there is no control you can exercise over that.

That's all fixed in SSRS 2008; the engine knows which datasets will be affected by a parameter change and only those will re-execute.  Also the results can be shared between the parameter query and the default data set.

The really good bit is that all that you need to do is to move the reports to SSRS2008.

Comments
  • PingBack from http://geeklectures.info/2007/12/19/sql-server-2008-reporting-services-cascading-prompts/

  • Actually this is not always beneficial.  For example, I have 4 parameters and here are their dependencies:

    1: none

    2: 1

    3: 1 and 2

    4: 1 and 3.

    So when I update the first parameter it updates the other 3.  When I update the 2nd one it only updates the 3rd one and not the 4th one. Even though the 4th one should change because it is dependent on the 3rd one.  This is causing major issues with the report not behaving how it should.  Now I have also changed the parameter to "Always refresh" and I still get this issue.

  • And now bigger messes in ssrs 2008 with cascading parameters. If the query for parameter default values return no records, user gets an error message. The parameter does not recognise "Allow blank values" and treats blanks as nulls.

  • My cascading dropdowns worked fine in 2005 but in 2008, it is a mess. I have the same country, state, city thing and if you change any, it freezes sometimes (not everytime) and the report is unusable.

  • Rahul.  You might need to contact support to get to the bottom of that, having patched your installation.  2008 SSRS is faster then 2005 so other things to check are that your queries for the parameters are fast and that the indexes etc. you may have had in place in 2005 are all still OK

    Andrew

Your comment has been posted.   Close
Thank you, your comment requires moderation so it may take a while to appear.   Close
Leave a Comment