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.
Create a simple list report with Product | Product name and Sales fact | Quantity as columns.
Open Page Explorer and click on the Prompt Pages folder. Drag a new page from Insertable Objects under Prompt Pages.
Double click on the newly created prompt page to open it for editing.
From Insertable Objects, drag Value Prompt to the prompt page. This will open a wizard.
Set the prompt name to ProductLine, and then click Next.
Keep the Createa parameterized filter option checked. For package item, choose Sales (query) | Product | Product line code. Click Next.
Keep Create new query option checked. Give the query name as promptProductLine.
Under Value to display select Sales (query) | Product | Product line .
Click the Finish button. Run the report to test it.
When you drag a prompt object from insertable objects, 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 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 standard practice in data warehousing as it improves the performance of the query and uses the index.
In next step, Report Studio asks where you want to create a new query for prompt. This is the query that will be fired on database to retrieve prompt values. Here we have the option to choose a different column for display value.
In our recipe, we chose Product line as 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 Product Line Code of 2101.
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.
For that, select the Tools | Show Generated SQL/MDX option from the menu.
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.