Sum function in Report Builder 3.0

Question

I am creating a report that will sum up all subtotal per MaterialNo/Color/Quality/Size.

Here's my sample report:

enter image description here

Computation will be GrandTotal = sum(Subtotal)

I am using =Sum(Fields!TOTAL_CTN.Value) but the result is 12.

Is there a way to compute using like =sum(subtotal_TextBoxName)?

It's not clear how your totals are being calculated. For example if you're just using a straight Sum of TOTAL_CTN for your totals, I would expect a Grand Total of 12 based on the above data. Based on how you describe your grouping, I would also expect a Sub Total for each of rows in your report, but for CCC there is only one Sub Total, despite four different sizes. Can you please clarify: 1. Where the Sub Total grouping is applied? 2. How this affects the Grand Total? — Ian Preston
Thank you for your reply Ian, for material CCC, we will put all four sizes(S,M,L,XL) in one box so the subtotal will be 1 — devkiat
How is that Sub Total calculated, though? Is is the Max of the individual TotalCarton rows? The average? Or will each MaterialNo group have the same TotalCarton value for each row, i.e. there isn't actually any aggregation applied? For CCC you have four rows, each with TotalCarton = 1, so I would expect this to Sum to 4? — Ian Preston
SubTotal are calculated per MaterialNo/ColorCode/Quality/Size. All AAA will be place to 3 cartons, lets say its qty is 300(100per carton), for CCC lets say qty of 25 per size and will be put in one box. — devkiat

Ian Preston Ian Preston · Accepted Answer · 2013-06-06T10:45:33

I'm still not 100% sure of your underlying data and business logic, but here's one approach that might help.

I'm working off the following sample data:

enter image description here

Based on what your description above, it sounds like you have something similar to:

enter image description here

Which gives results:

enter image description here

It seems that you don't actually want to Sum TOTAL_CTN, rather this should be the same for every MaterialNo with the Sum only applied in the Grand Total.

As such you can change the Sub Total aggregation to Max and, since you're using Report Builder 3.0, you can then take the Sum of the group level aggregation values. The report will look like this:

enter image description here

Where the Grand Total expression is:

=Sum(Max(Fields!TOTAL_CTN.Value, "MaterialNoMax"))

In this expression MaterialNoMax refers to the name of the Row Group (based on MaterialNo in my example).

This gives your desired results:

enter image description here

The key here is thinking about what aggregation gets applied at each level and how these roll up to the end total.

Sum function in Report Builder 3.0

1 Answers