Sum Values based on Distinct Guids

0
votes

I am having trouble getting the SUM of a column of values in SSRS.

My SQL query returns something like the following:

ID          Total
Guid1    4
Guid2    6
Guid3    1
Guid2    6
Guid4    6
Guid1    4

I have it grouped in my SSRS Report so it appears like this:

ID          Total
Guid1    4
Guid2    6
Guid3    1
Guid4    6

I need the sum of the Total column, but it wants to sum the ungrouped set of values instead of the grouped set. So I get

Sum(Total) = 27

But I WANT 

Sum(DISTINCT? Guid Totals) = 17

I've tried the solutions that can be found on this page, with no luck: https://social.msdn.microsoft.com/Forums/sqlserver/en-US/3c2626d8-e835-4bba-b946-7d6796e9c617/sum-distinct?forum=sqlreportingservices

I've also tried to add code behind the scenes that stores the distinct ids in a hashtable, and if it's not in the hashtable, add it to a global total; which I call from each row of the table, and then display the global total at the bottom, but that's not working either.

Any ideas?

1
sql-serverreporting-servicesssrs-2008-r2ssrs-2012ssrs-tablix
1. use invisible table put your dataset in without grouping, and get SUM. 2. modify your query in new dataset, select sum(total) from table – Marco Bong

1 Answers

0
votes

pretty sure you can Sum the grouped field in ssrs.

=SUM(MAX(Fields!value.value,"GuidGroupName"))