Cascading Prompts: Making your reports more dynamic
by Kenny Rencher , Technical Product Marketing
Slicers are a great way to make everything in your report dynamic. Parameterizing your members and creating sets is another great way to make your reports dynamic. The process of creating parameters and then creating sets using that parameter can often be tedious and confusing. Fortunately, in BI Office, there is a tool that creates dynamic slicers using both parameters and sets all in one step to ensure that your report is as dynamic as possible.
In this blog, you will learn how to set up and use Cascading Prompts—whether based off a single hierarchy, or multiple hierarchies.
Using Cascading Prompts: Single Hierarchy
A cascading slicer (aka cascading prompt) is one of the most powerful tools in BI Office. It enables you to create a “waterfall effect” from one slicer to another based on your previous selections. It is especially beneficial when you are dealing with large lists or large hierarchies that need to be segmented into smaller, clearer choices. Cascading prompts are based around creating parameters and sets that use those parameters. Once created, you can also use any of your parameters or sets individually (instead of combining them in a cascading function) if you are looking for something specific. You can do this without having to create them separately, and without writing any code.
Let’s say you want to create a slicer that has a list of sales teams, and as you select the team, your slicer for sales person adjusts based on that selection. Just adding sales person and sales team as slicers will not work. You must parameterize the members involved and create sets based on those parameters for both slicers to work in tandem. You can independently slice by either category, but ensuring they interact together is another matter entirely.
Figure 1: Sales Person & Sales Team on Slicers
Creating this interaction can be done by creating individual sets and parameters, but we want to work smarter, not harder. This is where cascading prompts come in. Under the Analytics tab of the ribbon, expand out the Cascading Prompt drop down and select the Single Hierarchy option. In this example, I want to create slicers from a single hierarchy; we will address Multi Hierarchy later in this blog.
Figure 2: Click Multi Hierarchy
Type in “Sales Person” for the cascading prompt caption and give it a brief description, if desired. Since we want a slicer for sales team and sales person, both of which are in the sales person dimension, select that dimension for both slicers. Select the Sales Team hierarchy and check the boxes for Sales Team and Sales Person to create both slicers.
Figure 3: Single Hierarchy Slicer Creation
Click Next to see what elements the wizard created. We can see here that in order to get the interaction we want between slicers, the wizard has created two sales team sets, and a parameter all under the Sales Teams hierarchy. At the bottom, select Public to make it available to others, and click Finish.
Figure 4: Elements Created by the Wizard
Click Run Query, and the slicers will automatically show up in your report. You may need to rename the caption on your slicers by right clicking on them and selecting Slicer Options. Now, as we change the slicer for sales team, the slicer for sales person changes based on our team selection. It is now easy to tell how each team is doing and how much each individual person is contributing to their team.
Figure 5: Finished Dynamic Slicers
In the Elements Panel of our Sales Team hierarchy, we see our newly created parameter, as well as the two sets the wizard created in the Sales Team Folder under Public. Any of these parameters and sets can be used independently if desired. Since they were created in the public folder, others in your organization can incorporate these dynamic slicers in their own reports.
Using Cascading Prompts: Multi-Hierarchy
The ability to use cascading slicers off of different dimensions and hierarchies can make our report even more dynamic. Let’s say we want to create slicers based on the top five products based on a country slicer. Since country is in a different dimension than products, we use a multi-hierarchy cascading prompt.
Under the Analytics tab, expand the Cascading Prompt section again and select the Multi Hierarchy option to launch the wizard. Give the cascading prompt elements a caption. In this case, “Top 5 Products”. In the wizard, each row is a slicer, the top row being the slicer that all other slicers are based on. This creates a waterfall effect that makes slicers truly dynamic.
Figure 6: Multi Hierarchy Slicer Creation
For the first slicer for country, select the Customer dimension, then click on the Advanced Selection thumbnail to open that dialogue box. Make sure the Customer Dimension is selected and select the Customer Geography City hierarchy. We want it to slice at the country level, so click the Level Member Selection drop down and select Country, then OK. You can also choose a custom set as your member, such as Top 20 US Cities for the cascading slicer to be based on, or select specific members.
Figure 7: Advanced Selections for first Slicer
For the second slicer (the second row,) we want the top five selling products for each country selected in the first slicer. Since products are in the product dimension, select Product from the dimension drop down for the second slicer. Click the thumbnail for Advanced Filtering to open the filter dialogue box. Choose Top Count for the filter type, and enter five for the value. You can also filter by Top Percent or Bottom Count/Percent. There are many options for filtering your slicer output to make your reports more dynamic. Make sure Sales is selected as the measure and click OK.
Figure 8: Filter Options for Slicers
Click on the Advanced Selection thumbnail for the second slicer and make your member selections. Makes sure the dimension is Product, and the hierarchy is Products. Choose the Product Member Level, then click OK. Save it to public so the parameters and sets can be easily shared.
Figure 9: Advanced Selections for Second Slicer
Click Next to see what elements the wizard created. We can see that in order to get the interaction we want between the slicers, the wizard has created all of the sets and parameters needed.
Figure 10: Elements Created by the Wizard
Click Finish to close the wizard and view your new slicers. Now, as we choose a a value in the Country slicer, the Products slicer populates with the top five products sold based on that country selection.
Figure 11: Finished Dynamic Slicers
Creating slicers is a great way to make your report dynamic, but by themselves, they are limited. Using the Cascading Prompt feature in BI Office, enables you to create slicers that interact with each other and change based on user input. This is an easy way to parameterize members and create sets to make your report and your slicers as dynamic as possible.
Hi. Can you make an example of a cascading prompt with 'All' as one of the choices? So that in your example, you can select All Sales Team and All Sales Person, or South Sales Team and All Sales Person? I made something that works but I'd like to see if there's a better way. They also have to become Global slicers. I didn't use the wizard but created sets and parameters. Is there a way to do this with a wizard and still be able to select on both slicers? It only selects on the 2nd one so that if you have All on the 2nd, it ignores the 1st slicer. For example, selecting Sales Team: South, Sales Person: All, will give you All.
Hi Rose Yap-Fryman ,
Great question! One simple option for accomplishing that same goal is to edit whichever slicer you want the “All” to apply to, and make it a multi-selection slicer. Right click on the slicer, select Slice Options, and under the slice type dropdown choose the Multi-select Drop Down Box. This will enable you to select all the drop-down items to give you all as an output to the other slicer, but it will not give you an “All” option.
As far as creating this in a wizard goes, the wizard for single-hierarchy does not enable you to select “All." However, the multi-hierarchy does with its free select feature. You can use the multi-hierarchy wizard for single hierarchies, it just has more options for you to customize your slicers, including the free select option.
For example: lets create a set of cascading prompts (slicers) that have all in the first slicer. The first slicer I want is sales teams (with an all option) and the second I want is sales person based on the sales team input. Open the Cascading Prompt Wizard for Multi-Hierarchies. Give the cascading prompt a name like before, and choose the sales person dimensions for both slicers.
Click the advanced selection thumbnail, make sure the sales team attribute is selected, and click the Free Selection option. Click on the Free selection thumbnail, and it will let you choose exactly what members you want included, including the “All” option.
CTRL click all members you want as slicer options, in this case, choosing all and each individual region will get us the desired slicer options. This free selection can be used for making advanced selections under any dimension.
Choosing team member for the other slicer, and finishing the wizard like before will give you this result. The first slicer has the all option, and the second slicer will be based off the all input from the first slicer.
Hopefully this adequately answered your question. Please let me know if you have any additional questions.
Really good idea!! small question thought, if I have a 4 layered hierarchy and I am building this into a storyboard and the end user decides that instead of "drilling" fully down to the lowest he wants to results for the 2nd layer as a total?
to give an example:
chart: 1 measure (columns) - 1 dimension (Date)
Hierarchy= (superteam - Team-Subteam- person)
the end user (Manager for team 1.1) starts:
he selects superteam 1
then team 1.1
he wants to see the measure for the team 1.1 and later drill further down and have the overview for all levels.
In the storyboard which slicer should I make interact with this chart in order to be able to have data for each level as he drills down?