CODEWITHSUNDEEP
sqlreporting-services
Scott S.
Scott S.

Reputation: 173

SSRS - Converting a value into a number causing issues when working with a NULL value

I have an expression in SSRS that is designed to change the format of a field depending on the value of another field:

=IIF(ISNOTHING(Fields!RD.Value), "", IIF(InStr(Fields!ReportHeader_f2.Value,"Average") > 0,Format(Fields!RD.Value,"N") * 1.000,Format(Fields!RD.Value,"#,##;(#,##)") * 1.000))

I need the values to turn up as numbers in the excel spread sheet so I multiply them by 1.000 (I have also tried Cdbl).

When the expression encounters a NULL value for RD, the #Error cell pops up. I've tried using the ISNOTHING() tag to account for it and I've tried turning the NULL values into zeroes and then accounting for that.

What could be wrong with my expression?

Upvotes: 3

Views: 2329

Answers (3)

Chris Latta
Chris Latta

Reputation: 20560

There are a couple of things happening here causing problems:

Firstly, IIF is a function not a language construct. This means there is no logic short-circuit depending on the value of the first parameter comparison, it evaluates all parameters before calling the function. So if either the True or False parameter would have an error, the whole thing will blow up regardless of the value of the conditional.

To prevent this you generally need to arrange two IIF function calls in a way that no error will be encountered. Now to the source of your problem: CDbl will throw an error if it encounters non-numeric input. The answer by @Steve-o169 prevents this by using two IIF functions in a way so that no invalid conversion is done, preventing the error. Another way would be to use the Val function rather than CDbl because the Val function does numeric evaluations without throwing an error - it just grabs what it can and calls that a number.

However, you don't actually need to worry about this because presentation should be separate from data: you want the Value to be numeric, otherwise if you use the Format function then exporting to Excel will have text values rather than numeric. Rather than put the formating in the Value expression you should put it in the Format property where it belongs, so the values will export as numbers and the format will also be correct in Excel.

So we don't even have to worry about the value of the field, just set the expression of the Value property to the field value so it will export as a number.

Now to set the Format correctly, in the Format property of your cell use the following expression:

=IIF(InStr(Fields!ReportHeader_f2.Value, "Average") > 0, "N", "#,##;(#,##)")

Upvotes: 0

Steve-o169
Steve-o169

Reputation: 2146

To me it looks like your IsNothing isn't catching NULL values and the error arises when SSRS tries to format the NULL value but doesn't have a way to handle that. Personally, my preferred solution to this would be to make sure any NULL values are converted to zeroes in the query/proc prior to coming into the report. Just slap an ISNULL(RD, 0) and try to handle the zero in the report. I'd also try to CDbl basically everything coming into this expression just to be sure nothing is coming in with a wrong datatype. Probably a better option than trying to multiply each by 1.000. Additionally, the error could come from trying to multiply the formatted field by 1.000. SSRS works in mysterious ways. I would think this should work.

=IIF(CDbl(Fields!RD.Value) = 0.0, 
     "", 
     IIF(InStr(Fields!ReportHeader_f2.Value, "Average") > 0, 
         Format(CDbl(Fields!RD.Value), "N"),
         Format(CDbl(Fields!RD.Value), "#,##;(#,##)")))

You might also be able to replace the CDbl conversions with Format(Fields!RD.Value * 1.000, "N"), etc... There's a few places things could've gone wrong, but this approach seems the most likely to work, IMO.

Another potential solution I explored with my testing was to create a calculated field based on the original field. For example, call it RD2 and set the value equal to =IIF(Fields!RD.Value Is Nothing OR Fields!RD.Value = 0, 0, Fields!RD.Value). Then you can use the expression above with the RD2 calculated field. Seemed to catch the zero values in my testing.

Upvotes: 3

Eray Balkanli
Eray Balkanli

Reputation: 7990

1- You can use ISNOTHING function:

=IIF(IsNothing(Fields!RD.Value),0,Fields!RD.Value)

2- Develop a custom query. Open Report menu->Report Properties->Code, and use:

Function IfNullUseZero(myvalue as object)
     IF IsNull(myvalue) then
            Return 0
     Elseif myvalue =" " then
            Return 0
End If

then, on the report, do =Code.IfNullUseZero(Fields!RD.Value).

Ref: https://social.msdn.microsoft.com/Forums/sqlserver/en-US/bd6dfd75-feb6-4093-8909-6c4054b64c96/convert-null-to-0-in-ssrs?forum=sqlreportingservices

Upvotes: 0

Related Questions