reporting services - How to combine aggregates within a group with aggregates across groups within SSRS

Question

With this dataset:

Category | Amount
A        | 5
A        | 3
B        | 6
B        | 2
B        | 1
C        | 7

I want to create a tablix grouping on category, displaying the percentage of the total amount:

Category | Percentage
A        | 33%
B        | 38%
C        | 29%

Which should be a simple calculation:

Category | Percentage
A        | ((Sum of Amount within group / Sum of Amount across groups) * 100)%
B        | ((Sum of Amount within group / Sum of Amount across groups) * 100)%
C        | ((Sum of Amount within group / Sum of Amount across groups) * 100)%

But I can't figure out how to do that within Report Designer (SSRS) - whenever I create a row grouping on Category, I can get the sum within the group with =Sum(Fields!Amount.Value). But how to get the sum across groups from a cell within the group?

Answer 1

I'll answer my own question.

From within any expression, it's possible to perform lookups in all datasets. Through this way we'll get the data:

LookupSet(SourceFieldToCompare, TargetFieldToCompare, ResultField, DataSet)

Now, let's raise the bar for the question and say the data is grouped in yet another dimension, months - like this:

Category | January | February | March
A        | 33%     | 37%      | 35%
B        | 38%     | 36%      | 37%
C        | 29%     | 27%      | 28%

Say the dataset mentioned in the question is named 'categories'. Now, call on the LookupSet function (Reference):

LookupSet(Fields!Month.Value, Fields!Month.Value, Fields!Amount.Value, "categories")

(keep in mind that the first "Month" is linked to the dataset inside the tablix, and the second "Month" in the second argument is "Month" from the "categories" dataset!)

There remains one problem: LookupSet returns Object types, which Sum won't eat. You need to use a custom aggregate, (custom code is added in "Report Properties"): (Source)

Function SumLookup(ByVal items As Object()) As Decimal
  If items Is Nothing Then
    Return Nothing
  End If

  Dim suma As Decimal = New Decimal()
  suma = 0

  For Each item As Object In items
    suma += Convert.ToDecimal(item)
  Next

  Return suma
End Function

Now, by calling Code.SumLookup on the LookupSet function the sum is calculated of all fields.