RSS Feed
Download our iPhone app
Browse DevX
Sign up for e-mail newsletters from DevX


Advanced Ranking and Grouping with SQL Server Reporting Services : Page 3

Learn how to flexibly define a SQL Server Reporting Services report for ranking purposes. This article provides a workaround for custom grouping totals for top items and for jazzing the table up with some visual chart indicators.

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: True
ToggleItem: Grouper

Next, 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.

Close Icon
Thanks for your registration, follow us on our social networks to keep up-to-date