官术网_书友最值得收藏!

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:

  1. 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.
  2. Add three detailed filters as follows:
    • [Employee name]=?Employee?
    • [Sales (query)].[Employee by region].[Country]=?Country?
    • [Sales (query)].[Employee by region].[Branch region]=?Region?
  3. Define all filters as Optional as shown in the following screenshot:
  4. Now create a new prompt page. We will start by adding a prompt for Region.
  5. 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.
  6. Click on the Finish button.
  7. 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:
  8. 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.
  9. Select the Region prompt and set its Auto-Submit property to Yes. Do the same for the Countries prompt.
  10. 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
主站蜘蛛池模板: 张掖市| 慈溪市| 巴林左旗| 永顺县| 呼玛县| 佳木斯市| 雷山县| 通辽市| 石河子市| 惠水县| 清涧县| 新宁县| 尼勒克县| 临澧县| 犍为县| 汉中市| 宜黄县| 巴南区| 房山区| 襄城县| 炎陵县| 连南| 金华市| 古丈县| 济宁市| 佛坪县| 乃东县| 正安县| 阜康市| 南汇区| 本溪| 阿合奇县| 米脂县| 龙口市| 辽宁省| 洞头县| 建宁县| 乐平市| 平阳县| 涪陵区| 汽车|