In this blog post, I am going to talk about a new way of creating CDS Hierarchies. Earlier before 1809 (On-Premise release) or 1811(Cloud Release), there was a different way to create CDS Hierarchies.
Also, I will demonstrate a step-by-step tutorial to create a simple basic CDS Hierarchy. To make it really relevant for everyone I will start by creating TABLES, then to CDS Views, and then CDS Hierarchy.
Before jumping into Tutorial lets see the Introduction of this CDS Hierarchy.
CDS Hierarchy is a new way to define Hierarchy, it is written using Data Definition Language.
Syntax starts with DEFINE HIERARCHY
Basic Syntax rule of CDS Hierarchy follows
[@entity_annot1]
[@entity_annot2]
...
[@hierarchy_annot1]
[@hierarchy_annot2]
...
[DEFINE] HIERARCHY cds_entity
[parameter_list]
AS PARENT CHILD HIERARCHY(
SOURCE cds_view
CHILD TO PARENT ASSOCIATION _hierarchy_assoc
[DIRECTORY _directory_assoc FILTER BY cond_expr]
START WHERE cond_expr
SIBLINGS ORDER BY field1 [ASCENDING|DESCENDING][,
field2 [ASCENDING|DESCENDING], ...]
[DEPTH depth]
[NODETYPE node_type]
[MULTIPLE PARENTS {NOT ALLOWED}|LEAVES|ALLOWED]
[ORPHANS ERROR|IGNORE|ROOT]
[CYCLES ERROR|BREAKUP]
[GENERATE SPANTREE] )
{ element_list }
◈ Here First thing is to define CDS entity name for Hierarchy.
DEFINE HIERARCHY ztest_hierarchy
◈ Now if there are any input parameters required then mention below this in the parameter list.
NOTE: As of now, only PARENT-CHILD HIERARCHY is supported.
◈ After this, you need to mention SOURCE VIEW.
NOTE: Here a CDS view should be used which has self-association between Parent and Child and that association should be exposed in that CDS (see example below).
◈ Now mention the exposed association name in CHILD TO PARENT ASSOCIATION _exposedAssc
◈ Now the user has an option to put DIRECTORY syntax with directory association and filtered by condition expressions. This will filter out the following rows which are not satisfying the condition expression
Example of this:
CDS View
@AbapCatalog.sqlViewName: 'DEMOPACHSRCDIR'
@AccessControl.authorizationCheck: #NOT_REQUIRED
define view DEMO_CDS_PARENT_CHILD_SRC_DIR
as select from
demo_parchld_dir
association [1..*] to DEMO_CDS_PARENT_CHILD_SRC_DIR as _pcr on
$projection.parent = _pcr.id and
$projection.dir_entry = _pcr.id
association [1..*] to demo_hiera_dir as _dir on
$projection.dir_entry = _dir.dir_entry
{
_pcr,
_dir,
id,
parent_id as parent,
dir_entry
}
CDS Hierarchy
define hierarchy DEMO_CDS_PARENT_CHILD_DIR
with parameters
p_id1 : abap.char(2),
p_id2 : abap.char(2),
p_dir : abap.char(2)
as parent child hierarchy(
source
DEMO_CDS_PARENT_CHILD_SRC_DIR
child to parent association _pcr
directory _dir filter by dir_entry = :p_dir
start where
id = :p_id1 or id = :p_id2
siblings order by
parent
multiple parents allowed
)
{
id,
parent,
dir_entry
}
Create an ABAP table
From the Context menu of an ABAP system select TABLE and click on the Database table to create table
Also, I will demonstrate a step-by-step tutorial to create a simple basic CDS Hierarchy. To make it really relevant for everyone I will start by creating TABLES, then to CDS Views, and then CDS Hierarchy.
Before jumping into Tutorial lets see the Introduction of this CDS Hierarchy.
CDS Hierarchies
CDS Hierarchy is a new way to define Hierarchy, it is written using Data Definition Language.
Syntax starts with DEFINE HIERARCHY
Basic Syntax rule of CDS Hierarchy follows
[@entity_annot1]
[@entity_annot2]
...
[@hierarchy_annot1]
[@hierarchy_annot2]
...
[DEFINE] HIERARCHY cds_entity
[parameter_list]
AS PARENT CHILD HIERARCHY(
SOURCE cds_view
CHILD TO PARENT ASSOCIATION _hierarchy_assoc
[DIRECTORY _directory_assoc FILTER BY cond_expr]
START WHERE cond_expr
SIBLINGS ORDER BY field1 [ASCENDING|DESCENDING][,
field2 [ASCENDING|DESCENDING], ...]
[DEPTH depth]
[NODETYPE node_type]
[MULTIPLE PARENTS {NOT ALLOWED}|LEAVES|ALLOWED]
[ORPHANS ERROR|IGNORE|ROOT]
[CYCLES ERROR|BREAKUP]
[GENERATE SPANTREE] )
{ element_list }
◈ Here First thing is to define CDS entity name for Hierarchy.
DEFINE HIERARCHY ztest_hierarchy
◈ Now if there are any input parameters required then mention below this in the parameter list.
NOTE: As of now, only PARENT-CHILD HIERARCHY is supported.
◈ After this, you need to mention SOURCE VIEW.
NOTE: Here a CDS view should be used which has self-association between Parent and Child and that association should be exposed in that CDS (see example below).
◈ Now mention the exposed association name in CHILD TO PARENT ASSOCIATION _exposedAssc
◈ Now the user has an option to put DIRECTORY syntax with directory association and filtered by condition expressions. This will filter out the following rows which are not satisfying the condition expression
Example of this:
CDS View
@AbapCatalog.sqlViewName: 'DEMOPACHSRCDIR'
@AccessControl.authorizationCheck: #NOT_REQUIRED
define view DEMO_CDS_PARENT_CHILD_SRC_DIR
as select from
demo_parchld_dir
association [1..*] to DEMO_CDS_PARENT_CHILD_SRC_DIR as _pcr on
$projection.parent = _pcr.id and
$projection.dir_entry = _pcr.id
association [1..*] to demo_hiera_dir as _dir on
$projection.dir_entry = _dir.dir_entry
{
_pcr,
_dir,
id,
parent_id as parent,
dir_entry
}
CDS Hierarchy
define hierarchy DEMO_CDS_PARENT_CHILD_DIR
with parameters
p_id1 : abap.char(2),
p_id2 : abap.char(2),
p_dir : abap.char(2)
as parent child hierarchy(
source
DEMO_CDS_PARENT_CHILD_SRC_DIR
child to parent association _pcr
directory _dir filter by dir_entry = :p_dir
start where
id = :p_id1 or id = :p_id2
siblings order by
parent
multiple parents allowed
)
{
id,
parent,
dir_entry
}
- Now comes START WHERE with condition expression, where a user can put from where hierarchy should begin
- After this user can sort the Hierarchy w.r.t to its SIBLINGS with the statement – SIBLINGS ORDER BY
- Now comes some optional syntaxes:
- DEPTH – used to define hierarchy till which level, you can use parameter also in this.
- NODETYPE – its defines element of the Hierarchy as node type, which is used on consuming this Hierarchy
- MULTIPLE PARENTS – This can have three values ALLOWED, LEAVES ONLY and NOT ALLOWED which is self-understandable
- ORPHANS – If the Source view has some orphan nodes then it has three options – ERROR or IGNORE or ASSIGN to ROOT NODES
- CYCLES – This is used to check if there is any cycle created, if a user is mentioning this then MULTIPLE PARENTS ALLOWED need to be put.
- GENERATE SPAN TREE is still not supported.
Create an ABAP table
From the Context menu of an ABAP system select TABLE and click on the Database table to create table
1. Enter the package – in my case, I used Local Package
2. Enter table name – Z**_HIER_TBL
3. Enter Description – Parent-Child Hierarchy table
Now add the fields:
◈ id – Data Type (char10)
◈ parentId – Data Type (char10)
◈ Name – Data Type (char50)
Now let’s write a report program to insert values in this table
From the context menu select others ABAP object and then search for Program
Now add values to the table
Now execute the report to insert values in the table.
After executing to report go to the table and press F8 to see that values are inserted in the table
Create a CDS View
Now after creating a table and inserting values into it, let’s create CDS view
Open the context menu and search for Data Definition
Add below code to CDS. Here we are creating self association with Parent Id.
@AbapCatalog.sqlViewName: 'ZYV_HIER'
@AbapCatalog.compiler.compareFilter: true
@AbapCatalog.preserveKey: true
@AccessControl.authorizationCheck: #CHECK
@EndUserText.label: 'CDS Hierarchy view for Parent Child Hier'
define view ZYV_PARENT_CHILD_HIER as select from zyv_hier_tbl
association[1..1] to ZYV_PARENT_CHILD_HIER as _child on
$projection.parentId = _child.id
{
_child,
id,
parentid as parent,
name
}
Note: Exposing of association is important here as it will be used in creating Hierarchy
Create CDS Hierarchy
Now its time to create CDS Hierarchy on top of this CDS View. Again open the context menu and search for Data Definition
Now paste below code to create Hierarchy.
define hierarchy ZYV_HIERARCHY
as parent child hierarchy(
source ZYV_PARENT_CHILD_HIER
child to parent association _child
start where
id = '1'
siblings order by
id ascending
)
{
id,
parent,
$node.hierarchy_rank as h_rank,
$node.hierarchy_tree_size as h_tree_size,
$node.hierarchy_parent_rank as h_parent_rank,
$node.hierarchy_level as h_level,
$node.hierarchy_is_cycle as h_is_cycle,
$node.hierarchy_is_orphan as h_is_orphan,
$node.node_id as h_node_id,
$node.parent_id as h_parent_id
}
Here we have:
◈ Source as the CDS View we created before this step.
◈ We have access to $node where we get all the values regarding hierarchy
◈ In place of Start where – Id as 1 we can put a parameter which is then passed from outside
No comments:
Post a Comment