How do I deterimine if a double is not infinity, -infinity or Nan in SSRS?

If got a dataset returned from SSAS where some records may be infinity or -infinity (calculated in SSAS not in the report).

I want to calculate the average of this column but ignore those records that are positive or negative infinity.

My thought is to cr开发者_如何学Goeate a calculated field that would logically do this:

= IIF(IsInfinity(Fields!ASP.Value) or IsNegativeInfinity(Fields!ASP.Value), 0 Fields!ASP.Value)

What I can't figure out is how to do the IsInfinity or IsNegativeInfinity.

Or conversely is there a way to calculate Average for a column ignoring those records?

Just stumbled across this problem and found a simple solution for determining whether a numeric field is infinity.

=iif((Fields!Amount.Value+1).Equals(Fields!Amount.Value), false,true)

I'm assuming you are using the business intelligence studio rather than the report builder tool.

Maybe you are trying a formula because you can't change the SSAS MDX query, but if you could then the infinity would likely be caused by a divide by zero. The NaN is likely caused by trying to do Maths with NULL values.

Ideally change the cube itself so that the measure is safe from divide by zero (e.g. IIF [measure] = 0,don't do the division just return "", otherwise do it) . Second option would be create a calculated measure in the MDX query that does something similar.

As for a formula, there are no IsInfinity functions so you would have to look at the value of the field and see if its 1.#IND or 1.#INF or NaN.