Business owners want to see sales made by employees. They also want the facility to limit the report to 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.
Create a simple list report with Employee name (from 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.
- We will start by creating detailed filters on the report query. Select the list report and open the filters dialog by clicking the Filters button.
- Add three detailed filters as follows:
[Employee name]=?Employee?[Sales (query)].[Employee by region].[Country]=?Country?[Sales (query)].[Employee by region].[Region]=?Region?
- Define all filters as Optional.
- 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 Regions for this parameter.
- Click 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.
- Similarly, add 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.
In our case, users may run the report for the whole company, select 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 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 for different countries/regions. By keeping those filters, we would assure that report fetches data for that employee, for the selected Region/Country only.
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, employee list is disabled until a valid value is submitted for countries.
In step 9, we set the Auto - Submit property to Yes for the prompts.
When the auto-submit property is set to Yes, the prompt value is automatically submitted when the user selects one. This enables the dependant 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. This will be covered in detail in the next recipe.
Up to version 8.2, when a prompt value is submitted by an auto-submit action, the whole page refreshes. You can see the progress bar in the browser become active and the page takes a few moments to reappear.
With version 8.3, a new technology is implemented in Cognos. The auto-submit action does not reload the whole page. Instead, only affected prompts are refreshed. This action is much quicker and gives a seamless experience to users.