SSRS grouping and getting the totals

Kinetic ERP
1

I am preparing a report using Microsoft report builder. My BAQ data appears as below

JobNum ProdQty PartNum Description RequiredQty Backflush
J1 2 P1 Part1 10 FALSE
J1 2 P2 Part2 20 FALSE
J1 2 P3 Part3 30 FALSE
J1 2 P4 Part4 40 TRUE
J2 3 P1 Part1 10 FALSE
J2 3 P2 Part2 20 FALSE
J2 3 P3 Part3 30 FALSE
J2 3 P4 Part4 40 TRUE
J2 3 P3 Part3 30 FALSE
J2 3 P4 Part4 40 TRUE

I want my report to appear as below

Job Num ProddQty
J1 2
J2 3
Total 5
PartNum Description Requiredqty
P1 Part1 20
P2 Part2 40
P3 Part3 90

The problem I am facing is that the total production qty is coming as 26. Rest everything in the report is coming as I require. Could any body suggest how to get the correct total.

2

Use Max(ProdQty) and not Sum?
Use a Sum of Max() on JobNum. Sounds like you’re doing a Sum of all lines.

3

Hi Smith;
Thanks for the suggestion. I did as below for the first table.

I grouped the table on the basis of jobnum using the row group properties. Then I inserted a row outside group-below as I don’t need the value after every jobnumber. Then in the new row I gave the expression as below
=Sum(Max(Fields!JobHead_ProdQty.Value))
My result is 1
ET_JOBMTL.rdl (47.1 KB)

4

Try this reporting services - How to sum the Group totals only? - Stack Overflow

2 Likes
5

On the group that holds the data, add a second Group By and it will group on both fields.

6

Exactly how I have done this in the past. :smiley:
Sorry for not telling you about adding a scope.

1 Like
7

Thanks Theodore Koch and Clint Smith for the support. It worked and I got my result as required.

Wish you all the best.

1 Like