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.

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.