Based on verbal feedback I got at the SQL Server Connections following my session, “Creating Reports with Reporting Services 2005 and Analysis Services 2005,” one of the most popular topics I covered was the use of server aggregations in a report. My friend Reed Jacobson touched on this on the Hitachi Consulting BI Blog, but he didn’t spell out the details there. Books Online doesn’t provide many clues either, nor can I find anyone else writing about the topic – my apologies if I missed something somewhere. Consequently, I thought it would be helpful to explain what the Show Aggregates button does in the MDX query designer and how to get server aggregates from the cube.

Why Use Server Aggregates?

First – why would you care about getting cube aggregates? Can’t you just use the Reporting Services aggregate functions (sum, min, max, count, etc.) to do the necessary calculations? Yes and no. The built-in aggregate functions in SSRS work just fine if your measures are all additive. But you run into a problem with semi-additive measures – like inventory counts or general ledger balances that add up nicely across products or accounts, respectively, but not across time. That is, you don’t add the inventory you had on the last day of January to the count you had on the last day of February (and any days in between if you track inventory more frequently) to determine your total inventory for March 1.

You also have a problem with non-additive measures, like ratios, because you can’t add up the ratios for each member of a dimension to determine the total for the dimension. For example, if you calculate a gross margin percent for each product sold as (Sales – Cost)/Sales, the gross margin percent calculation for all products is not the sum of each product’s individual gross margin. You must calculate total sales, subtract total costs, and then divide the result by total sales to get the correct value.

In both examples, the correct answer is obtained only by using server aggregations. After all, one of the key reasons to use a cube is to get aggregated values computed correctly no matter what type of measure it is: additive, semi-additive, or non-additive.

How to Define the Report Layout to Use Server Aggregates

The Show Server Aggregates button on the Data tab – using the MDX Query designer – is tempting, but it doesn’t appear to do anything. It’s job is simply to show you what server aggregates are available to the report, but it doesn’t tell SSRS to get them. Instead, all the work is done in the report layout which seems a bit counterintuitive to me. After all, the purpose of a dataset is to get data and the purpose of layout is to define where that data goes. In this case, the layout affects the MDX query in the dataset – an interesting way to go about it, but workable nonetheless.

The secret to accessing server aggregates is to use the SSRS Aggregate function in the report layout and to define a grouping in the data region. So, using the AdventureWorksDW sample cube that ships with SSRS, let’s start with a simply query with the following attributes: Sales Channel, Date.Calendar Year, Sales Territory Group. Add the Gross Profit Margin measure. Now add a table to the report layout and drag Sales Territory Group from the Datasets window to the detail row in the first column and drag Gross Profit Margin to the detail row in the second column and also in the table footer row. By default, the Sum aggregate function is applied, which will yield an incorrect result. But that’s okay for now. Let’s look at the MDX at this point. Switch back to the Data tab and click the Design Mode button to see the following query:

SELECT NON EMPTY { [Measures].[Gross Profit Margin] } ON COLUMNS, NON EMPTY { ([Sales Channel].[Sales Channel].[Sales Channel].ALLMEMBERS * [Date].[Calendar Year].[Calendar Year].ALLMEMBERS * [Sales Territory].[Sales Territory Group].[Sales Territory Group].ALLMEMBERS ) } DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS FROM [Adventure Works] CELL PROPERTIES VALUE, BACK_COLOR, FORE_COLOR, FORMATTED_VALUE, FORMAT_STRING, FONT_NAME, FONT_SIZE, FONT_FLAGS

The query returns values for each combination of Sales Channel, Calendar Year, and Sales Territory. In other words, the All member for each dimension is excluded from the query – no server aggregations are returned.

Now let’s get some aggregate action going. Switch to the Layout tab, and insert two groups into the table. In the first group, set the expression as =Fields!Sale_Channel.Value and set the second group’s expression as =Fields!Calendar_Year.Value. For this demonstration, I like to keep the layout simple, so keep only the group header or the group footer selected, but not both. Be sure to add the fields to the respective rows in column one to show the data in the report. Now drag Gross Profit Margin into the second column of each group row.

Does adding groups change the MDX query? Let’s see. Switch back to the Data tab. Nope – nothing’s changed. Switch back to the Layout tab for the final step. Change the Sum function to Aggregate in the group rows and the table footer row such that the expression now reads as follows: =Aggregate(Fields!Gross_Profit_Margin.Value). You might want to set formatting styles on the group rows to make them easier to identify and set the formatting on the gross profit margin column to p2 to make the values easier to read. When you’re ready, preview the report. If the final row in the report shows 11.43%, then you know the correct value is being retrieved from the cube.

If you were to use SQL Server Profiler to capture the query, you’d see a different MDX query than we see in the dataset. Switch back to Data and you can see the query hasn’t changed. Even if you click the Design Mode button to toggle back to the graphical query designer and then click it again to return to the generic query designer, you won’t see a change here. It all happens under the covers – the grouping and the Aggregate function tell Reporting Services that the MDX needs to be altered to get the aggregates from the cube.

Does this mean you can only see what’s happening if you use SQL Server Profiler? No. Now let’s try the Show Aggregages button. You need to toggle to the graphical query designer if it’s not visible on the Data tab. Then click the Show Aggregates button. The aggregates appear as shown here:


The null values really represent the All member for the attribute represented in that column. So the very first line should read Internet | All Periods | All Sales Territories | 0.4114… if the name of the All member were retrieved. If you now switch to the generic query designer (click the Design Mode button), you can see the MDX query has changed:

SELECT NON EMPTY { [Measures].[Gross Profit Margin] } ON COLUMNS, NON EMPTY {[Sales Channel].[Sales Channel].[Sales Channel].ALLMEMBERS * {[Date].[Calendar Year].[All Periods]} * {[Sales Territory].[Sales Territory Group].[All Sales Territories]}, [Sales Channel].[Sales Channel].[Sales Channel].ALLMEMBERS * [Date].[Calendar Year].[Calendar Year].ALLMEMBERS * {[Sales Territory].[Sales Territory Group].[All Sales Territories]}, ([Sales Channel].[Sales Channel].[Sales Channel].ALLMEMBERS * [Date].[Calendar Year].[Calendar Year].ALLMEMBERS * [Sales Territory].[Sales Territory Group].[Sales Territory Group].ALLMEMBERS ) } DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS FROM [Adventure Works] CELL PROPERTIES VALUE, BACK_COLOR, FORE_COLOR, FORMATTED_VALUE, FORMAT_STRING, FONT_NAME, FONT_SIZE, FONT_FLAGS

The query now includes the All members – All Periods, All Sales Territories – for the attributes that are used in the grouping of the table. You don’t need to follow these steps to get the query right in the dataset before you deploy. Reporting Services knows what to do when it sees the grouping and the Aggregate function. However, as the report developer, you may want to see the query generated to retrieve server aggregates and make modifications to suit your needs more specifically.