Multiple IF statements issue

building reports desktopintelligence
1

Hi everyone!

Looks like problems never come alone, I still didn’t solve that mysterious #ERROR issue that I encounter difficulties with multiple IF statements. I did search the forum, and came across this topic:

I did have a look at the suggested solution and it was exactly what I had done: here is the definition of the variable I created to display the result of my multiple statements:

= If (UserResponse (“SQL 1 with ekswtst” , “No du tiers”)=“10860”) Then “ATS”
Else If (UserResponse (“SQL 1 with ekswtst” , “No du tiers”)=“7994”) Then “C Set ID”
Else If (UserResponse (“SQL 1 with ekswtst” , “No du tiers”)=“11156”) Then “PRODIS”
Else If (UserResponse (“SQL 1 with ekswtst” , “No du tiers”)=“11267”) Then “FIRST”
Else If (UserResponse (“SQL 1 with ekswtst” , “No du tiers”)=“12587”) Then “SWISS VIDEO”
Else If (UserResponse (“SQL 1 with ekswtst” , “No du tiers”)=“12778”) Then “SECURAX”
Else If (UserResponse (“SQL 1 with ekswtst” , “No du tiers”)=“13277”) Then “PROSECURITY”
Else “#MAUVAIS NUMERO DE TIERS#”

I tested it on the two first values (10860 and 7994) and it displays the correct string (“ATS” and “C Set ID” respectively), but as soon as I try a further value the resulting string becomes “SQL1 with ekswtst”

Any help would be appreciated :smiley:

Manuel

PS: the report uses two DP’s, both free-hand SQL and I’m running BO Full Client 6.5.2 querying Oracle 8.1 DB

mvansluys :belgium: (BOB member since 2006-02-28)

2

Just some debugging suggestions try moving the 3rd IF up to the first line and see what happens. As an alternative, could you display the prompt results and use the Alerter function to display the Text message.

Michele Pinti (BOB member since 2002-06-17)

3

I would break this into components. I would have one variable that captures the user response

UserResponse ("SQL 1 with ekswtst" , "No du tiers")

and then reference that in your If statement. It can help with debugging. It also ensures that you are using the exact same logic throughout your If statement if you reference a variable instead of copying a part of an expression.

Dave Rathbun :us: (BOB member since 2002-06-06)

4

Thanks for the suggestions :yesnod:

I wanted to give them a try this morning but something magic had happened and when I reopened my report my IF statements were working just fine :blue:

Still I wanted to try to put the User Response in a variable to make the code easier to read if someone wants to modify the report later, so I created the variable =UserResponse (“SQL 1 with ekswtst” , “No du tiers”).

I was also wondering whether the “#” character from my error message could cause problems, so I suppressed it. All this led me to the following statement:

= If (=“10860”) Then “ATS”
Else If (=“7994”) Then “C Set ID”
Else If (=“11156”) Then “PRODIS”
Else If (=“11267”) Then “FIRST”
Else If (=“12587”) Then “SWISS VIDEO”
Else If (=“12778”) Then “SECURAX”
Else If (=“13277”) Then “PROSECURITY”
Else “MAUVAIS NUMERO DE TIERS”

And there…surprise…everything went wrong again, I tried 3 or 4 Vendor numbers and they all returned “#MAUVAIS NUMERO DE TIERS#”, note the “#” character, this was my old error message, not appearing anywhere in the new statement :crazy_face:

So, I played with this issue for 15 minutes or so, trying to invert some of the statements, changing the variable definition from measure to detail, etc… nothing helped :roll_eyes:

I then reverted to the original statements (the ones I posted yesterday) and they were still working fine. My issue is thus solved but I’m completely lost…

Just for my curiosity, does anyone have a plausible explanation?

Manuel

PS: I didn’t try the Alerter workaround because I actually have other IF statements in the same report that need to be used in other formulas and not displayed, I don’t think it’s possible with alerters but I’m completely new at this so I may be wrong :mrgreen:

mvansluys :belgium: (BOB member since 2006-02-28)