How to build SAP BO universe objects for complex query

[nola]

Hello,

see the (simplified) sql query below:

with t1 as (

select create_month, cust_id

, max(case when age <= 50 then 1 else 2 end) as age_group

, sum(amount) cust_amt

from bla.revenue

where create_date between '2022-01-01' and '2022-07-31'

group by create_month, cust_id

)

select create_month, age_group, count(cust_id) cust_cnt, sum(cust_amt) sum_amt

from t1

group by create_month, age_group;

Range of create_date is given by user prompt in webi.

Max function on age column is because customer age is stored in each database record with create_date.
Each customer could have several entries a day. The classification into the age group should be made on the basis of the maximum age in the month.

I don't want to have the results of the inner query in the webi, because it can be millions of records.

The "work" should be done in database.

Is it possible to build universe objects to achieve this behavior.

Thank you,Norbert

[amitrathi239]

I believe you can.either you can derived table with sql in universe or if it’s one time request then you can use free hand sql provider in webi report as well