Evaluation of iif statements

Just bumped into a really weird issue I wasn't expecting to see, and
I'm wondering if anyone could shed some light on it. On my report, we
have a bunch of metrics being displayed over the various dimensions of
the hierarchy. Depending on the level of the entity the report is
being run for, there are some formatting changes (0 or 2 decimal
points on the percentage). Also, in the calc for each of these metrics
- we're using a cube - we have a condition that if the number of cases
aggregated in the entity is less than 10, the result should
automatically be "na" and otherwise it is quotient of two other
numbers. So, for example, in the Overall CEI calc's cell I have an
expression that looks like this:
=iif(Fields!Case_Count.Value<10,"NA**",
iif(IsNumeric(Fields!Overall_CEI.Value),
FormatPercent(Fields!Overall_CEI.Value,
iif(Parameters!Level.Value=1,0,2)),
"--"))
Now, remember that the na is actually being created at the database
layer by the cube, so the cell has a possibility of seeing either a
number or a string passed as a value from the dataset (the "NA**" is
just a more report-specific display). However, what's happening is
that if I get an entity with 8 records aggregated in it, I'm getting
the #Error display in my cell on the report. I went back and checked
out what I was doing with my iif statements and realized that it only
blows up when I have a math function like FormatPercent or Round in it
and I pass in a string as the value, REGARDLESS of where the math
function lives. I was under the assumption that as soon as an iif
evaluation returns True, it would stop evaluating everything else and
do what it's supposed to do. Is there any way to write more defensive
code here? Does anyone know why this works this way?Greetings,
I would guess that your iif statements might be out of sequence. What I
would try for testing this is to write a new query in the Report DataSource
window (leave the original query intact - just add a new datasource - new
query to the list of queries for that report) which will bring up only the
records where you get the error message -- except you will only have one iif
statement in this query. First - write the query to bring up these records.
If that query runs OK, then modify the query by adding an iif statement. If
the query still runs OK then you don't have the correct number of iif
statements in the original query, and that is your problem.
"Adam Schmitt" wrote:
> Just bumped into a really weird issue I wasn't expecting to see, and
> I'm wondering if anyone could shed some light on it. On my report, we
> have a bunch of metrics being displayed over the various dimensions of
> the hierarchy. Depending on the level of the entity the report is
> being run for, there are some formatting changes (0 or 2 decimal
> points on the percentage). Also, in the calc for each of these metrics
> - we're using a cube - we have a condition that if the number of cases
> aggregated in the entity is less than 10, the result should
> automatically be "na" and otherwise it is quotient of two other
> numbers. So, for example, in the Overall CEI calc's cell I have an
> expression that looks like this:
> =iif(Fields!Case_Count.Value<10,"NA**",
> iif(IsNumeric(Fields!Overall_CEI.Value),
> FormatPercent(Fields!Overall_CEI.Value,
> iif(Parameters!Level.Value=1,0,2)),
> "--"))
> Now, remember that the na is actually being created at the database
> layer by the cube, so the cell has a possibility of seeing either a
> number or a string passed as a value from the dataset (the "NA**" is
> just a more report-specific display). However, what's happening is
> that if I get an entity with 8 records aggregated in it, I'm getting
> the #Error display in my cell on the report. I went back and checked
> out what I was doing with my iif statements and realized that it only
> blows up when I have a math function like FormatPercent or Round in it
> and I pass in a string as the value, REGARDLESS of where the math
> function lives. I was under the assumption that as soon as an iif
> evaluation returns True, it would stop evaluating everything else and
> do what it's supposed to do. Is there any way to write more defensive
> code here? Does anyone know why this works this way?
>