Infinity / Nan / #Error in SSRS 2005 Reports

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2005 » Business Intelligence » Infinity / Nan / #Error in SSRS 2005 Reports

Infinity / Nan / #Error in SSRS 2005 Reports

Rate Topic

Display Mode

Topic Options

Author

Message

douglascfast

Posted Friday, April 24, 2009 4:53 AM

SSC Rookie

Group: General Forum Members
Last Login: Friday, October 26, 2012 4:29 AM
Points: 40, Visits: 173

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

Lowell

Posted Friday, April 24, 2009 6:07 AM

SSChampion

Group: General Forum Members
Last Login: Today @ 6:05 AM
Points: 11,792, Visits: 28,081

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

douglascfast

Posted Friday, April 24, 2009 6:41 AM

SSC Rookie

Group: General Forum Members
Last Login: Friday, October 26, 2012 4:29 AM
Points: 40, Visits: 173

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.

Sad

Doug

Post #703902

stevefromOZ

Posted Friday, April 24, 2009 9:06 AM

SSCommitted

Group: Moderators
Last Login: Yesterday @ 11:15 PM
Points: 1,764, Visits: 3,202

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