Crystal Reports & SQL Reporting Services Training Consultants
Account Log in:
Username:
Password:
Don't be afraid to say "I don't know"
 
Institute of IT Trainers - Freelance Trainer of the Year 2006 & 2009
Liverpool Business Connect Member
  Maximum Impact Solutions Limited - Reporting Solutions, Creating Answers
Reporting Solutions - Creating Answers, Crystal Reports, Dashboarding (Xcelsius) & SQL Reporting Services

The Maximum Impact Solutions Blog Feed ME!!!!

02
June

How Do I Do a Sum Distinct Records in SQL Server Reporting Services?

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.

  1. Add the Tablix/Matrix to the SSRS report canvas

  2. Apply the neccessary group(s) to the object
  3. Add the Summary Fields to the Group Header/Footer sections

  4. 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

MAKE A COMMENT

Name *

Email Address *

Comment *

To prove you are human what is:

59 + 31 =