Report parameters are a useful feature for dynamic reporting. You can reference a parameter value in any expression to control the appearance or behavior of items in a report, or you can send it to a dataset as a query parameter for filtering. If you set up a parameter to accept multiple values, Reporting Services stores the selected values as an array which affects how you use it in an expression or a query.
In particular, if you use a multi-value parameter as a query parameter to filter a dataset, you can easily do this by including a WHERE clause and using the parameter as a list to the right of IN, like this:
WHERE TableColumnValue IN (@ParameterName)
However, if you must use a stored procedure for your dataset, you cannot simply pass the multi-value parameter values as a query parameter, like this:
EXEC myStoredProc (@ParameterName)
Let’s say that your parameter selections include a list of years: 2016, 2017, 2018, and 2019. Reporting Services passes the parameter values to the stored procedure as a single string of values, like this: ‘2016,2017,2018,2019’. If your stored procedure uses the parameter values in a WHERE clause as described above, the query returns no rows. To work properly, the stored procedure needs a list like this: ‘2016’, ‘2016’, ‘2018’, ‘2019’.
The traditional solution to handling multi-value parameters in a stored procedure is to create a user-defined function to split the string by delimiter and return a list. The addition of JSON support to SQL Server 2016 allows you to use an alternative method. In this example, you will see how to use the OPENJSON function to work directly with the single string of values from a multi-value parameter. Although this example does not require the use of a stored procedure, you can use the same technique within your own parameterized stored procedure
Product Versions (one of the following):
- SQL Server Reporting Services 2016 or newer
- Power BI Report Server
- Paginated Reports in Power BI or Power BI Desktop
What You’ll Need
- A Power BI report connected to a data source using import, DirectQuery, or SSAS direct connect mode.
- A Power BI Report Server or a Power BI subscription with premium capacity (minimum: P1 or A4).
- Moderate level DAX coding skills
Difficulty Level: 200
Contributed by Stacia Varga
Creating the Solution
An example of the finished report for this recipe is shown in Figure 1.
To apply this recipe, you need one query that returns a distinct set of values from a table. This set of values is used to provide a list of values for a report parameter. In the code shown below, the query returns the distinct years from the OrderDate column in the Sales table.
Add a data source to the report for the PaginatedReportData sample database.
Add a dataset to the report, using the following T-SQL query:
select distinct YEAR(OrderDate) as Year from Sales order by Year;
Now you need to create a multi-value parameter that uses this dataset as its list of available values.
Add a report parameter with an Integer data type and configure the parameter to allow multiple values and to use the query created in the previous step for the Available Values.
Optionally configure the parameter to use the same query for the Default Values.
You also need a query that can filter a table by using one or more values in the parameter selection. However, remember that you cannot use these values as a query parameter because the Reporting Services parameter object stores multiple values as an array. Instead, you preprocess the parameter values by using the built-in Join function to create a single string of text from each parameter value selection using a comma as a delimiter. For example, if you select the years 2016 and 2017 as parameter values, the Join function returns the following string:
To set up a dataset that uses a query applying the Join function to your parameter, you must use an expression. However, if you first create the dataset by using an expression, the fields list associated with the dataset is empty because Report Builder cannot resolve the query yet. Therefore, you should start with a query that uses static values as a placeholder for the parameter values.
The query shown in the code below returns the sum of values in the SalesAmount column from the Sales table filtered by years 2016 and 2017 and grouped by distinct year. Add another dataset to the report, using the following T-SQL query:
select YEAR(OrderDate) as Year, SUM(SalesAmount) as SalesAmount from Sales s inner join OPENJSON(N'[2016,2017]') on YEAR(s.OrderDate) = value group by YEAR(OrderDate) order by Year;
Notice the use of the OPENJSON table-valued function in which an array object, enclosed in brackets, is used as its single argument. In this case, the function returns a table consisting of a single column named value containing one row per element in the array.
Figure 2 shows the Report Data pane after adding the data source, two datasets, and one parameter to the report design.
Now that fields are generated by Reporting Services for the dataset, you can modify the dataset to use an expression. In this case, the expression concatenates the static portion of the SELECT statement with the dynamic values for the parameter which are combined into a single comma-delimited string by using the built-in Join function.
Replace the T-SQL query in the dataset created in the previous step with the following expression:
="select YEAR(OrderDate) as Year, SUM(SalesAmount) as SalesAmount from Sales s inner join OPENJSON(N'[" + Join(Parameters!Years.Value,",") + "]') on YEAR(s.OrderDate) = value group by YEAR(OrderDate) order by Year;"
With the OPENJSON function in place to use the parameter value selections in a dataset, you are now ready to use that dataset with a data region. In the next step, a chart data region is used, but you can of course use any data region supported by Reporting Services.
Add a column chart to the report with SalesAmount as Values and Year as Category Groups.
When the report is rendered, the parameter default values (if configured) or selected values are used in the chart’s dataset expression. This expression returns a query that includes the results of the OPENJSON function in an inner join with the Sales table, which in turn filters the Sales table.
This recipe shows you how to use the T-SQL OPENJSON function to parse multi-value parameters. You can use this same technique in a parameterized stored procedure by using dynamic T-SQL to combine the static portion of the query with the parameter variable containing the value passed in from Reporting Services. In that case, you do not need to use the Join function, but can simply associate the report parameter with the query parameter in the dataset definition that calls the stored procedure.