I agree with Doug Lane (blog | twitter), who pointed out in his T-SQL Tuesday post that it’s best if you use the database engine to calculate aggregates rather than use aggregate functions in Reporting Services. There are some things the database engine can simply do better, and we should use the best tool for the job. But an even better way to work with aggregated data, in my opinion, is to put data into a cube and let Analysis Services do the calculations. Either way, whether you use a relational source like SQL Server or a multidimensional source like Analysis Services, the point is that the back-end engines will always outperform Reporting Services. On the back-end, Analysis Services should outperform SQL Server if the cube and the report query are both designed properly.
That said, there is some trickiness involved when retrieving and using aggregations in reports. I was having a déjà vu moment as I was writing this blog and sure enough I found that I had touched on the subject several years ago in my blog post, Showing Server Aggregations in Reporting Services. The post was written in reference to Reporting Services 2005, but the principles still apply. In today’s post, I’ll revisit the same query but add in some pictures this time to help clarify.
If I create a table using the query from the referenced post, and if I add a total row using the Reporting Services SUM function, I get this result:
The SUM function would work just fine if I were to use an additive measure, like SalesAmount, but the SUM function doesn’t work at all with non-additive measures like Gross Profit Margin. It adds up the percentage values to produce the total row value but it really needs to compute the sum of sales and the sum of costs separately and then do the math to compute the correct result.
Instead of using the SUM function, we can use the AGGREGATE function instead. That is we replace this expression:
with this expression:
Where’s the Value?
When I make the change to the expression and preview the report, I find that I have a new problem – the total row is now missing a value completely!
The reason is that the Aggregate function requires me to use a query in design mode only, not in query mode. (At least that’s the case in Reporting Services 2008 R2. I don’t remember that this was true in Reporting Services 2005, but I don’t have an instance for that version handy at the moment to try it out. I think, from reading my prior post, that it worked in either mode, but I can’t prove it at the moment!) In other words, I can’t write the MDX manually in query mode – I must use the drag-and-drop interface to create my query. That’s fine for simple queries, but it won’t work when you need to add in calculations or otherwise make adjustments to the auto-generated query.
The Complete Solution
So once I switch the query to design mode like this:
And then preview the report, I now get the desired result.
As I mentioned above, this will only work when the query is in design more. And there’s another requirement–the tablix must have at least one group defined. The benefit, though, is a faster report than you’ll get from running a comparable relational query. You won’t notice the difference in the AdventureWorks databases, but in that case, Reporting Services will perform aggregate functions reasonably well anyway. But if your data source has high volumes of data, then using a combination of cubes and Reporting Services will deliver much faster reports.