[3.1] Check if the dimension value exists in another query

building reports webintelligence
1

I use the following formula to create mapping in my report


=If([MyColumn] InList ("1";"2";"3")) Then "ItemA"
ElseIf([MyColumn] InList ("4";"5";"6")) Then "ItemB"
ElseIf([MyColumn] InList ("7";"8";"9";"10")) Then "ItemC"
Else "ItemD"

It works fine until someday the items In List exceeds the limitation. The WEBI is on hold for long time and finally crash due to session timeout.


=If([MyColumn] InList ("1";"2";"3";.........;"999999")) Then "ItemA"
ElseIf([MyColumn] InList ("4";"5";"6")) Then "ItemB"
ElseIf([MyColumn] InList ("7";"8";"9";"10")) Then "ItemC"
Else "ItemD"

So I now consider to alternative solution.
I add new queries “ItemA”, “ItemB”, “ItemC” and “ItemD” in the report. Then, I need a way to check if [MyColumn] is in the List.
Is it possible to do such mapping? Thanks

summoner :hong_kong: (BOB member since 2008-12-18)

2

Hi,

take a look at

http://scn.sap.com/community/businessobjects-web-intelligence/blog/2014/12/08/using-merged-dimensions-to-perform-set-operations-union-intersection-and-minus,
It may give you some clues.
Regards,
Rogerio

rgoulart :brazil: (BOB member since 2011-08-21)

3

Why do it in the report?

I’d always resolve something like this at the database level where possible; if not, at the very least get it fixed within the universe.

4

Document not found :cry:

KFonMurphi :fr: (BOB member since 2007-10-16)

5

Dear Mark ,
sometimes (in my case almost never) you don´t have access to universe design or database, or the solution will be cost effective. Perhaps you need just to do it for one report and creating it at universe side is not cost effective.
So we have to develop other solutions.
Regards,
Rogerio

rgoulart :brazil: (BOB member since 2011-08-21)

6

Rogerio,

I understand that - I’ve worked with pre-canned universes in the past ( :wah: :cuss: :hb: ) and they’re not pleasant.

It just strikes me that it would be far easier to create a case statement in the universe, or a lookup table within the database.

7

Thanks. it works.
What I did is to create 3 queries ItemA, ItemB, and ItemC, which retrieve data with ItemCode and any column (say “ItemDescription”) in the table

Then I build detail object and linked up to merged dimension ItemCode.
After that, everything is straight forward


If (Not(IsNull([ItemDescription])) Then "Y" Else "N"

I hate to work with pre-canned universe :wah:

But not the case this time. :wink:
I build it in report level because I give my users privileges to control the mapping themselves :roll_eyes:

summoner :hong_kong: (BOB member since 2008-12-18)

8

The trailing , is not separated by a space, hence “the url does not exist” …
Is this better : How to Find Matches e No Matches in Dimensions fro... - SAP Community ?

RensH :belgium: (BOB member since 2007-06-18)