Summing a column of expressions in SSRS

Question

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 
total:        6                     12                0.5

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

but that gives the 0.5.

Anyone have any ideas?

---

My Solution:

I added another column to the right of the ratio column and set its visibility to "not visible". In the detail row, I added this expression:

=runningValue( sum(Fields!one.Value) / sum(Fields!two.Value), Sum, "table1_grp")

(table1_grp is in internal group).

I copied the value from the runningValue textbox in the ratio column in the total row.

The expression is:

=ReportItems!Textbox55.Value  

So, the running sum is invisible in the table, but I use the final value in the total row.

---

UPDATE:

My colleague came up with an elegant solution.

In ratio total box, use the following expression:

=sum( sum(Fields!one.Value, "table1_grp") / sum(Fields!two.Value, "table1_grp") )  

adding the scope to the inner sums does the trick.

Brokerage

Answer 1

Try Sum((Fields!one.Value / Fields!two.Value)).

Turbellarian

I just tried that and it didn't work - it gave me the sum of the ratios for all of the underlying data (without summing the fields first). This resulted in an answer of 2.0. – Brokerage

Answer 2

You could add a calculated field in your data set that creates the ratio.

I - Insert the data field instead of the expression directly in the field.

Now you have it total by right-clicking and selecting add a total.

II - Right-click on your data set, select Add calculated field, name it ratio, add your expression here.

Then ratio will be a selection you can add to your table as an actual field instead of an expression. You can then total it.

Conventual