SSRS Total a Group

Kinetic ERP
1

Good afternoon,
I am editing a report to show totals for a list of order releases. The BAQ returns multiple rows for each order release (because of other things I am pulling in the BAQ). In my SSRS I simply group by a calculated field that makes a unique id out of the order line and release numbers. I can see each row of releases in my SSRS, but I would like to add a total to this group. If I just use the “Add Total After” group option, then I just get =Sum(Fields!Calculated_OpenQty.Value). However this returns incorrect totals because it is totaling every row in the BAQ, not just the unique rows in the group. I can use grouping to show the data rows I want in SSRS, but how do I total the group?
Thanks!
Nate

2

Wild guess… something like this?
=Sum(Max(Fields!Calculated_OpenQty.Value, “your calculated field group”))

3

Oooohh! I have never used Max with more than one parameter. I’ll check it out.
Thanks!

4

That syntax didn’t work. But I found this that seems to do the trick!
SSRS sum of distinct values - Stack Overflow
First I added a new index field to my BAQ.

ROW_NUMBER() OVER (PARTITION BY UniqueRequest ORDER BY UniqueRequest)

Then I changed the total expression in the SSRS to:

=Sum(Iif(Fields!Calculated_CountFlag.Value=1, Fields!Calculated_OpenQty.Value, 0)) * Fields!OrderDtl_UnitPrice.Value

It works perfectly!

1 Like