Conditional if formula WITH or statement

building reports
1

Hi, I’m using Webi BO 4.2, I need to write a variable to test the value of an object.

There are about 2000 conditions that need to be tested.

I want to write something like:

= If [Item Id] = 1757226 Then “A” ElseIf
[Item Id] = 1757268 Then “b” elseif
.
.
** till end of first 1000 conditions **

or
If [Item Id] = 17526 Then “c” ElseIf
[Item Id] = 175777 Then “r” elseif
.
.
** till end of second1000 conditions **
else “-”

I’m missing something and I’m not able to figure the above formula to handle the 2000 ifs in single variable, any idea how this can be done?

Thank you,
Ahmed

2

It may just be too long. Pretty sure there is a character limit to variables. Maybe try breaking it up into multiple variables then combine into a final variable that you use in your report.

3

Sounds like something I’d be doing at the database level rather than the report level, especially if it’s needed in more than one report.

Either that or put the IDs and outcomes in a spreadsheet, load them as a separate data provider and merge them with the original query.

2 Likes
4

if you really want to check 2000 conditions in that way,
you should urgently attend a data-modeling/reporting course
or you are bored and want to kill the time :laughing:

5

Or use a additional data set the can be merged

6

This is good solution, but when we did so, so lines disappeared for unknown reason.

7

We can do so, however we wanted the variable solution.

8

:slight_smile: the conditions are already listed in a spread sheet

9

Is this set of variables that can be merged latter? if yes, we did so but some lines disappeared for unknown reason. I believe I did the single variable method with OR statement few years back, but I can not remember what I did at that time, and I can not find that report as well.

10

Can’t you add this then to the universe as SQL definitions?

11

Upload the spreadsheet to BO, add this to your report as datasource
and merge the key-value with your report.
Pay particular attention to consistency of the key-values (e.g. additional blanks, upper/lower-case, number-formatting) !
If there are differences, create variables with the logic of cleaning the values and merge these variables instead of the original value.
Data only get lost in merging if there is no exact matching partner !

12

If you don’t want to have an external datasource in Excel or can’t add this to you database, you can use groups which are effectively CASE statements. However, you have to check for new values each time you refresh the report and it’s just for a single report which means it’s easy to be inconsistent between reports.

Given that IDs are not very descriptive, I’d concatenate the ID with a descriptive element, insert that in the report and group the concatenated variable.

13

Check your logic. You’re missing a case/if somewhere. Build in stages. Display each variable in your report, temporarily, to help troubleshoot.

As others have said, variables are not the best solution for this. Especially since you mention you’ve had to use the same logic before - reuse/recycle! If you have a large dataset, you’ll likely see a performance hit.

14

Hello All,

I used to do use code similar to the below

= If [Item Id] <= 1999 Then
(If [Item Id] = 1 then “A” elseif
… till end of first 2000 conditions
[Item Id] = 1999 then “ZXZ”
) ElseIf

[Item Id] >= 2000 And [Item Id] <= 4000 Then
(If [Item Id] = 2000 then “ABC” elseif
… till end of second 2000 conditions
[Item Id] = 4000 then “ZZZ”
)
Else “–”

However, in my new condition the [Item Id] is not numerical, so I’m confused how I can modify the above code to handle the huge number of conditions in single variable using the IF else statement.

Thank you for your replies, and hope we can find a solution for this request,
Ahmed Moursi

15

Hi MarkP,
Could you guide me how i can use seperate sheet i business intelligence 4.3.

BR
Sultan