The end of my SSRS "NaN" misery! Avoid SSRS "NaN"....
I always had problems with that annoying “NaN” in SSRS, I used to run around it using IIF or REPLACE and ROUND, but finally I decided not to give up and find a neater solution for that particular report.
Reading more about the IIF function @ http://msdn.microsoft.com/en-us/library/27ydhh0d%28vs.71%29.aspx
At the remarks section, they say “The expressions in the argument list can include function calls. As part of preparing the argument list for the call to IIf, the Visual Basic compiler calls every function in every expression. This means that you cannot rely on a particular function not being called if the other argument is selected by Expression.”
Anyway to cut my story short, if you have a function like:
=IIF(Lookup(Fields!ADM_DR_ID.Value, Fields!ADM_DR_ID.Value, Fields!ID2010.Value, "LOS") = nothing, "0" , Lookup(Fields!ADM_DR_ID.Value, Fields!ADM_DR_ID.Value, Fields!ID2010.Value, "LOS")) It’s not going to work.
The right way is to avoid dividing by zero!
=IIF(Fields!ID2010.Value = 0, "0" , Lookup(Fields!ADM_DR_ID.Value, Fields!ADM_DR_ID.Value, Fields!ID2010.Value, "LOS"))
Thanks, I was having the same issue, also that post helped http://social.msdn.microsoft.com/Forums/en-US/sqlreportingservices/thread/fb0f9f16-e142-4a9c-8e9b-fcc84a7f6a0e/
ReplyDeleteI want to remove the NaN% from my report. This is what I have:=round((sum(Fields!Passed.Value))/(sum(Fields!Total.Value)) * 100,2) & "%"
ReplyDeleteHi, I have an issue with NaN too. here is my line of code, maybe you could hel me.
ReplyDelete=round((sum(Fields!Passed.Value))/(sum(Fields!Total.Value)) * 100,2) & "%"
use the IIF as indicating above and don't use the % sign, you could format the field to display it.
Delete