SubQuery's in Business Objects

The title may seem complex, but the issue is simple.

Lets say you wanted to see the Sales Revenue per Store, but only for Stores located in a State that had a Sales Revenue over 7.000.000 $.

In that case, you would have to produce a query that would return all of the States over 7.000.000 $, and based on that query, obtain all of the stores you needed. The Stores by themselves don’t have such a high Sales Revenue, so you can’t get this in one go.

Here is how :

This would return us the Stores and their Sales Revenue (all stores). So next, we need to perform a Filter on the State, setting a Sub-query as a source.

The result is as follows :

The stores listed only come from states with a Sales Revenue over 7000000$ — just as required.

Great stuff isn’t it ?

Beware : Universes have a parameter that limit the maximum inlist values. Furthermore, some databases have limits on the maximum inlist values.

The most common number is 999. The parameter on the universe that sets this is : MAX_INLIST_VALUES and is set, by default to 256.