Summary
This article will detail how to implement cascading filters, sometimes referred to as parameters based on a parameter. In this exercise, a value selected in the first parameter will then drive the values that appear in the second parameter when in run mode.
Article sections:
Method
This example is based on the Solver Corp Demo. I have a three-segment account string as shown from the DW screen
- Segment 1 = account
- Segment 2 = department
- Segment 3 = entity
In my report,
- I have report parameters for Account and Department and the Account String.
- Account and Department parameters are ordered before the Account String filter and have been set up with:
- Allow Blank = all >> disabled
- Allow Multi-Select >> disabled
Order is important, the Account and Department parameters must be ordered before the Account String
- Editing my Account String parameter, type in this formula
'{Parameters.Account}-{Parameters.Department}-SUS':'{Parameters.Account}-{Parameters.Department}-SUS'Replace Account and Department with your respective dimension names.
- An example of how to hardcode a segment is shown in the above formula as well, notice that Segment 3 is hardcoded to SUS. To make Entity a parameter, replace SUS with {Parameter.Entity}
- The syntax is case sensitive, Parameters must have the capital "P" and standard dimension (Account, Entity, Scenario ...) start with a capital letter as well while all custom dims will be with a lower case "d"
- You cannot reference a dimension attribute
This completes the configuration or cascading filters. Now run the report keeping in mind that Account and Department must have values provided.
- If you have a large account string, the filter may take a couple of seconds to render in the parameter list.
Note: The most common example is probably an account string where the value selected for "Segment 1" then filters an account string parameter, returning a much smaller list.
Properties
Applies to:
- Cloud
- Private Host v 5.2.21210+
- Report Designer 5.2.21010