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

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
主站蜘蛛池模板: 瓦房店市| 集安市| 观塘区| 威海市| 余庆县| 巴楚县| 东辽县| 清苑县| 卓资县| 东港市| 西乌珠穆沁旗| 丹凤县| 乌兰浩特市| 阿克苏市| 日土县| 合川市| 福建省| 武汉市| 磴口县| 万源市| 资兴市| 鲁山县| 沙河市| 德阳市| 霍邱县| 宜君县| 金坛市| 宁强县| 新晃| 高陵县| 清流县| 荥经县| 南涧| 马鞍山市| 平武县| 宜良县| 吉安县| 桑植县| 景泰县| 五台县| 黄浦区|