Part VII: Filtering and Parameterization

• Multiple Criterion Report Filtering

• Using Multi-Value Parameters with a Stored Procedure

• Using Multi-Value Parameters with a Subscription Report

• Parameterized Top Values Report

• Cube Restricting Rows

• Creating Custom Sorting Reports

• Filtering User-Specific Report Data

VII.1. MULTIPLE CRITERION REPORT FILTERING

Report design requirements may call for complex combinations of parameter values used to filter report data. Using Transact-SQL, you should be able to handle practically any advanced filtering criteria and filter the data before it reaches the report server. However, if you need to use report filtering to provide the same kinds of filtering support against data already cached by the data set query, the Report Designer has some significant limitations in this area. By employing some advanced expression logic or a little bit of custom code, practically any filtering logic can be implemented in the query or using dataset filtering within the report.

Product Versions

VII.2. USING MULTI-VALUE PARAMETERS WITH A STORED PROCEDURE

SSRS includes a multi-value parameter option that enables users to select one or more options from an embedded query. This is a simple feature, similar to the filtering format in SharePoint, which is available when building a report as a property of the parameter options. By itself, this feature provides users and developers with an advanced filtering option; however, this also presents a challenge because Reporting Services uses an array to store a multi-value parameter's value. Since T-SQL doesn't directly support arrays, passing in a string value of, for example, "1, 2, 3, 4, and 5" will cause the stored procedure to fail with a data type conversion error. This makes it difficult to pass the parameter into the T-SQL query and forces you to bring back the entire record set and do the filtering at the table level. If you are working with a simple query, this isn't too big of an issue, however, as your dataset result grows this will present a performance issue that will need to be addressed.

A better solution is to use a stored procedure and a user-defined function (UDF) to pass the array into T-SQL and keep the filtering at the T-SQL level. The function presented in this recipe allows you to pass in a string value of delimited values and splits the values by the designated delimiter. The result allows you to use a simple "IN Clausem" that will avoid the data type conversion error.

VII.3. USING MULTI-VALUE PARAMETERS WITH A SUBSCRIPTION REPORT

The "Using Multi-Value Parameters with a Stored Procedure" recipe demonstrated how easy it is to pass multiple values to a stored procedure using a T-SQL User Defined Function (UDF). This enabled you to maintain dataset filtering at the database level rather than directly on the report. However, it would be nice to automate this procedure and perform the same functionality for both a regular report and a subscription report. To accomplish this, this recipe shows you how to create an additional UDF—one that returns multiple values to a comma separated string. You also need to be a little creative with parameters, hiding some during the normal viewing of the report and populating others during a data-driven subscription report.

Product Versions

VII.4. PARAMETERIZED TOP VALUES REPORT

Top ranked lists are a common type of report. In many cases, business users may want to see only a specific number of records at the top or bottom of a range, effectively reporting the best or worst items in ranked order. We see ranked lists in all types of applications to answer questions like "What are the top five best selling products?" or "Who are my five worst producing sales people?" This is a relatively simple matter using a top values or top ranked query. It may be even more useful if users could select or enter the number of items to return in the report.

Product Versions

VII.5. CUBE RESTRICTING ROWS

This recipe is another step toward the complete SSRS Cube Browser recipe that begins with the "Cube Dynamic Rows" recipe earlier in this book.

One of the challenges in creating dynamic reports is the user can accidentally request a huge amount of data. In this recipe, you take a quick look at how to add functionality for restricting the number of rows returned in a report.

VII.6. CREATING CUSTOM SORTING REPORTS

This recipe consists of a series of related report recipes that demonstrate various techniques for creating reports that enable users to dynamically modify the sort order of report content. Traditional reporting solutions typically contain several different reports with only slight differences in design. A "Super Report" is one report that replaces multiple reports by implementing dynamic features. This design pattern replaces several reports with one more capable report with features enabling custom sorting, grouping, and filtering so that one flexible report design meets the needs of many users. The techniques vary in flexibility and complexity and there are pros and cons for each. In an effort to simplify, the following list provides a brief overview of these techniques and corresponding design recipes.

• Parameterizing Custom Sorted Queries: This technique uses conditional logic in the dataset query to execute a conditional query, based upon a parameter selection. This is a very flexible option that can be used to address complex business requirements and complex sorting criteria.

• Parameterizing the Order By Clause: An optimized extension of the preceding technique, this approach is straightforward and efficiently designed. It may not offer the same level of flexibility; however, it allows users to define primary and secondary sorts without duplicating the entire dataset query.

• Custom Sorting in Tablix Groups: This technique places the conditional sort logic in the report rather than the query. The advantage is that sorting can be managed within table and matrix groups rather than the entire dataset query that feeds data to the report items. It may not offer the same level of control and conditional logic but it can be more efficient since the data is sorted on the report server, using cached data on the report server.

• Using the Interactive Sort Feature: This is the easiest sorting method to implement. It requires no conditional expressions, programming, or custom query design. Interactive Sort is a feature added in SSRS 2005 and it applies to textboxes in the table header row. You can control the group level scope of sorting but there are limitations to this feature. Small sort order direction arrows are displayed in table column headers but this interface may not be customized. Sorting is limited to only one column at a time.

  This feature is very fast and efficient due to optimizations built into the SSRS server architecture. It not only sorts on the report server data cache, but also implements client events and page level caching.

• Creating a Custom Interactive Sort: This technique is a custom emulation of the Interactive Sort feature that enables you to apply more explicit business rules and complex sorting logic. Sorting is implemented in the report groups using expressions, so the sorting can apply to data cached on the report server. This technique also allows you to customize the report user interface. Report actions are used to drill-through to the same report and modify parameter values used in a table, tablix, or group Sort property expression. This approach is efficient, very flexible, but takes a little more work to design.

VII.7. FILTERING USER-SPECIFIC REPORT DATA

Due to security requirements, a lot of organizations have a need to display data specific to the user accessing the report. With increasing emphasis on standards compliance, many organizations are taking the protection of private information more seriously. There are a couple of different techniques for creating user-specific reports. This example explores two techniques.

Product Versions