CODEWITHSUNDEEP
business-objectswebi
Parixitsinh Chauhan
Parixitsinh Chauhan

Reputation: 1

SAP BO 4.2 SP07 inList clause in webi

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?

Upvotes: 0

Views: 1953

Answers (1)

Isaac
Isaac

Reputation: 3363

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

Solution #1

Here are my 2 queries to begin...

enter image description here

enter image description here

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.

enter image description here

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.

enter image description here

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

enter image description here

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

enter image description here

And you are done.

enter image description here

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].[column1] InList ([query2].[column2]) logic you want.

enter image description here

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".

Upvotes: 0

Related Questions