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
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.
Building the Rest of the Report
Click the Layout tab and drop a table control on the Body section. Set its datasource to your sales dataset, AW_Sales. Next, add a new group (by default SSRS names this table1_Group1). Ensure that both the group header and group footer are included by marking the checkboxes appropriately. This will be important for the aggregate workaround discussed later. Rather than specifying a field in the Group On Expression, you use the formula =Fields(Parameters!Grouper.Value).Value (see Figure 3).
 | |
| Figure 3. Grouping and Sorting Properties: Use this formula instead of specifying a field in the Group On Expression. |
The Parameters!Grouper.Value syntax is a reference to the dynamic grouping criteria, not simply a static reference to a field in your dataset, as the more familiar Fields!Fieldname.Value syntax does. For example, if you select “Product Name,” the top level will be grouped by name (the field reference); select Customer, and the top level will be grouped by Customer_Name.
Next, filter the grouping to the Top X values. There is an important difference between a filter of the entire dataset and a filter at the group level. The calculation here requires a test of the top sales (a sum of LineTotal). SSRS will not allow an aggregate formula in a filter of the table at this point. Therefore, your test must be at the grouping level. To accommodate this, click the Filters tab for table1_Group1, and create a filter with the following properties:
Expression: =IIF(Parameters!Limit.Value is Nothing, "",Sum(Fields!LineTotal.Value))Operator: Top NValue: =IIF(Parameters!Limit.Value is Nothing, 2147483647,Parameters!Limit.Value) | |
| Figure 4. Details Grouping: You will expose some relevant order detail information to users. |
The first part of the conditional statements bypasses the filter if the user displays all records. While you can provide an empty value on the text portion of the example, the second IIF statement in the preceding code uses a large default value (2147483647, the upper boundary of an integer) if the user doesn?t enter a limit value. Unfortunately, SSRS expects an integer there, so a blank space or double quotes won’t cut it. To ensure that the filter expression won’t fail, this example uses the upper boundary of an integer.
Lastly, click the Sorting tab for this group and use the following properties so the report shows the greatest sales volume first:
Expression: =Sum(Fields!LineTotal.Value)Direction: DescendingThe final grouping to add is for the detail line. From Table Properties, click Details Grouping. You will expose some relevant order detail information to users, but it will be summarized by sales order number (SalesOrderNumber) and customer name (Customer_Name). Figure 4 displays the grouping on this detail.
 | |
