Chapter 9 – Advanced Queries and Parameters

This post is part of the series: “Professional SSRS 2016 Book Preview Posts” which are excerpts for my Wrox Press book: Professional SQL Server 2016 Reporting Services and Mobile Reports.  Each of the posts is a condensed version of the material covered in a corresponding chapter from the book.  The goal for this posts is to provide useful and meaningful information you can use.  For more comprehensive details, I refer readers to the rather lengthy book itself.  Note that large portions are copied directly from the book manuscript that may refer to figures and screen capture images that have been removed for brevity.

• Understanding T-SQL queries and parameters
• Understanding MDX queries, parameters, and expressions
• Managing report parameters
• Using parameter expressions

Chapter 5, “Database Query Basics,” introduced you to query and report parameters. This chapter introduces you to a few simple techniques to parameterize queries. The discussion in this chapter steps through the design of each sample report, and then delves deeper into less- common and more advanced design patterns.

The parameter architecture in Reporting Services has remained the same since the product’s inception. But recent enhancements in SQL Server 2016 give you control of the parameter placement in the parameter bar displayed at the top of the browser. In this chapter, you see how to define specialized parameter items you can use to return all (or a range of) dataset records with a single parameter selection. Many of the same techniques can be used with MDX queries for SQL Server Analysis Services (SSAS). However, you will need an understanding of the unique needs and capabilities of the MDX language and query objects.

NOTE Unlike in previous chapters, this chapter does not walk you through every step of the included exercises, but provides the necessary instructions to apply the skills you have acquired in the previous chapters.

T-SQL QUERIES AND PARAMETERS

As the native query language of Microsoft SQL Server, Transact-SQL (T-SQL) provides a great deal of flexibility and many creative ways to dynamically filter datasets with parameters. Out of the box, you get several sample reports and instructions to demonstrate different parameter techniques (such as using parameter lists, simple and multi-select parameters, inter-dependent cascading parameters, and so on).

Parameter Lists and Multi-select

The Ch09 – Parameter In List report has two datasets: one to populate a parameter list, and one for the main report query.

The ProductList dataset uses the following query:

SELECT
ProductKey, ProductName
FROM Product
ORDER BY ProductName
;

Another dataset, named ReportDate, uses the following query:

SELECT s.OrderDate, s.SalesOrderNumber p.ProductCategory p.ProductSubcategory, p.ProductName p.ProductKey p.StandardCost p.ListPrice, s.OrderQuantity, s.SalesAmount
FROM
vSales2013 s
inner join dbo.Product p on s.ProductKey = p.ProductKey
WHERE
p.ProductKey IN ( @ProductKeys )
;

When this query is executed in the designer, the ProductKeys parameter is added to the report, and a corresponding dataset parameter is created. These two objects have the same name, so they can be easily confused, but it is important to understand the difference. Starting with the dataset, let’s work from the inside out. Figure 9-1 shows the Parameters page of the Dataset Properties dialog where the report parameter, ProductKeys, is mapped to the dataset parameter having the same name.

FIGURE 9-1: Parameters page of Dataset Properties dialog.

Inspect the report parameter in the Report Data window and note the following properties (as shown in Figure 9-2):

The “Data type” should be set to Integer instead of the default Text—Setting “Data type” to match the filtered field can be more efficient. Certain data types (such as Date and Boolean) change the input control displayed in the parameter bar. Date-type parameters use a calendar picker control, and Boolean-type parameters display a pair of radio buttons to facilitate selecting either True or False values. All other types use a plain input box, unless values are provided in the Available Values page. In that case, a drop-down list is displayed.

“Allow multiple values” is checked—The drop-down list (which contains parameter values for selection) shows a check box before each item. This causes a comma-separated list of the selected values to be sent to the query parameter and converted to a single text string.

A multi-select report parameter is an array of key/value pairs named Value and Label.

FIGURE 9-2: Report Parameter Properties page of the Report Data window.

On the Available Values page shown in Figure 9-3, you can use the ProductList dataset to provide the product values based on the query. The “Value field” property is set to use the ProductKey and the “Label field” property will use the ProductName. This means that report users will see the names, but key values will be used internally.

