CODEWITHSUNDEEP
reporting-servicesaggregate-functions
vstrien
vstrien

Reputation: 2605

How to combine aggregates within a group with aggregates across groups within SSRS

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?

Upvotes: 13

Views: 20176

Answers (2)

WAXER
WAXER

Reputation: 21

You could have used Scope descriptors to identify the groups that the SUM is to be run against:

  • Category group is defined in tablix Row Groups.
  • DataSet1 is the name of the dataset.

Grouped Amount: [Sum(Amount)]

Dataset Total: SUM(Fields!Amount.Value, "DataSet1")

Percentage: SUM(Fields!Amount.Value, "Category") /SUM(Fields!Amount.Value, "DataSet1")

Upvotes: 2

vstrien
vstrien

Reputation: 2605

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.

Upvotes: 26

Related Questions