ften when developing reports in SQL Server Reporting Services (SSRS), mixing summary (or grouped) data with transactional detail becomes a requirement. Show a sales manager how her top products or customers are performing, and she might want an easy way to drill down into the data to examine the root cause. While enabling flexible grouping and displaying detail records in a drilled-down, on-demand way are not particularly difficult, combining custom ranking and aggregations on filtered table groups in table-based SSRS reports often can be.
Suppose you have a list of 1,000 customers that you group by state on a report that is sorted in descending order by sales volume. Generally in these types of reports, it's nice to illustrate a ranking next to each row, not just the resulting data. To do so, you need a combination of functions.
|Figure 1. Parameter for the Custom Grouping: The parameter for the custom grouping is named Grouper.|
Using SSRS, this article demonstrates how to flexibly define a report for ranking purposes that allows you to modify the common "Top 100" style report to a "Top X" version that can satisfy the needs of users with a variety of groupings. The article also provides an example of a workaround to provide a custom-grouping total for the top items and to jazz the report up with some visual indicators.
Setting Up the Report
To create the report example for this article, simply create a new report solution in Business Intelligence Development Studio (BIDS) and then add a new report. Add two datasets to the new report. On the Data tab, select <New Dataset>, and then create a new Data Source connection to the server where AdventureWorks is located. This example uses the AdventureWorks database supplied with SQL Server 2005 to select some sales data that is joined with related attributes, such as products and customers. The resulting dataset is named AW_Sales. (See Listing 1 for the complete select statement.)
The dataset ShipDates (see below) will be the default source for the input parameters FromShipDate and ToShipDate. You may find it useful to bookend the earliest and latest dates (ship, order, cancel, invoice, etc.) rather than simply providing a blank field to users in the report preview. Simple tweaks and defaults such as this ensure a more user-friendly querying experience.
Select MIN(ShipDate) as Min_Ship_Date, Max(ShipDate) as Max_Ship_Date from Sales.SalesOrderHeader;
For the Top Sales report, you will provide the user with two levels of flexibility:
- Selecting from a set of varying attributes on which to group
- Defining the Top X as any integer the user desires
The report itself is a simple table control with one level of grouping for the user-selected value with totals. The details will display order-level information.
The parameter for the custom grouping is named Grouper (for lack of any creativity). Figure 1 shows a few items that are populated for the user.
|Figure 2. Parameters Displayed to End Users: Here are the final parameters as displayed to end users.|
This example allows a ranking of sales by product, customer, territory, or state. Note that the field references made in the Value property for each option are case sensitive and must exactly match any fields you'd like to offer up for optional grouping. Additionally, the fields must be defined in your dataset; you can't just arbitrarily request a field be grouped if it is not included in and returned to the report.
Before you build the actual guts of the report, take a quick look at the filters you will be providing by clicking the Preview tab. Figure 2 shows the final parameters as displayed to end users: date pickers, the grouper selection, and a "Limit to Top" option that takes nulls.
The last option makes this report interesting. Leaving the "Limit to Top" value null will allow this report to behave as any standard sales report. When a user enters a value, however, you will limit the output to only the top products or customers, no matter how many lines of detail might fall below in the order history.