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(Fiel...

问题:

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?


回答1:

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.


回答2:

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

  • 发表于 2019-03-12 14:50
  • 阅读 ( 248 )
  • 分类:sof

条评论

请先 登录 后评论
不写代码的码农
小编

篇文章

作家榜 »

  1. 小编 文章
返回顶部
部分文章转自于网络,若有侵权请联系我们删除