Cascading Filters

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.

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,

1. I have report parameters for Account and Department and the Account String. 
2. 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

1. 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 lowercase “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.