Read the latest from our developers as they share their real world experiences…

If you are working with SSRS, there will come a time when you need to unavoidably duplicate data onto each row of a dataset.  Perhaps you are adding new custom fields in a query (such as tracking row numbers), or you are joining one table against only a few records that each apply their data to multiple targets.

This becomes a more frequent issue when working with Dynamics CRM Online, as we cannot use a SQL query as a datasource.  Comparatively speaking, FetchXML is very constrained for Joins, Unions, and complex Where clauses, so we must work more magic on the side of SSRS.

Luckily beginning with SQL Server 2008 R2, reporting services now allow use of “nested aggregates”, and if you are using CRM Online, you are automatically on a very recent version of SQL server.  However, their usage is sometimes confusing.  I focus here on the following scenario: how can I treat some values for each row as Unique when I am subtotaling and totaling values from nested Groups?

As an example, let’s say your dataset pulls a list of all Opportunities from CRM.  In your Tablix, this set is then grouped and broken out by Team, and then into a child group for Owner.  Finally, you are joining this data to Goal data so you can calculate the progress of salespeople for the year, and you are only including the First values present, because Goal data is only one value per salesperson.  Let’s assume you’ve worked out any issues with your FetchXML query and it is “as good as it gets”, and that you have worked out your percentage values successfully for all rows and totals.

So, you have something like this:

Team	Owner	# of Opportunities	# Won	# Lost	Yearly Goal	% of Goal
Alpha	Major Malfunction	10	3	4	6	50%
	Colonel Candor	12	2	1	6	33.3%
	Team Total:
Beta	Sergeant Simple	8	1	0	6	16.7%
	Team Total:
	Grand Total:

 

Look good so far?  Now, let’s assume you are simply trying to Sum up the Team row totals.  This is what you will most likely get, and it may or may not surprise you:

Team	Owner	# of Opportunities	# Won	# Lost	Yearly Goal	% of Goal
Alpha	Major Malfunction	10	3	4	6	50%
	Colonel Candor	12	2	1	6	33.3%
	Team Total:	22	5	5	132	3.8%
Beta	Sergeant Simple	8	1	0	6	16.7%
	Team Total:	8	1	0	48	2.1%
	Grand Total:

 

After looking at the numbers for a moment, you should realize what is going on.  Your Sum function in Yearly Goal is adding the same value for each Opportunity in your data, instead of from each visible row.  Other aggregate numbers like “# Won” and “# Lost” are simple, but they are intended to add up each row, so how can you fix this?  You can’t modify your FetchXML further, can’t Sum up repeating Textbox values, and you try many other Expression combinations, even calling out the Scope of a Sum (e.g. SUM(Fields!Yearly_Goal.Value, “Team_Group”)).  But, you only run into one error or bad result after another, and remain confused about your options.

So here comes “nested aggregate” functionality to the rescue, with some caveats.  The subtotal Expression will probably seem simple enough after you think about it:

We are taking the Max value from inside the Owner Group level, and then Summing it up at the Team Group level.  This code must sit as the Expression inside the Team Group, outside of the Owner Group, but it will correctly Sum the unique values.

So then, how do we fix the Grand totals, which sits outside of both Groups?  After trying other things and receiving errors about Scope, you might think at first that this would work:

=SUM(MAX(Fields!Yearly_Goal.Value,"Team_Group"))

Or this:

=SUM(MAX(Fields!Yearly_Goal.Value),"Team_Group")

However, both are giving confusing results.  Knowing that you can call out Dataset names as well, you may then also try:

=SUM(SUM(MAX(Fields!Yearly_Goal.Value,"Owner_Group"),"Team_Group"),"Opportunity_Dataset")

You are on the right track, but the above only gives an error.  The correct Expression is:

Again, it seems simple at the same time as it seems confusing.  The above Expression finds the Max value from what is Grouped by