You can select any combination of parameter values.

How do you show users what parameter values they selected in the report header? When a report is printed as shown in Figure 9-4, it may be important to capture the parameter selection so that the readers understand the context of the report.

FIGURE 9-3: Specifying the “Value field” and “Label field” properties.

Before looking at the finished expression, here is an experiment for educational purposes. Add a new textbox to the report header area above the table. Right-click and choose Expression… to open the Expression builder dialog. Use the parameter list and double-click the ProductKeys parameter, which creates the following expression:

=Parameters!ProductKeys.Value(0)

There are two problems with this expression. It will display the numeric product key (rather than the product name) in the textbox, and it will only display the first selected item. To correct this, you use the “Label field” property instead of the “Value field” property. A multi-value parameter is stored as an array, so you can’t just display it as a single value. The VB.NET JOIN function will

iterate through each array element, and represent the key/value pairs for each selected item. Here’s the working expression to display a comma-separated list of every selected parameter value:

=JOIN(Parameters!ProductKeys.Label, “, ”

FIGURE 9-4: Clarifying the context in the printed report.

Figure 9-5 shows the finished report with the parameter list in the heading.

FIGURE 9-5: Finished report with the parameter list in the heading.

Cascading Parameters

In the previous example, the parameter list is a little long and inconvenient. With so many values to choose from, multiple parameters can be used to break a list down into a manageable hierarchy. A parameter can depend on another parameter so that the list of avail- able values is filtered based on another parameter selection. For example, if you offer users a list of product categories and another list of product subcategories, the subcategory list would show only subcategories for a selected category. Figure 9-6 shows an example. A selection from the Categories parameter filters the product subcategories, and that selection filters the products list.

The sample report Ch09 – Cascading Parameters

FIGURE 9-6: Filtering product subcategories.

shows how to build this. You start with three separate datasets that are used to populate the parameter lists. Dataset names are listed before query. Note the logical dependencies between each query

in order.

CategoryList:

SELECT DISTINCT ProductCategory
FROM Product
;

SubcategoryList:

SELECT DISTINCT ProductSubcategory
FROM Product
WHERE ProductCategory IN ( @Categories )

ProductList:

SELECT ProductKey, ProductName
FROM Product
WHERE ProductSubcategory IN ( @ProductSubcategories )

The main report dataset (named ReportData in the sample report) uses only the ProductKeys parameter as a predicate to filter sales records.

SELECT s.OrderDate, s.SalesOrderNumber p.ProductCategory, p.ProductSubcategory p.ProductName, p.ProductKey, p.StandardCost p.ListPrice,
FROM
s.OrderQuantity s.SalesAmount
vSales2013 s
inner join dbo.Product p on s.ProductKey = p.ProductKey
WHERE
p.ProductKey IN ( @ProductKeys )

Recall from the previous example that the ProductKeys parameter used two different fields for the product key value and label. Conversely, for the Category and Product subcategory parameters, it is unnecessary to have different fields. Figure 9-7 shows the Available Values settings for the Categories parameter where the ProductCategory field is used for both the “Value field” and “Label field” properties. Using the name rather than a key value to filter from a parameter typically works, as long as the values are unique and it is a relatively short list.

FIGURE 9-7: Available Values settings.

The ProductSubcategory parameter settings are similar to the Category, with the ProductCategory field used for the “Field value” and “Label value” properties. The ProductKeys parameter is essentially the same as it was in the previous report example, with the ProductKey used for the “Value field” and the ProductName for the “Label field.” Cascading parameters must be arranged in order of dependency, which, in this case, is Category Product Subcategory, and Product. Use the up and down arrows in the Report Data toolbar header to change the order if necessary.

Arranging Parameters in the Parameter Bar

SQL Server 2016 introduces a new report design feature that provides control over the placement of report parameters in the parameter bar displayed above a rendered report. Note that the parameter bar is displayed for reports in a report server configured for native mode.

NOTE In SharePoint integrated mode, parameters continue to be arranged vertically in a panel on the right side of the browser window.

The parameter bar is customizable. As shown in Figure 9-8, use the right-click menu to add and remove columns and rows to and from the grid, and then drag and drop the parameters into any cell. Right-click and remove unneeded rows and columns.

FIGURE 9-8: Using the right-click menu to add and remove columns and rows.

Managing Long Parameter Lists

Multi-select parameters typically work well when the user will select a manageable number of parameter items. There is no stated limit either to the number of items that can be included in a parameter list, or to the number of items that can be selected. The only control that you really have is to limit the items you display in the list. Both the number of values on this list and the number of selected items can affect report performance.

The sample database returns 397 products, which are included in the parameter list shown in Figure 9-9. Any more than this and you would see a noticeably longer report rendering time. Even more impactful is the effect of selecting many values and passing them into the query for filtering.

FIGURE 9-9: Products in parameter list.

Multi-select parameters always display a “(Select All)” item at the top of the list. Choosing this item checks the boxes for every item in the list, and unchecking it deselects all of the listed items.

WARNING Long parameter lists can affect report performance. Multi-select parameters include an item at the top of the list labeled “(Select All).” Choosing this item selects every item on the parameter list and passes all of those values into the report query. You cannot disable this feature, nor limit the number of items selected. You should limit parameter lists to a few hundred items.

Consider what happens if the “(Select All)” option were used in this sample report. The following query is executed after the parameter values are parsed, which explicitly includes every available ProductKey:

SELECT s.OrderDate s.SalesOrderNumber p.ProductCategory p.ProductSubcategory, p.ProductName, p.ProductKey, s.OrderQuantity, s.SalesAmount
FROM
vSales2013 s
inner join dbo.Product p on s.ProductKey = p.ProductKey
WHERE
p.ProductKey IN ( 486,223,224,225,484,447,559,473,472,471,
485,555,552,470,469,468,466,467,464,465,462,463,451,452
483,603,558,393,396,304,305,306,296,297,298,299,301,302
303,300,307,308,309,288,289,290,291,293,294,295,292,412
401,402,544,421,517,537,439,440,441,442,443,444,210,437,
438,241,242,243,244,245,246,247,248,249,250,251,252,211
238,239,240,415,407,408,547,424,520,540,497,498,499,500
494,495,496,492,554,523,487,601,556,391,394,550,531,532
533,534,551,524,525,526,527,410,397,398,542,419,515,535
279,280,281,282,283,284,285,286,287,253,254,255,256,257,
258,259,260,261,262,263,264,265,266,267,268,269,270,271
272,273,413,403,404,545,422,518,538,510,502,503,504,505,
506,507,508,509,493,553,521,232,233,234,229,230,231,226,
227,228,235,236,237,461,460,459,454,453,445,455,448,602
557,392,395,409,426,427,428,549,511,512,513,411,399,400
543,420,516,536,274,275,276,277,278,417,418,429,430,431,
432,433,434,435,436,414,405,406,546,423,519,539,522,219
218,478,449,528,348,349,350,351,344,345,346,347,358,359
360,361,362,363,352,353,354,355,356,357,364,365,366,367,
587,588,589,590,596,597,598,599,600,591,592,593,594,595
480,482,481,514,501,479,529,311,312,313,314,310,373,374
375,376,377,378,379,380,368,369,370,371,372,580,581,582,
583,317,318,319,315,316,381,382,383,384,385,386,387,388,
389,390,338,339,340,341,342,343,332,333,334,335,336,337
326,327,328,329,330,331,320,321,322,323,324,325,604,605
606,584,490,489,488,491,215,216,217,220,221,222,212,213
214,450,416,548,425,541,530,573,574,575,576,561,562,563
564,577,578,579,560,585,586,565,566,567,568,569,570,571,
572,446,477,476,475,474,458,457,456 );

This query was tested with and without the FROM clause, which revealed a return of the same set of results. Surprisingly, the difference in performance is negligible, and the verbose query takes only milliseconds longer. Perhaps if you use twice the number of product keys, a larger data volume, or you are running on a production server with other competing operations, you might see a greater impact. The point is that, under certain conditions, queries like this play a role in causing performance issues. So you should take steps to manage them. Using cascading parameters can help, because the user wouldn’t be able to select every product at once. The following technique may also be helpful.

All Value Selection

Rather than allowing users to select all of the parameter items if they don’t want to exclude any data, adding a custom item to the list can help you manage the query logic with more efficiency. In the next scenario demonstrated in the sample report Ch09 – All Parameter Selection 1, the goal is to provide users with the option either to select a single country or to return results for all countries. Figure 9-10 shows the parameter list showing an item labeled “(All Countries).”

FIGURE 9-10: Using “(All Countries)” to provide users with an option.

The query for my CountryList dataset looks like the following. Note that this is actually two SELECT statements.

SELECT
‘(All Countries)’ AS Country
UNION
SELECT DISTINCT Country
FROM SalesTerritory
;

In T-SQL, two queries that return the same set of columns can be appended using the UNION statement. This adds the “(All Countries)” row to the top of the list ahead of the country names from the SalesTerritory table.

The magic happens here in the following main report query where a logical decision is performed in the WHERE clause:

SELECT s.OrderDate, s.SalesOrderNumber p.ProductCategory, p.ProductSubcategory p.ProductName, p.ProductKey, p.StandardCost p.ListPrice, s.OrderQuantity s.SalesAmount
FROM
vSales2013 s
WHERE
inner join dbo.Product p on s.ProductKey = p.ProductKey
inner join dbo.SalesTerritory t on s.SalesTerritoryKey = t.TerritoryKey
t.Country = @Country OR @Country = ‘(All Countries)
;

Using an OR operator, one of two conditions must be met for a given row to be output in this query. Starting on the left side of the OR, the Country column value must match the selected @Country parameter value. If this branch is used, only orders for the selected country would be output. But what about the expression on the right side of the OR operator? That statement says, if the selected

@Country parameter value is “(All Countries),” let every row through. The OR operator trumps the other statement and just returns everything.

Here’s one more example, which is very similar to the first, but uses a numeric key rather than text. In this scenario, the parameter displays the sales country and region values concatenated together for the “Label field” (Figure 9-11) and returns the TerritoryKey for the “Value field.”

FIGURE 9-11: Using a numeric key rather than text.

The design of the Ch09 – All Parameter Selection 2 sample report uses a slightly different approach.

As shown here, like the previous example, the TerritoryList dataset has an extra row generated using – for the TerritoryKey and the text All Territories). This row is appended to all of the table values in the query using the UNION operator.

