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 2

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.

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 N
Value: =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: Descending

The 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 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:

   Parameters!FromShipDate.Value , Parameters!ToShipDate.Value , 
   Parameters!Limit.Value, RunningValue(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.

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