Menu
I'm trying to produce a conditional sum in SQL Server Report Builder 3.0.
My expression looks like this:
=Sum(Iif(Fields!ProjectTypeID.Value=2,Fields!kWp.Value,0)) I'd hoped that this expression would produce a sum of the kWp of all projects of type 2.
Unfortunately, it is not to be. And I can't seem to work out why. It just returns a 0 result, even though I know that there are non-zero values in the kWp column, and the column does not contain nulls.
A colleague did manage to get a positive result by replacing the
Fields!kWp.Value with
1 * Fields!kWp.Value But we have no idea why this works, and therefore, can't really trust the answer.
How can I get this conditional sum to behave itself?
267
To add totals for a row group In the tablix data region row group area, right-click a cell in the row group area for which you want totals, point to Add Total, and then click Before or After.
Excels SUMIF in SQLThe the group by and over clauses specify the rows. The column is explicitly used in the <condition> that is put into the case expression. The case expression accepts different values in the when and then branches. This allows you to do the same thing as the third argument of the sumif function.
You can either likely moving the calculation into the SQL statement to allow yourself to use the normal Sum function in SSRS. You could go further down the chain and create a custom code function to add the ReportItem to a running total every detail line.
SSRS will automatically apply a Sum expression.
The data type of the column 'kWp' is Decimal so you need to either convert the default value to 0.00 or cast the column to double
SUM(iif(Fields!ProjectTypeID.Value = 2,cdbl(Fields!kWp.Value),0.00)) To get the sum of the kWp of all projects of type 2, the expression is as follows,
=IIf(Fields!ProjectTypeID.Value=2,sum(Fields!kWp.Value),0) I hope this will help u.
23
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
