问题:
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