| Figure 5. General Layout of the Report: You will expose some relevant order detail information to users. |
Figure 5 shows the general layout of the report. The table header dynamically provides the grouping level selected with Parameters!Grouper.Label so report viewers can see the user-friendly name you’ve provided instead of a geeky database column name. The group header will display the appropriate value with the expression Fields(Parameters!Grouper.Value).Value), exactly the same as the group property. This example also appropriately changed the name of this cell to Grouper.
Aside from the summary of LineTotal at the end, you’ll notice a Rank column. This is where you display the ranked number position for the group currently displayed. What good is a Top 50 filter if the user can’t easily tell who is ranked 10th and who is 23rd?
The dynamic expression you would typically use to provide the ranking is:
RunningValue(Fields(Parameters!Grouper.Value).Value,CountDistinct,Nothing)RunningValue is an SSRS expression that aggregates values at runtime. Here, inspect each Grouper value, get a distinct count (which should be 1 since you are grouping, right?), and set the scope of inspection. You could inspect a dataset or table, but because you are interested in the current scope of this group, the value is set to Nothing. RunningValue will merely increment each grouping so you can display an ordered set of values next to your selection.
Maintaining Order
There’s just one issue with the ranking formula provided in the previous section: It is based on a simple iterative count, so the first grouped line with be 1, the next will be 2, and so on. Suppose you later need to add some interactive sorting to the report, so after the display of the Top 20 customers in descending order of sales, the customers are listed in alphabetical order. Unfortunately, the rank would not be preserved and the order displayed would be incorrect. Because the ranking value is merely a simple counter, it would appear as though the first alphabetically sorted customer had the most sales.
To fix that, you need to add some code to the report to set the initial ranked values and redisplay them whenever the report gets re-sorted. You will need to add the code in Listing 2 to the code block in the report properties. Then, your ranking cell will call this function with the following expression:
=Code.GetSetRankArray(Parameters!Grouper.Value, Parameters!FromShipDate.Value , Parameters!ToShipDate.Value , Parameters!Limit.Value, RunningValue(Fields(Parameters!Grouper.Value).Value, CountDistinct,Nothing),Fields(Parameters!Grouper.Value).Value) | |
| Figure 6. Setup for the Sort on the Grouper Column: Add some interactive sorting to take advantage of the Grouper routine. |
This ensures that whenever the report refreshes because of a sort, the ranked values will initialize for the first display. However, each subsequent refresh based upon a column sort will retrieve the stored values based upon the Grouper value. GlobalRefresh() will set global variables to store the parameters of the user selection, such as dates, grouper values, and top limit. GetSetRankArray() will manage the storage of ranked values by Grouper value and store them in an array. They will be retrieved by using the GetRankArray() function if the report is re-sorted. However, if the user refreshes the parameters and reruns the report, the global variables will reinitialize.
Now you can add some interactive sorting to take advantage of this routine. Select the column header that holds the Grouper label. In the textbox properties on the Interactive Sort tab, select ?Add an interactive sort?’ and use the sort expression =Fields(Parameters!Grouper.Value).Value. In the “Evaluate sort expression” section, be sure to select the radio button to “Choose data region or grouping” and select table1_Group1, your group to re-sort.
Figure 6 shows the proper setup for the sort on the Grouper column.
Add another interactive sort on the column header above the LineTotal so you can do the same type of operation on that column as well.
Totals and Running Values
In the detail, you’ll notice that a Sum() has been added to the LineTotal because you happen to be grouping on this level. But you are probably more interested in the seeming redundancy in the three grouped footer lines, as well as the duplicated table below. It’s part workaround and part value-add.
Here’s the challenge: Filter on a group for the top 10 products and drop a sum in the table footer. You’d expect to see a total of the 10 products, right? Unfortunately, SSRS will return the sum of the entire unfiltered set. What’s worse, if you try to hack your report footer, you’ll find that certain aggregations and calls to other ReportItems storing your value (a common SSRS hack technique) will fail. The workarounds that follow will provide a running total, similar to the ranking calculation.
Under the Total Due column, a cell named TOP_COL2 has the expression for calculating the total:
=Code.StoreRVTop(RunningValue(Fields!LineTotal.Value, Sum, Nothing))This will sum the value on each footer and keep a running value. (It also runs a custom function in code-behind, but you will get to that later.) A row is added underneath to meet another common need when measuring performance. Specifically, managers typically like to see how others who aren’t included in the top selection performed. The expression for “All Others,” which is appropriately named NOTTOP_COL2 (because it holds values not in the Top, get it?) is:
=Code.StoreRVNotTop(Sum(Fields!LineTotal.Value,"table1") - ReportItems!TOP_COL1.Value)Under normal circumstances, you’d set some conditional visibility so your Running Total group footer would display only on the very last row (like the 10th in a Top 10). Unfortunately, you need to cover one more workaround: when you have more or fewer than 10 in a Top 10 set! It’s easy to have fewer; perhaps only eight products return in a request for the Top 10. But how might you have more than 10, you ask? In the case of ties, it is very likely that the 10th and 11th grouped items share the same value. Since SSRS preserves ties in ranking filters, you need to do the same and cannot blindly cutoff visibility based upon the X ranking selected by your user.
Since you really have no idea where the report will end, a new table serving as your total rows is added beneath the first. Next, you take the summary calculations above and stuff them into variables using some code-behind with a reference to Code.StoreRVTop() and Code.StoreRVNotTop() since SSRS doesn’t allow you to reach into the first table to retrieve the aggregates (try it, and you’ll get an error). You can be sure that the second table will always render directly below the first regardless of the number of rows returned. You could use a few textboxes, but it’s nice that you can simply copy/paste the first table and render the same exact layout (widths, fonts, etc.) with a few keystrokes. This ensures that my second table lines up identically with the first. An end user would never know that two tables were required to render the report.
If you take this approach, some light housekeeping is required on the second table. You need to remove the detail rows, the header columns (they are unnecessary), and all of the filtering and interactive sorting. You then must change the dataset source to ShipDates, which ensures the return of one?and only one?record.
Because the footer rows in the first table merely store values?and they will be hidden?you can add some notes that might be useful to a developer viewing the report design. Although it will never print, the hidden row text is set to gray so a report developer can quickly see your reporting logic. Keep that tip in your mind the next time you add some annotation to a report.
Since you can’t reference aggregates from the rows in the first table, your second (or “totals”) table simply retrieves the values from the variable using code-behind as well, with Code.getRVTop() and Code.getRVNotTop(). The textbox names used are TOP_COL1, NOTTOP_COL1, and GT_COL1 (for the grand total).
To complete the process, you merely set a few visible properties on the totaling table. (Figure 7 shows the grouping icons.)
 | |
