Adding cascaded prompts
Business owners want to see sales made by employees. They also want the facility to limit the report to a certain region, country, or employee.
When they select a region, they would like the country pick-list to automatically reduce to the countries falling in that region. Similarly, the employee pick-list should also reduce when they pick a country.
Getting ready
Create a simple list report with Employee name (from the Employee by region query subject) and Quantity (from Sales fact).
Define appropriate grouping and sorting for Employee name and ensure that aggregations for Quantity are correctly set.
How to do it...
In this recipe we will build three prompts for Region, Country, and Employee. We will create a relationship between these prompts by making them cascading prompts. So if you change the value selected in one prompt, the data in the other prompts should change accordingly. To do this, perform the following steps:
- We will start by creating detailed filters on the report query. Select the list report and open the filters dialog by clicking on the Filters button and then click on Edit Filters.
- Add three detailed filters as follows:
[Employee name]=?Employee?[Sales (query)].[Employee by region].[Country]=?Country?[Sales (query)].[Employee by region].[Branch region]=?Region?
- Define all filters as Optional as shown in the following screenshot:
- Now create a new prompt page. We will start by adding a prompt for Region.
- Drag a new value prompt. In the prompt wizard, choose the existing parameter Region for it. Choose to create a new query called
Regionsfor this parameter. - Click on the Finish button.
- Now add another value prompt. Choose the existing parameter Country for this, and create a new query called Countries. On the same page, choose Region under the Cascading source as shown in the following screenshot:
- Similarly, add the third and last value prompt for employee. Choose Employee as a parameter, Employees as the query name, and Country for the Cascading source.
- Select the Region prompt and set its Auto-Submit property to Yes. Do the same for the Countries prompt.
- Run the report to test it.
How it works...
In our case, users may run the report for the whole company, select a particular region, select a region and country combination, or go all the way down to employees. We want to allow them to submit the selections at any stage. That is why we created three filters and kept them all as optional.
Even if it was mandatory for the users to select an employee, we would have kept filters for country and region. The reason is that one employee might have done sales in different countries/regions. By keeping those filters, we would ensure that the report fetches data for that employee for the selected region/country only.
Cascaded source
When we set the cascaded source property, Report Studio ensures two things. Firstly, the prompt is disabled until the cascaded source is satisfied. Secondly, when re-prompted and the cascade source is populated, the prompt values are filtered accordingly.
In our case, the Countries prompt remains disabled until a valid value for region is submitted. Similarly, the employee list is disabled until a valid value is submitted for countries.
There's more...
In step 9, we set the Auto-Submit property to Yes for the prompts.
Auto-Submit
When the Auto-Submit property is set to Yes, the prompt value is automatically submitted when the user selects one. This enables the dependent prompt to be correctly filtered and enabled.
In our recipe, Auto-Submit for Region is set to Yes. Hence, when you select a region, the value is automatically submitted and the Country prompt is enabled with the correct values populated.
This action can also be performed by a Reprompt button. In that case, Auto-Submit is not required.
See also
- The Prompts – display value versus use value recipe in Chapter 1, Report Authoring Basic Concepts