I have a row tablix that is Group On PartNum and then a text field total sum of each PartNum. When I do the Group On, it works correctly but the totals for the PartNum do not sum correctly. It seems to be pulling in only the first value and not the sum. How do I do a Group On and Sum Total on the same row group?
Sample data:
Record | Group | Value
1 | A | 100
2 | A | 50
3 | A | 95
4 | B | 0
5 | B | 25
6 | B | 30
Group | Row Value Total
A | 245
B | 55
You need to sum the field on the row group. SUM(Value)
When I try that, it seems to be doubling or tripling the sum amount field.
Can you post a picture of your Row Groups?
It doesn’t always double or triple consistently on all the group by PartNum so it seems to be specific to the rows being grouped.
Correct, it doubles, triples, quadruples, etc based on the detail lines that are being returned in the report tables. The grouping gets tricky based on what fields from the detail are showing. Basically, if you create a row group, you should not display that field in any rows below it. Or, not include the detail group if they are not needed.
So you can’t create a row group and have the sum total be in the same row group in that row?
No, you can.
There are 2 things that can happen that causes sums to be off.
Do you have a detail row in your report? If you do, is it hidden? If it is hidden, I recommend un-hiding it to see what data is returned.
It seems like if you did a Row Groups by PartNum and then did a sum on field in the row group, it should only total whatever was Grouped By. In my example below it should list one row of Group A and the sum total in another field of 245. I am hiding other PartNum but that shouldnt effect the detail row since it is one for one. In my example below, you could have the same material part number on a job but then you want to group by part and them sum the qty.
Record | Group | Value
1 | A | 100
2 | A | 50
3 | A | 95
4 | B | 0
5 | B | 25
6 | B | 30
Group | Row Value Total
A | 245
B | 55