CODEWITHSUNDEEP
hana
Prathamesh H
Prathamesh H

Reputation: 150

How can I show hierarchy wise data in SAP HANA sp12 Calculation View

I've a table say A_PRODUCT_MASTER with some columns say Level1, Level2, Level3. Such that Level 1 being the parent node. and Hierarchy is as follows: Level1 -> Level2 -> Level3. I want to show Level-wise Sale_qty. (sale_qty can be acquired from table say A_ITEM_SALES)

Upvotes: 0

Views: 1623

Answers (3)

Lars Br.
Lars Br.

Reputation: 10388

If the question is mainly about computing queries on hierarchical structures (e.g. sum up revenue per org-unit), then using the Hierarchy Functions in SAP HANA 2 SP02 and newer would be the way to go.

On the UI side, you would need to use a frontend like SAP Business Analytics for Office that understands and represents the hierarchical nature of the data.

Upvotes: 0

Eralper
Eralper

Reputation: 6612

@Prathamesh H, I have prepared a second solution for you using SQL RollUp clause. You can also check for other aggregation functions like Group By Grouping Sets and Cube besides RollUp functionality on SQL

select distinct
    levels.Level1, levels.Level2, levels.Level3,
    SUM(sums.Qty) as Totals
from (

    select
        distinct level1, level2, level3
    from product_master

    union all

    select
        distinct level1, level2, NULL as level3
    from product_master

    union all

    select
        distinct level1, NULL as level2, NULL as level3
    from product_master

) as levels
left join (
    select
    Level1, Level2, Level3, Qty
    from PRODUCT_MASTER
) as sums
    on  levels.level1 = sums.level1 and 
        levels.level2 = sums.level2 and
        levels.level3 = sums.level3
group by rollup ( levels.Level1, levels.Level2, levels.Level3 )
having SUM(sums.Qty) > 0
order by levels.Level1, levels.Level2, levels.Level3

Output will be as follows

enter image description here

Upvotes: 1

Eralper
Eralper

Reputation: 6612

using SUM() aggregation function using Partition By clause can be an option.

Please check following SQLScript code with sample data

/*
create column table PRODUCT_MASTER (
Product varchar(30),
Level1 varchar(30), 
Level2 varchar(30), 
Level3 varchar(30),
Qty int
);

insert into PRODUCT_MASTER values ('SQLScript eBook', 'Book', 'Programming', 'Database', 10);
insert into PRODUCT_MASTER values ('Lambda', 'Book', 'Programming', 'Cloud', 5);
insert into PRODUCT_MASTER values ('NoSQL with DynamoDB', 'Book', 'Programming', 'Database', 3);
insert into PRODUCT_MASTER values ('SAP SD', 'Book', 'Software', 'ERP', 8);
insert into PRODUCT_MASTER values ('Mangala', 'Game', 'BoardGame', 'Traditional', 2);
*/

select
*,
Sum(Qty) Over (Partition by Level1) Lvl_1,
Sum(Qty) Over (Partition by Level1, Level2) Lvl_2,
Sum(Qty) Over (Partition by Level1, Level2, Level3) Lvl_3
from PRODUCT_MASTER

Output of this query will be as follows

enter image description here

I hope it helps, I'm not sure if you want to achieve this result

Upvotes: 1

Related Questions