Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Infinity / Nan / #Error in SSRS 2005 Reports Expand / Collapse
Author
Message
Posted Friday, April 24, 2009 4:53 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, June 25, 2014 3:48 AM
Points: 44, Visits: 194
Howdy all,

So I am new to the SSRS. Bought a book and started to go.

So far its been a walk in the park (with a lot of muggers).

Now I am stuck on this one problem. When I compute percents for my report, sometimes there are no sales for an area and I am getting NAN or #error or Infinity.

What I would like to do is show a 0 in that field instead.

The issue is that sometimes I am doing this on 4 fields (A_Rev_TY/B_Rev_TY) / (A_Rev_LY/B_Rev_LY)-1.

I don't really want to put 4 nested IIF's into the box, for my 56 boxs where I would need them.

Is there no way to say Don't show these errors or something I am missing?

Any Ideas?

Doug
Post #703841
Posted Friday, April 24, 2009 6:07 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 8:24 PM
Points: 12,876, Visits: 31,781
in my case, i do all my calculations in the SQL, instead of in the report....
so my SQL would have one more field, that has this for the calculation:

(A_Rev_TY/B_Rev_TY) / (A_Rev_LY/B_Rev_LY)-1.
CASE
WHEN ISNULL(B_Rev_TY,0.0) = 0
THEN 0 --no need to calc the second part of the equation...zero divided by anything is still zero
ELSE
--another, inner case:
CASE
WHEN ISNULL(B_Rev_LY,0.0) = 0
THEN 0
ELSE (A_Rev_TY/B_Rev_TY) / (A_Rev_LY/B_Rev_LY)
END
END



Lowell

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #703869
Posted Friday, April 24, 2009 6:41 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, June 25, 2014 3:48 AM
Points: 44, Visits: 194
Thanks for that. That is a good idea and I will keep it in mind.

Not to go into too much detail, but my report gives 4 cuts of the same data. So its sorted by Month / Offices / Department&Owner / Product / Corporate Customer.

There is just no way to do all the calcs in the SQL (As you can't sum or average the percents)

So can I take it then there is no easy OnError Display 0.



Doug
Post #703902
Posted Friday, April 24, 2009 9:06 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: Moderators
Last Login: Monday, July 14, 2014 10:41 PM
Points: 1,814, Visits: 3,453
Am not saying I *like* this (due to having to 'hook' the IsNaN function to the equation, doesn't appear to allow it to stand alone) but it works....


=Iif((Fields!field1.Value / Fields!field2.Value).IsNaN(Fields!field1.Value / Fields!field2.Value), 0, Fields!field1.Value / Fields!field2.Value)

so psuedo-code... if A / B is NaN then 0 else A / B


HTH,





Steve.
Post #704047
Posted Wednesday, September 9, 2009 1:17 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, September 9, 2009 1:17 AM
Points: 1, Visits: 0
This works ! .. Thanks :)
Post #784744
Posted Wednesday, October 28, 2009 2:10 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, July 19, 2011 2:16 PM
Points: 114, Visits: 193
Very Helpful..... Thank you....
Post #810297
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse