CODEWITHSUNDEEP
reporting-services
Dinesh
Dinesh

Reputation: 31

SSRS report parameters dependent on each other

I have a SSRS report with four parameters. Each parameter is multiselect and text type. When I select a value in one parameter, the values other parameters should change based on selected value. Its like parameters are forward dependent and backward dependent. How can I achieve this.

Upvotes: 0

Views: 3925

Answers (1)

Andrey Morozov
Andrey Morozov

Reputation: 7969

This feature is called Cascading Parameters and short answer to your question is - backward dependencies are allowed but forward dependencies are not.

I would describe this on short example.

Suppose you have a report with two parameters Dealer and Region - both are text and multiselect and you want to have a dependency from Dealer to Region, i.e. if you select region(s) from Region parameter first then dealers list in Dealer parameter dynamically changes to show only those dealers which is related to selected Region.

enter image description here

To acheive this you need to define three datasets and two parameters:

  • Main dataset for Main report with one parameter @dealername,
  • Dealers dataset - values source for @dealername parameter with one parameter - @region (backward dependency) and
  • Regions dataset - values source for @region parameter without any parameter

In other words approach then

MainReport <---is filtered by--- Dealers <---is filtered by--- Regions

would work well, but if you try to add forward dependency from Regions dataset to Dealers - i.e. add @dealername parameter to Regions, then you'll get an error message -

Forward dependencies are not valid because by doing so you'll create a circular reference which can not be resolved.

enter image description here

Upvotes: 3

Related Questions