SQLTeam.com | Weblogs | Forums

SSRS Funtion



I'm trying to write a function for a cell that references another cell. The cell that is already programmed has a function that looks like this:

=IIF(Fields!CL_DISADV_FL.Value = "N", "0.00", Fields!CST_AMOUNT.Value)

Is it possible to total this field with a function?

Thanks Masters.



You can do one of two things:

Wrap the expression as you have in the cell that has the data that you want to add up in the SUM function like this:

SUM( IIF(Fields!CL_DISADV_FL.Value = "N", "0.00", Fields!CST_AMOUNT.Value) )

Or, Find the report item name of the cell in which you have the value and sum it. Assuming it is Texbox1,



I tried both of these options and get two different errors. For the first option you gave me. I get an error that states:

[rsAggregateOfNonNumericData] The Value expression for the textrun ‘CL_WOM_OWN_FL2.Paragraphs[0].TextRuns[0]’ uses a numeric aggregate function on data that is not numeric. Numeric aggregate functions (Sum, Avg, StDev, Var, StDevP, and VarP) can only aggregate numeric data.

And the second option gives me this error:

[rsRuntimeErrorInExpression] The Value expression for the textrun ‘Textbox40.Paragraphs[0].TextRuns[0]’ contains an error: Input string was not in a correct format.



The first error is likely because you need to convert to CDbl before aggregating.

SUM( CDbl( IIF(Fields!CL_DISADV_FL.Value = "N", "0.00", Fields!CST_AMOUNT.Value) ) )

Not sure what the second error is. Make sure that in place of TextBox1, you have the correct report item.


Works great!

Thanks for all your help, Sir.