CODEWITHSUNDEEP
sql-serverreporting-servicesreportbuilder
Reyn
Reyn

Reputation: 271

SSRS Total of IIF with SUM

I have two column groups, they have their individual row totals, calculated by

Sum(Fields!ColumnGroup1.Value)

and

Sum(Fields!ColumnGroup2.Value)

I then have a column that shows the difference between the two sums, but only if their total difference is bigger than 0

=IIF(
     Sum(Fields!ColumnGroup1.Value) - Sum(Fields!ColumnGroup2.Value) > 0.00,
     Sum(Fields!ColumnGroup1.Value) - Sum(Fields!ColumnGroup2.Value), 
     0.00)

Now the row contains my two column groups, their totals and their differences if it is bigger than 0

How do I calculate the sum of all the rows for the expression column ?

For some reasons the following gives me incorrect values:

=SUM (
     IIF(
         Sum(Fields!ColumnGroup1.Value) - Sum(Fields!ColumnGroup2.Value) > 0.00,
         Sum(Fields!ColumnGroup1.Value) - Sum(Fields!ColumnGroup2.Value), 
     0.00)
     )

Is that suppose to work ?

Thanks!

Upvotes: 1

Views: 20676

Answers (3)

Reyn
Reyn

Reputation: 271

I solved my problem. What I failed to say was that I have row groups as well. I needed to specify the child row group on the SUMs. (I would have thought it should be the parent row group) 

=SUM (
   IIF(
     Sum(Fields!ColumnGroup1.Value,"Child") - Sum(Fields!ColumnGroup2.Value,"Child") > 0.00,
     Sum(Fields!ColumnGroup1.Value,"Child") - Sum(Fields!ColumnGroup2.Value,"Child"), 
   CDec(0))
 )

If you need to display a blank value for a subtotal of 0, use 'Nothing', if you need to display a value, then use 'CDec(0)'

Thanks

Upvotes: 3

LeSteelBox
LeSteelBox

Reputation: 435

Did you add a "group by" to your query? I believe you need to do this to actually aggregate a sum value.

If it's not that then try moving the fields (that contain your expression) to a static row (i.e. not contained in the column group).

Also, why don't you try losing the decimal point? A zero is a zero no matter how many zero decimal places it has.

Upvotes: 0

Bobby
Bobby

Reputation: 2928

I think you need extra brackets around the sums you are checking against 0

=SUM (
     IIF((Sum(Fields!ColumnGroup1.Value) - Sum(Fields!ColumnGroup2.Value)) > 0.00,
         Sum(Fields!ColumnGroup1.Value) - Sum(Fields!ColumnGroup2.Value), 
     0.00)
     )

Upvotes: 0

Related Questions