Search for answers here or ask a question

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.

8replies Oldest first
  • Oldest first
  • Newest first
  • Active threads
  • Popular
  • 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.


    Like 1
    • Kenny Rencher
    • Technical Product Marketing
    • Kenny_Rencher
    • 5 yrs ago
    • Reported - view

    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.

    Like 2
  • Thank you for your awesome reply, Kenny Rencher ! Rose Yap-Fryman , does that help?

  • Karen Owens Kenny Rencher

    Yes! Thank you so much. That helps a lot. 

    Like 1
  • Hey this is fantastic.


    The same functionality we are expecting in the PY18, can we do that ?



    B Rama Krishna

    Artha Solutions 

  • Hi Rama,

    This functionality is already built into the latest version of Pyramid 2018.

    We have a detailed guide on how to use in our help section.

    Click here for the Cascading Slicers guide and here for the Multi Hierarchy Cascading Slicer guide.

    Like 1
  • Thanks Itamar.

  • 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?