Summing a column of expressions in SSRS (total sum)

Question

I need this on Report Builder:

The detail row in my table contains a column with an expression:

=sum(Fields!one.Value) / sum(Fields!two.Value) I want to sum that column, but the results I get are not the sum of the ratios, but the ratio of the sums.

For example:

 sum(Fields!one.Value)  sum(Fields!two.Value)    ratio
          3                      6                0.5 
          3                      6                0.5 
           6                     12                0.5

The last row is the total.

I want the bottom right corner value to be the sum of the values above it (i.e. 1.0), not the ratio of the values to the left of it. I've tried calculating the sum as:

sum( sum(Fields!one.Value) / sum(Fields!two.Value) ). I need the answer to be 1 (0.5 + 0.5). But that gives the 0.5

Anyone have any ideas?

I copy and paste this same question: Summing a column of expressions in SSRS because I have the same case, and the ideas they have do not work

What is your expected output based on your table, I am abit confused about that. — Kin Siang
The sum total of the 'sum(Fields! One.Value)' is 6 : (3 + 3). The sum total of the 'sum (Fields! Two.Value)' is 12 : (6 + 6). I want to obtain the total sum of the ratio 1 : (0.5 + 0.5), but the result that it gives me is 0.5 (he does the calculation like this: 6/12 = 0.5) — Manuela Osorio
=SUM(Sum(Fields!one.Value,"rowgroupname") / Sum(Fields!one.Value,"rowgroupname")) is the correct way to do this as you other post you copies suggests. All you need to do is swap out the rowgroupname with the name of your row group (it might be "Details" if you dont have any grouping. Then based on this sample, it will work. — Alan Schofield

niktrs niktrs · Accepted Answer · 2021-06-03T07:05:56

You can use custom code to calculate the ratio total

Add the following custom code to your report

Public Dim ratio_total

Public Function CalculateRatio ( ratio As Double) As Double

ratio_total = ratio_total + ratio

Return ratio

End Function

The function takes the current ratio value, updates the total and returns the ratio value

Now on your detail set the ratio expression to somethink

=Code.CalculateRatio(Sum(Fields!one.Value) / Sum(Fields!two.Value))

On your ratio total set the expression to = Code.ratio_total

***** UPDATE *****

You can use an expression based on totals like in the answers of the post you mentioned.

=SUM(Sum(Fields!one.Value,"group1") / Sum(Fields!one.Value,"group1"))

You need to set the right scope for the expression to work

Summing a column of expressions in SSRS (total sum)

1 Answers