SELECT
-1 AS TerritoryKey
‘(All Territories)’ AS TerritoryName
UNION SELECT
TerritoryKey
CASE WHEN Country = Region THEN Region ELSE Region + ‘ ‘ + Country
END AS TerritoryName
FROM SalesTerritory
;

TIP When adding special-purpose items to a parameter list query, using a negative number for the key can ensure that it doesn’t duplicate the key value for an actual record.

Here is the main report query, which uses an expression on the WHERE clause to test the TerritoryKey query parameter. If the value is – , this indicates that the user selected the “(All Territories)” item. In this case, the OR operator effectively disregards the first filter clause and returns all rows.

SELECT s.OrderDate, s.SalesOrderNumber p.ProductCategory, p.ProductSubcategory p.ProductName, p.ProductKey p.StandardCost, p.ListPrice s.OrderQuantity s.SalesAmount
FROM
vSales2013 s
inner join dbo.Product p on s.ProductKey = p.ProductKey
inner join dbo.SalesTerritory t on s.SalesTerritoryKey = t.TerritoryKey
WHERE
s.SalesTerritoryKey = @TerritoryKey OR @TerritoryKey = -1

Handling Conditional Logic

Once you have mastered these essential parameter techniques, you can combine them to address real business reporting challenges.

The remainder of the chapter builds on these techniques to provide support for date range parameters, MDX query parameters, single and multi-value parameters and defaults, member ranges and sets.