@DPValues and @DPEnd

A nice little piece of nothing this one.
I noticed on the forums, someone asking about these functions. So, I did some digging.
I built a first query that list stores :
The query became something like :
SELECT
 
Agg_yr_qt_mt_mn_wk_rg_cy_sn_sr_qt_ma.Store_name
FROM
  Agg_yr_qt_mt_mn_wk_rg_cy_sn_sr_qt_ma
Then, I based a query on it:

the query became :
SELECT
 
Article_lookup.Family_name
FROM
  Article_lookup,
  Outlet_Lookup,
  Shop_facts
WHERE
  ( Outlet_Lookup.Shop_id=Shop_facts.Shop_id  )
  AND  ( Article_lookup.Article_id=Shop_facts.Article_id  )
  AND  (
      @dpvalues('
Outlet_Lookup.Shop_name',9,1)@dpend
  )
at the next iteration, it became:
@dpvalues('
Outlet_Lookup.Shop_name',9,7)@dpend
and when I tried filtering for a number, it became:
@dpvalues('
sum(Agg_yr_qt_rn_st_ln_ca_sr.Sales_revenue)',536870916,12)@dpend

This tells me, it’s not a user-function. It’s an internal function. The user is expected to use a subquery instead.
Or, as Andreas stated on the forums :  query on query, which allows you to filter based on a different datasource.

Binabik

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.