| Figure 7. Grouping Icons: Set a few visible properties on the totaling table. |
Click the detail row icon (the button with three dashed lines) on the first table and set the visibility properties as follows:
Hidden: TrueToggleItem: GrouperNext, select both Group 1 footers and set their visibility to Hidden using the conditional value in Listing 2.
On the second table, ensure that the Grand Total line is always visible. You’d like to see a total of the rows above whether the user decides to limit the output or not. The two subtotal lines directly above have their Hidden property set to =Parameters!Limit.Value is Nothing, which ensures the rows will always be hidden if the user hasn’t selected a Top limiter, but they will display for a ranked filter selection.
Jazzing Up the Report
You can add one last visual cue to note the difference between top sellers and all others with a simple inline bar chart that grows relative to the difference. Place one chart in the footer row for the “Top” total and one in the row for “All Others.” Set the dataset to the same as your first table, AW_Sales. For the first chart, set it to Simple Bar and add a new Data Value. The Value should be set to =ReportItems!TOP_COL1.Value, which is your total for the ranked set. On the Edit Chart Value property page, click Point Labels and enable “Show point labels.” Then enter the data label as follows:
=IIF(ReportItems!GT_COL1.Value<>0,ReportItems!TOP_COL1.value/IIF(ReportItems!GT_COL1.Value<>0,
ReportItems!GT_COL1.Value,0),0)Set the “All Others” row chart to Simple Bar as well. Add a value with the expression =ReportItems!NOTTOP_COL1.Value. On the Edit Chart Value property page, click Point Labels and enable “Show point labels.” Then enter the data label as follows:
=IIF(ReportItems!GT_COL1.Value<>0,ReportItems!NOTTOP_COL1.value/IIF(ReportItems!GT_COL1.Value<>0,
ReportItems!GT_COL1.Value,0),0)In the Chart Properties for both charts, select the Y axis tab and set the scale minimum to 0 and scale maximum to =ReportItems!GT_COL1.Value. You need to set the maximum to the total number of sales for the entire set so there is some consistent perspective.
Add a little gradient coloring to the Value (Edit button ? Appearance tab ? Series Style button ? Fill tab), and you now have a visual indicator for the relative difference in sales. You now have a flexible report that limits the grouping, while providing end users with the flexibility to drill into detail. Figure 8 and Figure 9 show some of the options users have in crafting reports and the preservation of ranked values when sorted on a different column.
 Figure 8. Custom Grouping by State: You now have a flexible report that limits the grouping, while providing end users with the flexibility to drill into detail. | ? |  Figure 9. Custom Grouping by Customer: You now have a flexible report that limits the grouping, while providing end users with the flexibility to drill into detail. |
A Scalable Report
It’s often important for report developers to look beyond users’ immediate needs and create solutions that scale to accommodate many requirements. Without getting into overkill, this article presented a good example of providing user inputs so a wider audience can get value from your reports, and it is easily scalable when users need a different perspective of their data.
