Parameters inside a dataset query/stored procedure are frequently used to filter report data before the data is returned to the report. They also can link related reports, control the report appearance, and/or narrow the scope of a report for specific users.
Creating Report Parameters:
A query parameter is populated in two ways:
1. Static (hard-coded)
2. Assigned via Filter (Map [Filter Variable] to Report Parameter)
If it’s a “Static” Parameter, check the empty Static checkbox and enter a Design Preview Value
These are parameters which your query requires to be “hardcoded”. For example, a static parameter could be used with one report which uses one query, but the query will return different result sets based on the static parameter passed.
Sample query using static parameter:
IF @MyStaticParameter = 1 BEGIN select * from table where country = ‘United States’ END
IF @MyStaticParameter = 2 BEGIN select * from table where country <> ‘United States’ END
- Assigned via Filter:
A report designed with report parameters must assign each Dataset Parameter to its according [Filter Variable]. By default, the names of these two items will match, as shown below.
By choosing the [Filter Variable] from the dropdown list you will pass the Dataset Parameter to the query. Next enter a default value in [Design Preview Value] box and click the [Design Preview] button to preview the report.
For more information, see Drilldown Reports.
Please note – Dataset Parameters are passed to queries/stored procedures or sub-reports and dashboards which also use those same parameters as the parent report.