Report Parameters

Report parameters are special variables that enable users to control report data and appearance. When you publish a report to a Report Server, parameters become available to the end-user in the report viewer. This allows them to customize the report output by selecting values for these parameters.

Purpose of Report Parameters

Filtering Data: Allow users to specify criteria to filter the data retrieved and displayed in the report. For example, a parameter for 'Sales Year' or 'Region'.
Controlling Report Appearance: Modify how the report is displayed, such as selecting a date format or choosing a chart type.
Driving Dynamic Content: Change the content of the report based on user selections.
Providing Input to Queries: Use parameter values directly within SQL queries or other data retrieval methods.

Creating Report Parameters

You create and manage report parameters within Report Designer (part of SQL Server Data Tools - SSDT). The process typically involves:

Opening the Report: Open your .rdl file in Report Designer.
Report Data Pane: Locate and expand the 'Report Data' pane.
Parameters Folder: Right-click on the 'Parameters' folder and select 'Add Parameter...'.
Parameter Properties: A dialog box will appear allowing you to configure the parameter's properties.

Parameter Properties

When creating or editing a parameter, you'll configure several important properties:

General Properties

Name: A unique identifier for the parameter (e.g., `SalesTerritory`).
Prompt: The text displayed to the user for the parameter (e.g., "Select Sales Territory:").
Data Type: The data type of the parameter (e.g., Text, Integer, Date/Time, Boolean). This helps in validating user input.
Allow null value: If checked, the user can leave the parameter blank.
Allow blank value: If checked, the user can enter an empty string for text-based parameters.
Allow multiple values: If checked, the user can select more than one value for the parameter. This is useful for multi-select lists.

Default Values

You can set a default value for the parameter, which will be pre-selected when the report is initially opened.
Default values can be static or dynamic (derived from a dataset).

Available Values

This defines the list of values that the user can choose from.
Static: You can manually enter a list of values and their corresponding labels.
Dynamic: You can specify a dataset and fields from that dataset to populate the list of available values. This is ideal for parameters whose options depend on your data (e.g., a list of customers or products).

Visibility

Hidden: The parameter is not displayed in the report viewer. This is useful for parameters that are set programmatically or automatically.
Visible: The parameter is displayed to the user.
Internal: The parameter is not displayed in the report viewer, but it can be referenced by other report items.

Using Parameters in Reports

Once defined, parameters can be used in various parts of your report:

In Dataset Queries

Parameters are commonly used to filter data in your report's datasets. If your dataset query is for SQL Server, you'd use the parameter name preceded by an '@' symbol.

                
SELECT
    ProductName,
    SalesAmount
FROM
    SalesData
WHERE
    Territory = @SalesTerritory
    AND OrderDate BETWEEN @StartDate AND @EndDate;
                
            

In Expressions

Parameters can be referenced in report expressions (e.g., in text boxes, charts, or tablix properties) using the following syntax:

=Parameters!ParameterName.Value

For example, to display the selected territory in a report title:

="Sales Report for " & Parameters!SalesTerritory.Value

Parameter Types and Features

Text Parameters: For string values.
Numeric Parameters: For integers, decimals, etc.
Date/Time Parameters: For date and time selections.
Boolean Parameters: For true/false selections.
Cascading Parameters: When the available values for one parameter depend on the value selected for another parameter (e.g., selecting a Country might filter the list of available Cities).

Tip: Design your parameters carefully to provide a good user experience. Clear prompts and well-defined available values make reports much easier to use.

Troubleshooting Common Issues

Invalid Data Type: Ensure the parameter's data type matches the data it's being compared against in the query or expression.
Parameter Not Appearing: Check the 'Visibility' property in the Parameter Properties dialog.
Incorrect Filtering: Verify that the parameter is correctly referenced in the dataset query and that the logic is sound.

Report parameters are a fundamental feature of SQL Server Reporting Services, offering powerful interactivity and customization for your reports.