Contents
1 Context
2 Sample hierarchy
3 Openpage solutions
3.1 RT_ENTITY
3.2 RT__ENTITY_ENTITY
3.3 RV_ENTITY_GPC
3.4 [Business Entity GPC]
4 Prompt cases
4.1 Case 1 - Only the selected entities
4.2 Case 2 - The selected entity AND the next level
4.3 Case 3 - The selected entity AND all levels below
4.4 Case 4 - The next level BELOW the selected entity
4.5 Case 5 - All levels BELOW the selected entity
5 Report cases
5.1 Case 1 - Only the selected entities – NO aggregation
5.2 Case 2 - Only the selected entities – WITH aggregation – incl. Entity itself
5.3 Case 3 - Only the selected entities – WITH aggregation – NOT incl. Entity itself
5.4 Case 4 - The next level BELOW the selected entity - NO aggregation
5.5 Case 5 - The next level BELOW the selected entity - WITH aggregation – Incl. Entity itself
5.6 Case 6 - The next level BELOW the selected entity - WITH aggregation – NOT Incl. Entity itself
5.7 Case 7 - All levels BELOW the selected entity
6 Appendix
6.1 Loss data in detail with GPC business entity
1 Context
2 Sample hierarchy
3 Openpage solutions
3.1 RT_ENTITY
3.2 RT__ENTITY_ENTITY
3.3 RV_ENTITY_GPC
3.4 [Business Entity GPC]
4 Prompt cases
4.1 Case 1 - Only the selected entities
4.2 Case 2 - The selected entity AND the next level
4.3 Case 3 - The selected entity AND all levels below
4.4 Case 4 - The next level BELOW the selected entity
4.5 Case 5 - All levels BELOW the selected entity
5 Report cases
5.1 Case 1 - Only the selected entities – NO aggregation
5.2 Case 2 - Only the selected entities – WITH aggregation – incl. Entity itself
5.3 Case 3 - Only the selected entities – WITH aggregation – NOT incl. Entity itself
5.4 Case 4 - The next level BELOW the selected entity - NO aggregation
5.5 Case 5 - The next level BELOW the selected entity - WITH aggregation – Incl. Entity itself
5.6 Case 6 - The next level BELOW the selected entity - WITH aggregation – NOT Incl. Entity itself
5.7 Case 7 - All levels BELOW the selected entity
6 Appendix
6.1 Loss data in detail with GPC business entity
Context
From business point of view, it is relative easy to understand business entity as hierarchical objects, and each business entity can associate with other risk objects such as loss event. However, to report data based on OpenPages report Framework becomes difficult, as we need to understand the real behavior of this report framework. This document uses a sample data to provide a guideline of report development based on OpenPages report framework with respect to business entity. 5 prompt cases and 7 report cases will be documented.
Sample hierarchy
In order to explain the concept, a sample data is provided as below. Assume loss with red color is actual loss for that node, while the total loss is shown as green color.
Openpage solutions
Behind the scene, IBM Openpages create report framework as below
Report Framework
|
[DEFAULT (Relational)].[Business Entity GPC]
|
Report Views
|
RV_ENTITY_GPC
|
Report Tables
|
RT_ENTITY, RT__ENTITY_ENTITY
|
Basic data table
|
RESOURCES, RESRELATIONSHIPS, ASSETTYPES, etc
|
RT_ENTITY
ENTITY_ID
|
FULL_PATH
|
1
|
/Node1
|
2
|
/Node1/Node2
|
3
|
/Node1/Node3
|
4
|
/Node1/Node2/Node4
|
5
|
/Node1/Node2/Node5
|
6
|
/Node1/Node2/Node6
|
7
|
/Node1/Node2/Node5/Node7
|
8
|
/Node1/Node2/Node5/Node8
|
RT__ENTITY_ENTITY
P_ENTITY_ID
|
C_ENTITY_ID
|
DISTANCE
|
1
|
1
|
0
|
1
|
2
|
1
|
1
|
3
|
1
|
1
|
4
|
2
|
1
|
5
|
2
|
1
|
6
|
2
|
1
|
7
|
3
|
1
|
8
|
3
|
2
|
2
|
0
|
2
|
4
|
1
|
3
|
3
|
0
|
3
|
5
|
1
|
3
|
6
|
1
|
3
|
7
|
2
|
3
|
8
|
2
|
4
|
4
|
0
|
5
|
5
|
0
|
5
|
7
|
1
|
5
|
8
|
1
|
6
|
6
|
0
|
7
|
7
|
0
|
8
|
8
|
0
|
RV_ENTITY_GPC
RT__ENTITY_ENTITY
|
RT__ENTITY_ENTITY (Distance = 0,1)
|
RV_ENTITY_GPC
| ||||||||||
P_ENTITY_ID
|
C_ENTITY_ID
|
DISTANCE
|
P_ENTITY_ID
|
C_ENTITY_ID
|
DISTANCE
|
G_ENTITY_ID
|
P_ENTITY_ID
|
G2P_DISTANCE
|
C_ENTITY_ID
|
P2C_DISTANCE
| ||
1
|
1
|
0
|
1
|
1
|
0
|
1
|
1
|
0
|
1
|
1
| ||
1
|
2
|
1
|
1
|
2
|
1
|
1
|
2
|
1
|
2
|
1
| ||
1
|
3
|
1
|
1
|
3
|
1
|
1
|
2
|
1
|
4
|
1
| ||
1
|
4
|
2
|
2
|
2
|
0
|
1
|
3
|
1
|
3
|
1
| ||
1
|
5
|
2
|
2
|
4
|
1
|
1
|
3
|
1
|
5
|
1
| ||
1
|
6
|
2
|
3
|
3
|
0
|
1
|
3
|
1
|
6
|
1
| ||
1
|
7
|
3
|
3
|
5
|
1
|
1
|
3
|
1
|
7
|
1
| ||
1
|
8
|
3
|
3
|
6
|
1
|
1
|
3
|
1
|
8
|
1
| ||
2
|
2
|
0
|
4
|
4
|
0
|
2
|
2
|
0
|
2
|
2
| ||
2
|
4
|
1
|
5
|
5
|
0
|
2
|
4
|
1
|
4
|
2
| ||
3
|
3
|
0
|
5
|
7
|
1
|
3
|
3
|
0
|
3
|
3
| ||
3
|
5
|
1
|
5
|
8
|
1
|
3
|
5
|
1
|
5
|
3
| ||
3
|
6
|
1
|
6
|
6
|
0
|
3
|
5
|
1
|
7
|
3
| ||
3
|
7
|
2
|
7
|
7
|
0
|
3
|
5
|
1
|
8
|
3
| ||
3
|
8
|
2
|
8
|
8
|
0
|
3
|
6
|
1
|
6
|
3
| ||
4
|
4
|
0
|
4
|
4
|
0
|
4
|
4
| |||||
5
|
5
|
0
|
5
|
5
|
0
|
5
|
5
| |||||
5
|
7
|
1
|
5
|
7
|
1
|
7
|
5
| |||||
5
|
8
|
1
|
5
|
8
|
1
|
8
|
5
| |||||
6
|
6
|
0
|
6
|
6
|
0
|
6
|
6
| |||||
7
|
7
|
0
|
7
|
7
|
0
|
7
|
7
| |||||
8
|
8
|
0
|
8
|
8
|
0
|
8
|
8
| |||||
[Business Entity GPC]
#
|
G_ENTITY_ID
|
G_ENTITY_FULL_PATH
|
P_ENTITY_ID
|
P_ENTITY_FULL_PATH
|
G2P_DISTANCE
|
C_ENTITY_ID
|
C_ENTITY_FULL_PATH
|
P2C_DISTANCE
|
1
|
1
|
/Node1
|
1
|
/Node1
|
0
|
1
|
/Node1
|
0
|
2
|
1
|
/Node1
|
2
|
/Node1/Node2
|
1
|
2
|
/Node1/Node2
|
0
|
3
|
1
|
/Node1
|
2
|
/Node1/Node2
|
1
|
4
|
/Node1/Node2/Node4
|
1
|
4
|
1
|
/Node1
|
3
|
/Node1/Node3
|
1
|
3
|
/Node1/Node3
|
0
|
5
|
1
|
/Node1
|
3
|
/Node1/Node3
|
1
|
5
|
/Node1/Node3/Node5
|
1
|
6
|
1
|
/Node1
|
3
|
/Node1/Node3
|
1
|
6
|
/Node1/Node3/Node6
|
1
|
7
|
1
|
/Node1
|
3
|
/Node1/Node3
|
1
|
7
|
/Node1/Node3/Node5/Node7
|
2
|
8
|
1
|
/Node1
|
3
|
/Node1/Node3
|
1
|
8
|
/Node1/Node3/Node5/Node8
|
2
|
9
|
2
|
/Node1/Node2
|
2
|
/Node1/Node2
|
0
|
2
|
/Node1/Node2
|
0
|
10
|
2
|
/Node1/Node2
|
4
|
/Node1/Node2/Node4
|
1
|
4
|
/Node1/Node2/Node4
|
0
|
11
|
3
|
/Node1/Node3
|
3
|
/Node1/Node3
|
0
|
3
|
/Node1/Node3
|
0
|
12
|
3
|
/Node1/Node3
|
5
|
/Node1/Node3/Node5
|
1
|
5
|
/Node1/Node3/Node5
|
0
|
13
|
3
|
/Node1/Node3
|
5
|
/Node1/Node3/Node5
|
1
|
7
|
/Node1/Node3/Node5/Node7
|
1
|
14
|
3
|
/Node1/Node3
|
5
|
/Node1/Node3/Node5
|
1
|
8
|
/Node1/Node3/Node5/Node8
|
1
|
15
|
3
|
/Node1/Node3
|
6
|
/Node1/Node3/Node6
|
1
|
6
|
/Node1/Node3/Node6
|
0
|
16
|
4
|
/Node1/Node2/Node4
|
4
|
/Node1/Node2/Node4
|
0
|
4
|
/Node1/Node2/Node4
|
0
|
17
|
5
|
/Node1/Node3/Node5
|
5
|
/Node1/Node3/Node5
|
0
|
5
|
/Node1/Node3/Node5
|
0
|
18
|
5
|
/Node1/Node3/Node5
|
7
|
/Node1/Node3/Node5/Node7
|
1
|
7
|
/Node1/Node3/Node5/Node7
|
0
|
19
|
5
|
/Node1/Node3/Node5
|
8
|
/Node1/Node3/Node5/Node8
|
1
|
8
|
/Node1/Node3/Node5/Node8
|
0
|
20
|
6
|
/Node1/Node3/Node6
|
6
|
/Node1/Node3/Node6
|
0
|
6
|
/Node1/Node3/Node6
|
0
|
21
|
7
|
/Node1/Node3/Node5/Node7
|
7
|
/Node1/Node3/Node5/Node7
|
0
|
7
|
/Node1/Node3/Node5/Node7
|
0
|
22
|
8
|
/Node1/Node3/Node5/Node8
|
8
|
/Node1/Node3/Node5/Node8
|
0
|
8
|
/Node1/Node3/Node5/Node8
|
0
|
Prompt cases
Case 1 - Only the selected entities
Filter
G2P_DISTANCE = 0
G_ENTITY_FULL_PATH in ('/Node1','/Node1/Node2')
Query Items
G_ENTITY_ID
G_ENTITY_FULL_PATH
Result
G_ENTITY_ID
|
G_ENTITY_FULL_PATH
|
1
|
/Node1
|
2
|
/Node1/Node2
|
Case 2 - The selected entity AND the next level
Filter
P2C_DISTANCE = 0
G_ENTITY_FULL_PATH in ('/Node1')
Query Items
P_ENTITY_ID
P_ENTITY_FULL_PATH
Result
P_ENTITY_ID
|
P_ENTITY_FULL_PATH
|
1
|
/Node1
|
2
|
/Node1/Node2
|
3
|
/Node1/Node3
|
Case 3 - The selected entity AND all levels below
Filter
G_ENTITY_FULL_PATH in ('/Node1')
Query Items
C_ENTITY_ID
C_ENTITY_FULL_PATH
Result
C_ENTITY_ID
|
C_ENTITY_FULL_PATH
|
1
|
/Node1
|
2
|
/Node1/Node2
|
3
|
/Node1/Node3
|
4
|
/Node1/Node2/Node4
|
5
|
/Node1/Node3/Node5
|
6
|
/Node1/Node3/Node6
|
7
|
/Node1/Node3/Node5/Node7
|
8
|
/Node1/Node3/Node5/Node8
|
Case 4 - The next level BELOW the selected entity
Filter
G2P_DISTANCE = 1
P2C_DISTANCE = 0
G_ENTITY_FULL_PATH in ('/Node1')
Query Items
P_ENTITY_ID
P_ENTITY_FULL_PATH
Result
P_ENTITY_ID
|
P_ENTITY_FULL_PATH
|
2
|
/Node1/Node2
|
3
|
/Node1/Node3
|
Case 5 - All levels BELOW the selected entity
Filter
G2P_DISTANCE = 1
G_ENTITY_FULL_PATH in ('/Node1')
Query Items
C_ENTITY_ID
C_ENTITY_FULL_PATH
Result
C_ENTITY_ID
|
C_ENTITY_FULL_PATH
|
2
|
/Node1/Node2
|
3
|
/Node1/Node3
|
4
|
/Node1/Node2/Node4
|
5
|
/Node1/Node3/Node5
|
6
|
/Node1/Node3/Node6
|
7
|
/Node1/Node3/Node5/Node7
|
8
|
/Node1/Node3/Node5/Node8
|
Report cases
Sample below is based on data (s. Sample hierarchy for detail )
ENTITY
|
ENTITY_FULL_PATH
|
Loss
|
3
|
/Node1/Node3
|
$2
|
4
|
/Node1/Node2/Node4
|
$1
|
7
|
/Node1/Node3/Node5/Node7
|
$3
|
All these loss is associated with Child Entity based on OpenPages Framework in term of modelling, while loss can happen to any entity from view of end users.
Case 1 - Only the selected entities – NO aggregation
Filter
G2P_DISTANCE = 0
G_ENTITY_FULL_PATH in ('/Node1','/Node1/Node2')
Query Items
G_ENTITY_ID
G_ENTITY_FULL_PATH
Result
G_ENTITY_ID
|
G_ENTITY_FULL_PATH
|
Loss
|
1
|
/Node1
|
$0
|
2
|
/Node1/Node2
|
$0
|
Actually Cognos will display no data
Behind the scene
select G_ENTITY_ID, G_ENTITY_FULL_PATH, sum(Loss) from GPC
where G2P_DISTANCE = 0 and G_ENTITY_FULL_PATH in ('/Node1','/Node1/Node2')
group by G_ENTITY_ID, G_ENTITY_FULL_PATH
#
|
G_ENTITY_ID
|
G_ENTITY_FULL_PATH
|
P_ENTITY_ID
|
P_ENTITY_FULL_PATH
|
G2P_DISTANCE
|
C_ENTITY_ID
|
C_ENTITY_FULL_PATH
|
P2C_DISTANCE
|
Loss
|
1
|
1
|
/Node1
|
1
|
/Node1
|
0
|
1
|
/Node1
|
0
| |
9
|
2
|
/Node1/Node2
|
2
|
/Node1/Node2
|
0
|
2
|
/Node1/Node2
|
0
|
Case 2 - Only the selected entities – WITH aggregation – incl. Entity itself
Filter
G_ENTITY_FULL_PATH in ('/Node1','/Node1/Node2')
Query Items
G_ENTITY_ID
G_ENTITY_FULL_PATH
Result
G_ENTITY_ID
|
G_ENTITY_FULL_PATH
|
Loss
|
1
|
/Node1
|
$6
|
2
|
/Node1/Node2
|
$1
|
Behind the scene
select G_ENTITY_ID, G_ENTITY_FULL_PATH, sum(Loss) from GPC
where G_ENTITY_FULL_PATH in ('/Node1','/Node1/Node2')
group by G_ENTITY_ID, G_ENTITY_FULL_PATH
#
|
G_ENTITY_ID
|
G_ENTITY_FULL_PATH
|
P_ENTITY_ID
|
P_ENTITY_FULL_PATH
|
G2P_DISTANCE
|
C_ENTITY_ID
|
C_ENTITY_FULL_PATH
|
P2C_DISTANCE
|
Loss
|
1
|
1
|
/Node1
|
1
|
/Node1
|
0
|
1
|
/Node1
|
0
| |
2
|
1
|
/Node1
|
2
|
/Node1/Node2
|
1
|
2
|
/Node1/Node2
|
0
| |
3
|
1
|
/Node1
|
2
|
/Node1/Node2
|
1
|
4
|
/Node1/Node2/Node4
|
1
|
$1
|
4
|
1
|
/Node1
|
3
|
/Node1/Node3
|
1
|
3
|
/Node1/Node3
|
0
|
$2
|
5
|
1
|
/Node1
|
3
|
/Node1/Node3
|
1
|
5
|
/Node1/Node3/Node5
|
1
| |
6
|
1
|
/Node1
|
3
|
/Node1/Node3
|
1
|
6
|
/Node1/Node3/Node6
|
1
| |
7
|
1
|
/Node1
|
3
|
/Node1/Node3
|
1
|
7
|
/Node1/Node3/Node5/Node7
|
2
|
$3
|
8
|
1
|
/Node1
|
3
|
/Node1/Node3
|
1
|
8
|
/Node1/Node3/Node5/Node8
|
2
| |
9
|
2
|
/Node1/Node2
|
2
|
/Node1/Node2
|
0
|
2
|
/Node1/Node2
|
0
| |
10
|
2
|
/Node1/Node2
|
4
|
/Node1/Node2/Node4
|
1
|
4
|
/Node1/Node2/Node4
|
0
|
$1
|
Case 3 - Only the selected entities – WITH aggregation – NOT incl. Entity itself
Filter
G2P_DISTANCE = 1
G_ENTITY_FULL_PATH in ('/Node1','/Node1/Node2')
Query Items
G_ENTITY_ID
G_ENTITY_FULL_PATH
Result
G_ENTITY_ID
|
G_ENTITY_FULL_PATH
|
Loss
|
1
|
/Node1
|
$6
|
2
|
/Node1/Node2
|
$1
|
Behind the scene
select G_ENTITY_ID, G_ENTITY_FULL_PATH, sum(Loss) from GPC
where G2P_DISTANCE = 1 and G_ENTITY_FULL_PATH in ('/Node1','/Node1/Node2')
group by G_ENTITY_ID, G_ENTITY_FULL_PATH
#
|
G_ENTITY_ID
|
G_ENTITY_FULL_PATH
|
P_ENTITY_ID
|
P_ENTITY_FULL_PATH
|
G2P_DISTANCE
|
C_ENTITY_ID
|
C_ENTITY_FULL_PATH
|
P2C_DISTANCE
|
Loss
|
2
|
1
|
/Node1
|
2
|
/Node1/Node2
|
1
|
2
|
/Node1/Node2
|
0
| |
3
|
1
|
/Node1
|
2
|
/Node1/Node2
|
1
|
4
|
/Node1/Node2/Node4
|
1
|
$1
|
4
|
1
|
/Node1
|
3
|
/Node1/Node3
|
1
|
3
|
/Node1/Node3
|
0
|
$2
|
5
|
1
|
/Node1
|
3
|
/Node1/Node3
|
1
|
5
|
/Node1/Node3/Node5
|
1
| |
6
|
1
|
/Node1
|
3
|
/Node1/Node3
|
1
|
6
|
/Node1/Node3/Node6
|
1
| |
7
|
1
|
/Node1
|
3
|
/Node1/Node3
|
1
|
7
|
/Node1/Node3/Node5/Node7
|
2
|
$3
|
8
|
1
|
/Node1
|
3
|
/Node1/Node3
|
1
|
8
|
/Node1/Node3/Node5/Node8
|
2
| |
10
|
2
|
/Node1/Node2
|
4
|
/Node1/Node2/Node4
|
1
|
4
|
/Node1/Node2/Node4
|
0
|
$1
|
Case 4 - The next level BELOW the selected entity - NO aggregation
Filter
G2P_DISTANCE = 1
P2C_DISTANCE = 0
G_ENTITY_FULL_PATH in ('/Node1')
Query Items
P_ENTITY_ID
P_ENTITY_FULL_PATH
Result
P_ENTITY_ID
|
P_ENTITY_FULL_PATH
|
Loss
|
2
|
/Node1/Node2
|
$0
|
3
|
/Node1/Node3
|
$2
|
Behind the scene
select P_ENTITY_ID, P_ENTITY_FULL_PATH, sum(Loss) from GPC
where G2P_DISTANCE = 1 and P2C_DISTANCE = 0 and G_ENTITY_FULL_PATH in ('/Node1')
group by P_ENTITY_ID, P_ENTITY_FULL_PATH
#
|
G_ENTITY_ID
|
G_ENTITY_FULL_PATH
|
P_ENTITY_ID
|
P_ENTITY_FULL_PATH
|
G2P_DISTANCE
|
C_ENTITY_ID
|
C_ENTITY_FULL_PATH
|
P2C_DISTANCE
|
Loss
|
2
|
1
|
/Node1
|
2
|
/Node1/Node2
|
1
|
2
|
/Node1/Node2
|
0
| |
4
|
1
|
/Node1
|
3
|
/Node1/Node3
|
1
|
3
|
/Node1/Node3
|
0
|
$2
|
Case 5 - The next level BELOW the selected entity - WITH aggregation – Incl. Entity itself
Filter
G2P_DISTANCE = 1
G_ENTITY_FULL_PATH in ('/Node1')
Query Items
P_ENTITY_ID
P_ENTITY_FULL_PATH
Result
P_ENTITY_ID
|
P_ENTITY_FULL_PATH
|
Loss
|
2
|
/Node1/Node2
|
$1
|
3
|
/Node1/Node3
|
$5
|
Behind the scene
select P_ENTITY_ID, P_ENTITY_FULL_PATH, sum(Loss) from GPC
where G2P_DISTANCE = 1 and G_ENTITY_FULL_PATH in ('/Node1')
group by P_ENTITY_ID, P_ENTITY_FULL_PATH
#
|
G_ENTITY_ID
|
G_ENTITY_FULL_PATH
|
P_ENTITY_ID
|
P_ENTITY_FULL_PATH
|
G2P_DISTANCE
|
C_ENTITY_ID
|
C_ENTITY_FULL_PATH
|
P2C_DISTANCE
|
Loss
|
2
|
1
|
/Node1
|
2
|
/Node1/Node2
|
1
|
2
|
/Node1/Node2
|
0
| |
3
|
1
|
/Node1
|
2
|
/Node1/Node2
|
1
|
4
|
/Node1/Node2/Node4
|
1
|
$1
|
4
|
1
|
/Node1
|
3
|
/Node1/Node3
|
1
|
3
|
/Node1/Node3
|
0
|
$2
|
5
|
1
|
/Node1
|
3
|
/Node1/Node3
|
1
|
5
|
/Node1/Node3/Node5
|
1
| |
6
|
1
|
/Node1
|
3
|
/Node1/Node3
|
1
|
6
|
/Node1/Node3/Node6
|
1
| |
7
|
1
|
/Node1
|
3
|
/Node1/Node3
|
1
|
7
|
/Node1/Node3/Node5/Node7
|
2
|
$3
|
8
|
1
|
/Node1
|
3
|
/Node1/Node3
|
1
|
8
|
/Node1/Node3/Node5/Node8
|
2
|
Case 6 - The next level BELOW the selected entity - WITH aggregation – NOT Incl. Entity itself
Filter
G2P_DISTANCE = 1
P2C_DISTANCE <> 0
G_ENTITY_FULL_PATH in ('/Node1')
Query Items
P_ENTITY_ID
P_ENTITY_FULL_PATH
Result
P_ENTITY_ID
|
P_ENTITY_FULL_PATH
|
Loss
|
2
|
/Node1/Node2
|
$1
|
3
|
/Node1/Node3
|
$3
|
Behind the scene
select P_ENTITY_ID, P_ENTITY_FULL_PATH, sum(Loss) from GPC
where G2P_DISTANCE = 1 and P2C_DISTANCE <> 0 and G_ENTITY_FULL_PATH in ('/Node1')
group by P_ENTITY_ID, P_ENTITY_FULL_PATH
#
|
G_ENTITY_ID
|
G_ENTITY_FULL_PATH
|
P_ENTITY_ID
|
P_ENTITY_FULL_PATH
|
G2P_DISTANCE
|
C_ENTITY_ID
|
C_ENTITY_FULL_PATH
|
P2C_DISTANCE
|
Loss
|
3
|
1
|
/Node1
|
2
|
/Node1/Node2
|
1
|
4
|
/Node1/Node2/Node4
|
1
|
$1
|
5
|
1
|
/Node1
|
3
|
/Node1/Node3
|
1
|
5
|
/Node1/Node3/Node5
|
1
| |
6
|
1
|
/Node1
|
3
|
/Node1/Node3
|
1
|
6
|
/Node1/Node3/Node6
|
1
| |
7
|
1
|
/Node1
|
3
|
/Node1/Node3
|
1
|
7
|
/Node1/Node3/Node5/Node7
|
2
|
$3
|
8
|
1
|
/Node1
|
3
|
/Node1/Node3
|
1
|
8
|
/Node1/Node3/Node5/Node8
|
2
|
Case 7 - All levels BELOW the selected entity
Filter
G2P_DISTANCE = 1
G_ENTITY_FULL_PATH in ('/Node1')
Query Items
C_ENTITY_ID
C_ENTITY_FULL_PATH
Result
C_ENTITY_ID
|
C_ENTITY_FULL_PATH
|
Loss
|
3
|
/Node1/Node3
|
$2
|
4
|
/Node1/Node2/Node4
|
$1
|
7
|
/Node1/Node3/Node5/Node7
|
$3
|
Behind the scene
select C_ENTITY_ID, C_ENTITY_FULL_PATH, sum(Loss) from GPC
where G2P_DISTANCE = 1 and G_ENTITY_FULL_PATH in ('/Node1')
group by C_ENTITY_ID, C_ENTITY_FULL_PATH
#
|
G_ENTITY_ID
|
G_ENTITY_FULL_PATH
|
P_ENTITY_ID
|
P_ENTITY_FULL_PATH
|
G2P_DISTANCE
|
C_ENTITY_ID
|
C_ENTITY_FULL_PATH
|
P2C_DISTANCE
|
Loss
|
2
|
1
|
/Node1
|
2
|
/Node1/Node2
|
1
|
2
|
/Node1/Node2
|
0
| |
3
|
1
|
/Node1
|
2
|
/Node1/Node2
|
1
|
4
|
/Node1/Node2/Node4
|
1
|
$1
|
4
|
1
|
/Node1
|
3
|
/Node1/Node3
|
1
|
3
|
/Node1/Node3
|
0
|
$2
|
5
|
1
|
/Node1
|
3
|
/Node1/Node3
|
1
|
5
|
/Node1/Node3/Node5
|
1
| |
6
|
1
|
/Node1
|
3
|
/Node1/Node3
|
1
|
6
|
/Node1/Node3/Node6
|
1
| |
7
|
1
|
/Node1
|
3
|
/Node1/Node3
|
1
|
7
|
/Node1/Node3/Node5/Node7
|
2
|
$3
|
8
|
1
|
/Node1
|
3
|
/Node1/Node3
|
1
|
8
|
/Node1/Node3/Node5/Node8
|
2
|
Appendix
Loss data in detail with GPC business entity
#
|
G_ENTITY_ID
|
G_ENTITY_FULL_PATH
|
P_ENTITY_ID
|
P_ENTITY_FULL_PATH
|
G2P_DISTANCE
|
C_ENTITY_ID
|
C_ENTITY_FULL_PATH
|
P2C_DISTANCE
|
Loss
|
1
|
1
|
/Node1
|
1
|
/Node1
|
0
|
1
|
/Node1
|
0
| |
2
|
1
|
/Node1
|
2
|
/Node1/Node2
|
1
|
2
|
/Node1/Node2
|
0
| |
3
|
1
|
/Node1
|
2
|
/Node1/Node2
|
1
|
4
|
/Node1/Node2/Node4
|
1
|
$1
|
4
|
1
|
/Node1
|
3
|
/Node1/Node3
|
1
|
3
|
/Node1/Node3
|
0
|
$2
|
5
|
1
|
/Node1
|
3
|
/Node1/Node3
|
1
|
5
|
/Node1/Node3/Node5
|
1
| |
6
|
1
|
/Node1
|
3
|
/Node1/Node3
|
1
|
6
|
/Node1/Node3/Node6
|
1
| |
7
|
1
|
/Node1
|
3
|
/Node1/Node3
|
1
|
7
|
/Node1/Node3/Node5/Node7
|
2
|
$3
|
8
|
1
|
/Node1
|
3
|
/Node1/Node3
|
1
|
8
|
/Node1/Node3/Node5/Node8
|
2
| |
9
|
2
|
/Node1/Node2
|
2
|
/Node1/Node2
|
0
|
2
|
/Node1/Node2
|
0
| |
10
|
2
|
/Node1/Node2
|
4
|
/Node1/Node2/Node4
|
1
|
4
|
/Node1/Node2/Node4
|
0
|
$1
|
11
|
3
|
/Node1/Node3
|
3
|
/Node1/Node3
|
0
|
3
|
/Node1/Node3
|
0
|
$2
|
12
|
3
|
/Node1/Node3
|
5
|
/Node1/Node3/Node5
|
1
|
5
|
/Node1/Node3/Node5
|
0
| |
13
|
3
|
/Node1/Node3
|
5
|
/Node1/Node3/Node5
|
1
|
7
|
/Node1/Node3/Node5/Node7
|
1
|
$3
|
14
|
3
|
/Node1/Node3
|
5
|
/Node1/Node3/Node5
|
1
|
8
|
/Node1/Node3/Node5/Node8
|
1
| |
15
|
3
|
/Node1/Node3
|
6
|
/Node1/Node3/Node6
|
1
|
6
|
/Node1/Node3/Node6
|
0
| |
16
|
4
|
/Node1/Node2/Node4
|
4
|
/Node1/Node2/Node4
|
0
|
4
|
/Node1/Node2/Node4
|
0
|
$1
|
17
|
5
|
/Node1/Node3/Node5
|
5
|
/Node1/Node3/Node5
|
0
|
5
|
/Node1/Node3/Node5
|
0
| |
18
|
5
|
/Node1/Node3/Node5
|
7
|
/Node1/Node3/Node5/Node7
|
1
|
7
|
/Node1/Node3/Node5/Node7
|
0
|
$3
|
19
|
5
|
/Node1/Node3/Node5
|
8
|
/Node1/Node3/Node5/Node8
|
1
|
8
|
/Node1/Node3/Node5/Node8
|
0
| |
20
|
6
|
/Node1/Node3/Node6
|
6
|
/Node1/Node3/Node6
|
0
|
6
|
/Node1/Node3/Node6
|
0
| |
21
|
7
|
/Node1/Node3/Node5/Node7
|
7
|
/Node1/Node3/Node5/Node7
|
0
|
7
|
/Node1/Node3/Node5/Node7
|
0
|
$3
|
22
|
8
|
/Node1/Node3/Node5/Node8
|
8
|
/Node1/Node3/Node5/Node8
|
0
|
8
|
/Node1/Node3/Node5/Node8
|
0
|
No comments:
Post a Comment