This is a migrated thread and some comments may be shown as answers.

IIf not working properly?

7 Answers 452 Views
Report Designer (standalone)
This is a migrated thread and some comments may be shown as answers.
klaudyuxxx
Top achievements
Rank 1
klaudyuxxx asked on 22 Mar 2013, 01:45 PM
I need to exec a stored procedure. Some of the parameters are strings (but can also be null) and need to have this format: < 'IntVal1,IntVal2,IntVal3' >.
IntVals are basically city IDs.
I managed to format them with Join function:  Join(",",Parameters.Cities.Value).
The problem I'm facing is when the parameter City is null (I sometimes need it to be null). The Join function outputs an error in that case. To avoid calling the Join function when the param City is null I tried the following:
@queryParamCity   =   IIf  (Parameters.Cities.Value,  Join(",",Parameters.Cities.Value),  null)
or
@queryParamCity   =   IIf  (Parameters.Cities.Value IS NOT null,  Join(",",Parameters.Cities.Value),  null)
or 
@queryParamCity   =   IIf(Parameters.Cities.Value is null,null,Join(",",Parameters.Cities.Value))
.....but the Join function is still being called even if the City parameter is null, so it generates an error and the execution is stopped.

So, no matter what is the value of the expression, the other two member of the IIf function are called.

7 Answers, 1 is accepted

Sort by
0
klaudyuxxx
Top achievements
Rank 1
answered on 22 Mar 2013, 02:04 PM
The reporting engine first evaluates the function parameters and then passes the result values to the function. This means that you need to make sure the parameters of the function are valid statements prior to executing the function.

Found my answer...But not a solution
0
klaudyuxxx
Top achievements
Rank 1
answered on 22 Mar 2013, 02:41 PM
and the solution
= IIf(Parameters.Cities.Value is null,null,Join(",", IsNull(Parameters.Cities.Value, Parameters.fake.Value)))

Where the 'fake' param is a string with Value < ="" >
0
erwin
Top achievements
Rank 1
Veteran
Iron
answered on 02 Apr 2013, 02:19 PM
Another option would be to use COALESCE in the SQL Query.

Update: sorry didn't notice at first, you are using Parameters, so COALESCE is not an option.
0
Gaurav
Top achievements
Rank 1
answered on 25 Oct 2013, 06:53 AM
What will happen if the logic is like : -
IIF(A=0,0,B/A)
As stated
The reporting engine first evaluates the function parameters and then passes the result values to the function. This means that you need to make sure the parameters of the function are valid statements prior to executing the function
Now as per my understanding by the statement :-
Function evaluate B/A first ?  Am i wrong ?  Than how can i handle Device by zero conditions ?
0
Alexandru
Top achievements
Rank 1
answered on 25 Oct 2013, 07:00 AM
null
0
klaudyuxxx
Top achievements
Rank 1
answered on 25 Oct 2013, 07:07 AM
try something like this
=IIf (A=0, 0, B / IIf(A=0,1,A))
0
Gaurav
Top achievements
Rank 1
answered on 11 Nov 2013, 04:45 AM
 @Alexandru

Null ??
Tags
Report Designer (standalone)
Asked by
klaudyuxxx
Top achievements
Rank 1
Answers by
klaudyuxxx
Top achievements
Rank 1
erwin
Top achievements
Rank 1
Veteran
Iron
Gaurav
Top achievements
Rank 1
Alexandru
Top achievements
Rank 1
Share this question
or