CODEWITHSUNDEEP
reporting-servicesreportbuilder3.0
devkiat
devkiat

Reputation: 139

Sum function in Report Builder 3.0

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)?

Upvotes: 1

Views: 7349

Answers (1)

Ian Preston
Ian Preston

Reputation: 39566

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.

Upvotes: 1

Related Questions