This type of Where clause allows you to enter values and generate a new result set each time you run a report. This is useful for reports that have one or more varying runtime parameters, such as start and stop dates, zip codes, or telephone area codes.
Consider the scenario of limiting the data retrieved in a report to customers in a specific state. When you create a Where clause to evaluate a value prompted at runtime, you could define a state each time you run the report. You could also create a default value for the state if a user failed to enter a value at runtime.
In this example, we will select Evaluate a Value Prompted at Runtime on the left side of the Where clause and Against a Database Column or Expression on the right side of the Where Clause.
To create a Where clause to evaluate a value prompted at runtime:
Click Add Clause in the main Where Clauses dialog box.
Select Evaluate a value prompted at runtime as the type of clause.
Choose the comparison operator or SQL predicate for the clause.
SQR Production Reporting Studio uses the operator or predicate to evaluate the information you define in the Where clause against the user input value.
For example to limit a report to a specific state, you would choose Equal to as the operator.
If you choose a comparison operator, you can modify it with the ANY or ALL operators. Comparison operators are mutually exclusive – you can select ANY, ALL, or None, but not all three. If you choose ANY or ALL, the right side of the Where clause must be a subquery, and the appropriate dialog boxes will appear. (See Creating Subqueries that Return Zero to Many Values for examples.)
Choose how to evaluate the value prompted at runtime.
Depending on the operator or SQL predicate selected, you can evaluate the value against a column or expression, against a constant or one-off expression, or against one or more values from a subquery. (SQR Production Reporting Studio disables the value prompted at runtime option since you cannot join two report parameters.)
In our example, we want evaluate the value prompted at runtime against the State database column. So, we would select Against a Column or Expression as the option to place on the right side of the Where clause.
Choose the database column or expression to evaluate.
For example, to limit the data displayed to a different state each time you run the report, you would choose the State column.
If you chose to evaluate the value against a constant or one-off expression or against one or more values from a subquery, the appropriate dialog boxes would appear instead of the database column or expression dialog box above.
Enter the text that will appear in the prompt and the maximum number of characters that you can input when you run the report.
SQR Production Reporting Studio generates a default prompt for you. For example, when you run the report, SQR Production Reporting Studio may prompt you to: “Enter a value for state.” You can override the default prompt if you wish.
The input length is the maximum number of characters you can enter when you respond to the prompt and run the report. For example, if you limit the report to a specific state, you could define the input length to be a maximum of two characters. If you do not select an input length, SQR Production Reporting Studio sets the input length to the column size.
Define how to handle the report when a user fails to enter a value at runtime.
If users fail to enter a value when they run the report, SQR Production Reporting Studio can:
Verify that the Where clause is correct and click Finish to return to the main Where Clauses dialog box.
In our example, the Where clause to evaluate a value prompted at runtime would appear in the main Where Clauses dialog box as follows:
Enter a value for STATE [[input]=STATE]
We created the Where clause described above by selecting Evaluate a Value Prompted at Runtime on the left side of the Where clause and Against a Database Column or Expression on the right side of the Where clause.
You could also create the Where clause by selecting Evaluate a Database Column or Expression on the left side of the Where clause and Against a Value Prompted at Runtime on the right side of the Where clause. Although the dialog boxes in which you enter the information would appear in a different order than described above, the resulting Where clause would effectively be the same.