In order to achieve the best performance with our queries, we need to perform filtering on the numerical key columns. However, the display values in the prompts need to be textual and user friendly.
In this recipe, we will create a filter that displays the product line list (textual values) but actually filters on the numerical codes (Product_Line_Code).
In this recipe, we will create a prompt and examine the differences between using the display value and the use value.
Open Page Explorer and click on the Prompt Pages folder. Drag a new page from Toolbox under Prompt Pages.
Double-click on the newly created prompt page to open it for editing.
From the toolbox, drag Value Prompt to the prompt page. This will open a wizard.
Set the prompt name to
ProductLineand then click on Next as shown in the following screenshot:
Keep the Create a parameterized filter option checked. For Package item, choose Sales (query) / Products / Product line code. Click on Next as shown in the following screenshot:
Keep the Create new query option checked. Give the query name as
promptProductLine.Under Value to display, select Sales (query) / Products / Product line.
Click on the Finish button. Run the report to test it.
When you drag a prompt object from Toolbox, Report Studio launches the prompt wizard.
In the first step, you choose the parameter to be connected to the prompt. It might be an existing parameter (defined in the query filter or framework model) or a new one. In this recipe, we chose to create a new one.
Then, you are asked whether you want to create a filter. If there is already a filter defined, you can uncheck this option. In our example, we are choosing this option and creating a filter on Product line code. Please note that we have chosen the numerical key column here. Filtering on a numerical key column is a standard practice in data warehousing as it improves the performance of the query and uses the index.
In the next step, Report Studio asks where you want to create a new query for the prompt. This is the query that will be fired on the database to retrieve prompt values. Here we have the option to choose a different column for the display value.
In our recipe, we chose Product line as the display value. Product line is the textual or descriptive column that is user friendly. It has one-to-one mapping with the Product line code. For example, Camping Equipment has a product line code of 991.
Hence, when we run the report, we see that the prompt is populated by Product line names, which makes sense to the users. Whereas if you examine the actual query fired on the database, you will see that filtering happens on the key column; that is, Product line code.
You can also check the generated SQL from Report Studio.
In order to do that, navigate to the Tools | Show Generated SQL/MDX option from the menu as shown in the following screenshot:
It will prompt you to enter a value for the product line code (which is proof that it will be filtering on the code).
Enter any dummy number and examine the query generated for the report. You will see that the Product line code (key column) is being filtered for the value you entered.
So, now you know how the prompt display values and use values work.
If you ever need to capture the prompt value selected by the user in expressions (which you will often need for conditional styling or drill-throughs), you can use the following two functions:
ParamDisplayValue (parameter name): This function returns the textual value which represents the display value of the prompt. In our example, it will be the product line that was selected by the user.
ParamValue (parameter name): This function returns the numeric value which represents the use value of the prompt. In our example, it will be the Product line code for the product line selected by the user.