business objects - SAP BO 4.2 SP07 inList clause in webi

Question

Can i use another column in InList clause?

Example, i have created a variable and below is the formula.

IF [query1.column1] inList ([query2.column2]) then SUM([query1.amountColumn])
Else 0

OR is it possible to put variable after inList in formula?

If not possible -- is there any other alternative to this?

question from:https://stackoverflow.com/questions/66058341/sap-bo-4-2-sp07-inlist-clause-in-webi

Answer 1

I see two possible approaches. I will to use the eFashion universe for both solutions.

Solution #1

Here are my 2 queries to begin...

Run your queries. Click on the columns you want to compare, [query1].[column1] and [query2].[column2] in your case; [Query 1].[Month] and [Query 2].[Month] for me. Right-click and merge them. They must be dimensions and of the same data type.

Now create a variable based on [Query 2].[Month Name] which you can filter on to eliminate the results from Query 1 that do not match up to anything in Query 2.

[UV Month Name]=[Query 2].[Month Name]

The key here is you need to change the Qualification to "Detail" and set the Associated Dimension to what we just merged by clicking three dots to the right. Choose [Month Name] not from either query, but the merged dimension.

Now build out your table with whatever object you want from Query 1 and add in the variable we just created.

Now add a filter on that variable to only show row where it is not null.

And you are done.

Pros

• Works when limiting query (query2) has a relatively large number of values (compare to Cons for Solution #2).

Cons

• More complicated to set up
• May run into universe or performance issues related to query being filtered (query1).

Solution #2

Building upon Solution #1, I duplicated Query 1 and renamed it Query 3. Now you can choose "Results from another query" to get the [query1].[colunmn1] InList ([query2].[column2]) logic you want.

If you take this approach then you don't need to do the merge, variable, and filter. The results of the query are filter before being returned by the report.

Pros

• Simple

Cons

• The number of values coming from your second query must be relatively small. It varies by database or maybe even your universe. I have found if it is over 1,000 values I get an error when I run the query that it is "too complex".