Home Sum Distinct SSRS by Group (by two fields)
Reply: 0

Sum Distinct SSRS by Group (by two fields)

user1457
1#
user1457 Published in May 22, 2018, 11:30 am

I can't create sub groups, I just need to figure out how to code a sum statement in SSRS for a given group (and right now that looks like this) =Sum(Fields!seats.Value) however the group repeats data it has information, by customer_no, order_no, seats, and zone. In the case that one customer has multiple zones the totals (seats) repeat.

for example
customer_no  order_no  seats  zone
1            1          2     5
1            1          2     32
2            2          3     3
3            3          2     4
4            4          1     11
4            4          1     8

Totals should be: 
4            4          8 
what i'm getting is 
4            4          11

in cases like this, what needs to happen is the sum has to add for customer 1 and 4 their seat totals only once. there is nothing i can do about how the code is written and how the data is returned (alas). currently my sum statement just totals all the seats for each grouping. which then causes incorrect totals.

I've seen a couple of posts out there with similar questions but nothing solving this issue.

please advise.

** one idea i had if this can't be done, would be to highlight the row with the issue at hand for one customer/order combo when there are multiple rows i could just highlight them to have the user review that separately not sure how to do that either. **

You need to login account before you can post.

About| Privacy statement| Terms of Service| Advertising| Contact us| Help| Sitemap|
Processed in 0.328286 second(s) , Gzip On .

© 2016 Powered by mzan.com design MATCHINFO