DAX based SSRS reports with parameter based upon another parameter

There could be a situation where we need to have a parameter based on the choice made in some other parameter in the SSRS report.

It can be implemented using standard queries as well as Data source based on business logic.

Step 1: Create a query “DemoQuery” in DAX. Choose data source as EmplTable. Change the property DYNAMIC on DataSources->EmplTable->Fields node to No. This will enable us to select the fields that are required.

Step 2 : Create another query “DemoQueryRelated” with HRPPartyPositionTableRelationship table  as  the data source .Also add a range on the field reference.

Step 3 : Create a VS 2008 MSDAX Reporting Project. In the solution Explorer open the Report1.moxl that is the model view of the report .Create two Datasets based on the two queries that has been created in DAX. Choose the query and corresponding fields from a list populated with Physical queries from AX by clicking on the ellipses button of “QUERY” property in the property sheet of new dataset. After this process it will be like this :

Step 4: As soon as the datasets are created  it can be observed that a parameter has automatically been added in the parameter list.

Step 5: Create two parameters EmplId & Position and assign them values.

Step 6: Now we have two parameters “EmplId & Position”. Parameter “Position” is dependent on “EmplId”. In order to achieve it the value selected in parameter “EmplId” is passed to the auto created parameter “PositionDS_Reference”. This way a range is being applied on the Query “DemoQueryRelated” in AX. See below.

This way the value chosen in the parameter EmplId is passed to the parameter “PositionDS_Reference”. Also make the Property VISIBLE as Hidden.

Step 7: After getting all these steps done we can add an auto design by dragging the PositionDS dataset onto Design node. After browsing the design we can see parameter list similar to below.

The values available in Position parameter list varies based on the value chosen in Parameter Employee. It can be verified by looking at the respective list. This demo is done with Single-value parameters. The same works with parameters of type Multi-value.

I will talk about the same example with multi-value parameter in a business logic environment.

Njoy……….. J