Introduction
In this blog post, we'll explore parameters in Pixel Perfect Reports and illustrate their ease of use compared to other reporting tools, such as SSRS. When users add a list to create a new level of detail, Pixel Perfect Reports automatically adds parameters behind the scenes, using the primary keys as defaults. These parameters serve as the left side of the comparator in a predicate, comparing them to the runtime value as part of the WHERE clause in the SQL query of the list. This process filters the appropriate rows for the detail list.
In most cases, using primary keys is the appropriate way to set up the predicates, so no action is needed regarding the parameters. However, users can add or remove the primary key parameters and use other fields if necessary. The process is straightforward and involves checking or unchecking some checkboxes in the Parameters dialog box.
Adding Parameters in Microsoft SSRS
Open the Report Data Pane:
In SSDT, go to the View tab and click on Report Data if the pane is not already visible.
In Report Builder, the Report Data pane is usually on the left side of the screen.
Add a New Parameter:
Right-click on Parameters in the Report Data pane and select Add Parameter.
This opens the Report Parameter Properties dialog.
Configure Parameter Properties:
General Tab:
Name: Enter a name for the parameter. This name is used in expressions and queries, so make it descriptive.
Data Type: Choose the appropriate data type for the parameter (e.g., Integer, Text, Date/Time).
Prompt: Enter the text that will be displayed to users when they are prompted to enter a value for this parameter.
Default Values: You can set a default value for the parameter or allow users to select from a predefined list or query-based list.
Available Values: If you want to provide users with a list of options, specify where these values should come from (static list or dataset).
Advanced Tab:
Configure additional options like hidden or internal parameters based on your needs.
Integrating the Parameter into Your Dataset
Once the parameter is created, you need to modify your dataset query to use this parameter.
Edit the Dataset Query:
Right-click on the dataset in the Report Data pane and select Dataset Properties.
Go to the Query tab and incorporate the parameter into your SQL query. For example:
SELECT * FROM Orders WHERE OrderDate >= @StartDate AND OrderDate <= @EndDate
Replace @StartDate and @EndDate with the names of your parameters.
Update Parameters in the Query:
Click on Parameters in the Dataset Properties dialog.
Ensure that the parameters in your query are mapped correctly to the parameters you created in the report.
Conclusion
Pixel Perfect Reports significantly reduces the complexities involved in setting up parameters. In most cases, users don't need to do anything at all. This contrasts with other reporting tools on the market, such as SSRS, which require elaborate steps to set up parameters.