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

by

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.

More Related Contents:

  1. SSRS multi-value parameter using a stored procedure
  2. Compare SQL Server Reporting Services to Crystal Reports [closed]
  3. SSRS 2008 R2 – SSRS 2012 – ReportViewer: Reports are blank in Safari and Chrome
  4. Does the iif function compute both paths in SSRS or is it short-circuited?
  5. SSRS Expression Divide by Zero Error
  6. Reporting Services export to Excel with Multiple Worksheets
  7. When to use RDLC over RDL reports?
  8. SSRS 2005 Set SimplePageHeaders on the report instead of the server?
  9. SSRS 2008 R2 – SSRS 2012 – ReportViewer: Reports in Safari/Chrome but works fine in Firefox/Internet Explorer 8… why?
  10. SSRS – How to build a simple multi-column report?
  11. SSRS – How to continue data to next column?
  12. SSRS distinct lookupset function
  13. SSRS Conditional Formatting Switch or IIF
  14. Pivot a table in reporting services?
  15. Access Page number in report body In SSRS
  16. The permissions granted to user ‘ are insufficient for performing this operation. (rsAccessDenied)”}
  17. hide/remove header and footer
  18. Apply multiple functions to multiple groupby columns
  19. Aggregate columns with additional (distinct) filters
  20. Function to Calculate Median in SQL Server
  21. Left Outer Join Not Working?
  22. Concatenate multiple result rows of one column into one, group by another column [duplicate]
  23. Is there ANY_VALUE capability for mysql 5.6?
  24. Best way to count rows by arbitrary time intervals
  25. SSRS 2014 report deploy issue
  26. Changes to parameter not showing on Report Server after deployment
  27. MySQL: GROUP_CONCAT with LEFT JOIN
  28. Could not load file or assembly ‘Microsoft.ReportViewer.Common, Version=11.0.0.0
  29. SQL: difference between PARTITION BY and GROUP BY
  30. replace NULL values with latest non-NULL value in resultset series (SQL Server 2008 R2)

Leave a Comment