The problem:
The clients have created summary datasets that is returning duplicate records within the dataset, when added to SQL Server Reporting Services (SSRS) tablix or matrix report objects.
Although with using the various features and functions of these SSRS objects, one can hide this, but this still left the problem of totaling the unique records.
The Solution:
Unfortunately, SSRS does not come with built in Sum Distinct aggregate function.
As a workaround, if one uses the logic, that to create a Sum Distinct calculation, with a duplicate dataset, one only wants to Sum on of the values.
- Add the Tablix/Matrix to the SSRS report canvas
- Apply the neccessary group(s) to the object
- Add the Summary Fields to the Group Header/Footer sections
- To achieve this in SSRS, use the formula:
=SUM(MAX(Fields!FieldName.Value, "GroupName")
If you have any questions, leave us a comment below, or need any assistance, please do not hesitate to Contact Us |
