Skip to Content

Archived discussions are read-only. Learn more about SAP Q&A

dynamic hierarchy in parent child hierarchy table??

Do you have any experience to handle the requirement of dynamic hierarchy in universe/webi?

We have some data in parent child hierarchy as below u201CCustomeru201D table.

Customer

Parent Child

Z A

Z B

A AA

B BB

AA AAA

For example, Company Z is the parent company of Company A.

Another table, Amount is the amount value of different Customers.

Amount

ID Amt

AA 10

AAA 1

BB 2

Is there any functionality in Universe designer to build related Classes and objects, So that the web intelligence documents represent the following report with the drilling results?

When we want to see the ID and Amt, the expected result should be:

Z 13

when we drill down Z

the result should be:

A 11

B 2

when we drill down A:

the result should be:

AA 11

    • notes **

the level of hierarchy is dynamic

Any suggestion is appreciated. Thanks.

Former Member replied

Hi,

The only way to do it is to create recursive derived table that flatten you parent child hierarchy with a given maximum depth.

Here is a sample I built a long time ago to flatten a parent-child hierarchy on Employees table in Foodmart database (SQL Server).

Didier

SELECT DISTINCT 
	Z.employee_id,
	A.supervisor_id_1,
	A.employee_id_1,
	A.full_name_1,
	A.supervisor_id_2,
	A.employee_id_2,
	A.full_name_2,
	A.supervisor_id_3,
	A.employee_id_3,
	A.full_name_3,
	A.supervisor_id_4,
	A.employee_id_4,
	A.full_name_4,
	A.supervisor_id_5,
	A.employee_id_5,
	A.full_name_5,
	A.supervisor_id_6,
	A.employee_id_6,
	A.full_name_6,
	Z.supervisor_id AS supervisor_id_7,
	Z.employee_id AS employee_id_7,
	Z.full_name AS full_name_7
FROM employee Z, 
(
	SELECT DISTINCT 
		A.supervisor_id_1,
		A.employee_id_1,
		A.full_name_1,
		A.supervisor_id_2,
		A.employee_id_2,
		A.full_name_2,
		A.supervisor_id_3,
		A.employee_id_3,
		A.full_name_3,
		A.supervisor_id_4,
		A.employee_id_4,
		A.full_name_4,
		A.supervisor_id_5,
		A.employee_id_5,
		A.full_name_5,
		Z.supervisor_id AS supervisor_id_6,
		Z.employee_id AS employee_id_6,
		Z.full_name AS full_name_6
	FROM employee Z, 
	(
		SELECT DISTINCT 
			A.supervisor_id_1,
			A.employee_id_1,
			A.full_name_1,
			A.supervisor_id_2,
			A.employee_id_2,
			A.full_name_2,
			A.supervisor_id_3,
			A.employee_id_3,
			A.full_name_3,
			A.supervisor_id_4,
			A.employee_id_4,
			A.full_name_4,
			Z.supervisor_id AS supervisor_id_5,
			Z.employee_id AS employee_id_5,
			Z.full_name AS full_name_5
		FROM employee Z, 
		(
			SELECT DISTINCT 
				A.supervisor_id_1,
				A.employee_id_1,
				A.full_name_1,
				A.supervisor_id_2,
				A.employee_id_2,
				A.full_name_2,
				A.supervisor_id_3,
				A.employee_id_3,
				A.full_name_3,
				Z.supervisor_id AS supervisor_id_4,
				Z.employee_id AS employee_id_4,
				Z.full_name AS full_name_4
			FROM employee Z, 
			(
				SELECT DISTINCT 
					A.supervisor_id_1,
					A.employee_id_1,
					A.full_name_1,
					A.supervisor_id_2,
					A.employee_id_2,
					A.full_name_2,
					Z.supervisor_id AS supervisor_id_3,
					Z.employee_id AS employee_id_3,
					Z.full_name AS full_name_3
				FROM employee Z, 
				(
					SELECT DISTINCT 
						A.supervisor_id_1,
						A.employee_id_1,
						A.full_name_1,
						Z.supervisor_id AS supervisor_id_2,
						Z.employee_id AS employee_id_2,
						Z.full_name AS full_name_2
					FROM employee Z, 
					(
						SELECT DISTINCT 
							supervisor_id AS supervisor_id_1,
							employee_id AS employee_id_1,
							full_name AS full_name_1
						 FROM employee
						 WHERE supervisor_id = 0 OR supervisor_id IS NULL
					) A
					WHERE A.employee_id_1 = Z.supervisor_id
				) A
				WHERE A.employee_id_2 = Z.supervisor_id
			) A
			WHERE A.employee_id_3 = Z.supervisor_id
		) A
		WHERE A.employee_id_4 = Z.supervisor_id
	) A
	WHERE A.employee_id_5 = Z.supervisor_id
) A
WHERE A.employee_id_6 = Z.supervisor_id
UNION
SELECT DISTINCT 
	A.employee_id_6 AS employee_id,
	A.supervisor_id_1,
	A.employee_id_1,
	A.full_name_1,
	A.supervisor_id_2,
	A.employee_id_2,
	A.full_name_2,
	A.supervisor_id_3,
	A.employee_id_3,
	A.full_name_3,
	A.supervisor_id_4,
	A.employee_id_4,
	A.full_name_4,
	A.supervisor_id_5,
	A.employee_id_5,
	A.full_name_5,
	A.supervisor_id_6,
	A.employee_id_6,
	A.full_name_6,
	NULL AS supervisor_id_7,
	NULL AS employee_id_7,
	NULL AS full_name_7
FROM employee Z, 
(
	SELECT DISTINCT 
		A.supervisor_id_1,
		A.employee_id_1,
		A.full_name_1,
		A.supervisor_id_2,
		A.employee_id_2,
		A.full_name_2,
		A.supervisor_id_3,
		A.employee_id_3,
		A.full_name_3,
		A.supervisor_id_4,
		A.employee_id_4,
		A.full_name_4,
		A.supervisor_id_5,
		A.employee_id_5,
		A.full_name_5,
		Z.supervisor_id AS supervisor_id_6,
		Z.employee_id AS employee_id_6,
		Z.full_name AS full_name_6
	FROM employee Z, 
	(
		SELECT DISTINCT 
			A.supervisor_id_1,
			A.employee_id_1,
			A.full_name_1,
			A.supervisor_id_2,
			A.employee_id_2,
			A.full_name_2,
			A.supervisor_id_3,
			A.employee_id_3,
			A.full_name_3,
			A.supervisor_id_4,
			A.employee_id_4,
			A.full_name_4,
			Z.supervisor_id AS supervisor_id_5,
			Z.employee_id AS employee_id_5,
			Z.full_name AS full_name_5
		FROM employee Z, 
		(
			SELECT DISTINCT 
				A.supervisor_id_1,
				A.employee_id_1,
				A.full_name_1,
				A.supervisor_id_2,
				A.employee_id_2,
				A.full_name_2,
				A.supervisor_id_3,
				A.employee_id_3,
				A.full_name_3,
				Z.supervisor_id AS supervisor_id_4,
				Z.employee_id AS employee_id_4,
				Z.full_name AS full_name_4
			FROM employee Z, 
			(
				SELECT DISTINCT 
					A.supervisor_id_1,
					A.employee_id_1,
					A.full_name_1,
					A.supervisor_id_2,
					A.employee_id_2,
					A.full_name_2,
					Z.supervisor_id AS supervisor_id_3,
					Z.employee_id AS employee_id_3,
					Z.full_name AS full_name_3
				FROM employee Z, 
				(
					SELECT DISTINCT 
						A.supervisor_id_1,
						A.employee_id_1,
						A.full_name_1,
						Z.supervisor_id AS supervisor_id_2,
						Z.employee_id AS employee_id_2,
						Z.full_name AS full_name_2
					FROM employee Z, 
					(
						SELECT DISTINCT 
							supervisor_id AS supervisor_id_1,
							employee_id AS employee_id_1,
							full_name AS full_name_1
						 FROM employee
						 WHERE supervisor_id = 0 OR supervisor_id IS NULL
					) A
					WHERE A.employee_id_1 = Z.supervisor_id
				) A
				WHERE A.employee_id_2 = Z.supervisor_id
			) A
			WHERE A.employee_id_3 = Z.supervisor_id
		) A
		WHERE A.employee_id_4 = Z.supervisor_id
	) A
	WHERE A.employee_id_5 = Z.supervisor_id
) A
UNION
SELECT DISTINCT 
	A.employee_id_5 AS employee_id,
	A.supervisor_id_1,
	A.employee_id_1,
	A.full_name_1,
	A.supervisor_id_2,
	A.employee_id_2,
	A.full_name_2,
	A.supervisor_id_3,
	A.employee_id_3,
	A.full_name_3,
	A.supervisor_id_4,
	A.employee_id_4,
	A.full_name_4,
	A.supervisor_id_5,
	A.employee_id_5,
	A.full_name_5,
	NULL AS supervisor_id_6,
	NULL AS employee_id_6,
	NULL AS full_name_6,
	NULL AS supervisor_id_7,
	NULL AS employee_id_7,
	NULL AS full_name_7
FROM employee Z, 
(
	SELECT DISTINCT 
		A.supervisor_id_1,
		A.employee_id_1,
		A.full_name_1,
		A.supervisor_id_2,
		A.employee_id_2,
		A.full_name_2,
		A.supervisor_id_3,
		A.employee_id_3,
		A.full_name_3,
		A.supervisor_id_4,
		A.employee_id_4,
		A.full_name_4,
		Z.supervisor_id AS supervisor_id_5,
		Z.employee_id AS employee_id_5,
		Z.full_name AS full_name_5
	FROM employee Z, 
	(
		SELECT DISTINCT 
			A.supervisor_id_1,
			A.employee_id_1,
			A.full_name_1,
			A.supervisor_id_2,
			A.employee_id_2,
			A.full_name_2,
			A.supervisor_id_3,
			A.employee_id_3,
			A.full_name_3,
			Z.supervisor_id AS supervisor_id_4,
			Z.employee_id AS employee_id_4,
			Z.full_name AS full_name_4
		FROM employee Z, 
		(
			SELECT DISTINCT 
				A.supervisor_id_1,
				A.employee_id_1,
				A.full_name_1,
				A.supervisor_id_2,
				A.employee_id_2,
				A.full_name_2,
				Z.supervisor_id AS supervisor_id_3,
				Z.employee_id AS employee_id_3,
				Z.full_name AS full_name_3
			FROM employee Z, 
			(
				SELECT DISTINCT 
					A.supervisor_id_1,
					A.employee_id_1,
					A.full_name_1,
					Z.supervisor_id AS supervisor_id_2,
					Z.employee_id AS employee_id_2,
					Z.full_name AS full_name_2
				FROM employee Z, 
				(
					SELECT DISTINCT 
						supervisor_id AS supervisor_id_1,
						employee_id AS employee_id_1,
						full_name AS full_name_1
					 FROM employee
					 WHERE supervisor_id = 0 OR supervisor_id IS NULL
				) A
				WHERE A.employee_id_1 = Z.supervisor_id
			) A
			WHERE A.employee_id_2 = Z.supervisor_id
		) A
		WHERE A.employee_id_3 = Z.supervisor_id
	) A
	WHERE A.employee_id_4 = Z.supervisor_id
) A
UNION
SELECT DISTINCT 
	A.employee_id_4 AS employee_id,
	A.supervisor_id_1,
	A.employee_id_1,
	A.full_name_1,
	A.supervisor_id_2,
	A.employee_id_2,
	A.full_name_2,
	A.supervisor_id_3,
	A.employee_id_3,
	A.full_name_3,
	A.supervisor_id_4,
	A.employee_id_4,
	A.full_name_4,
	NULL AS supervisor_id_5,
	NULL AS employee_id_5,
	NULL AS full_name_5,
	NULL AS supervisor_id_6,
	NULL AS employee_id_6,
	NULL AS full_name_6,
	NULL AS supervisor_id_7,
	NULL AS employee_id_7,
	NULL AS full_name_7
FROM employee Z, 
(
	SELECT DISTINCT 
		A.supervisor_id_1,
		A.employee_id_1,
		A.full_name_1,
		A.supervisor_id_2,
		A.employee_id_2,
		A.full_name_2,
		A.supervisor_id_3,
		A.employee_id_3,
		A.full_name_3,
		Z.supervisor_id AS supervisor_id_4,
		Z.employee_id AS employee_id_4,
		Z.full_name AS full_name_4
	FROM employee Z, 
	(
		SELECT DISTINCT 
			A.supervisor_id_1,
			A.employee_id_1,
			A.full_name_1,
			A.supervisor_id_2,
			A.employee_id_2,
			A.full_name_2,
			Z.supervisor_id AS supervisor_id_3,
			Z.employee_id AS employee_id_3,
			Z.full_name AS full_name_3
		FROM employee Z, 
		(
			SELECT DISTINCT 
				A.supervisor_id_1,
				A.employee_id_1,
				A.full_name_1,
				Z.supervisor_id AS supervisor_id_2,
				Z.employee_id AS employee_id_2,
				Z.full_name AS full_name_2
			FROM employee Z, 
			(
				SELECT DISTINCT 
					supervisor_id AS supervisor_id_1,
					employee_id AS employee_id_1,
					full_name AS full_name_1
				 FROM employee
				 WHERE supervisor_id = 0 OR supervisor_id IS NULL
			) A
			WHERE A.employee_id_1 = Z.supervisor_id
		) A
		WHERE A.employee_id_2 = Z.supervisor_id
	) A
	WHERE A.employee_id_3 = Z.supervisor_id
) A
UNION
SELECT DISTINCT 
	A.employee_id_3 AS employee_id,
	A.supervisor_id_1,
	A.employee_id_1,
	A.full_name_1,
	A.supervisor_id_2,
	A.employee_id_2,
	A.full_name_2,
	A.supervisor_id_3,
	A.employee_id_3,
	A.full_name_3,
	NULL AS supervisor_id_4,
	NULL AS employee_id_4,
	NULL AS full_name_4,
	NULL AS supervisor_id_5,
	NULL AS employee_id_5,
	NULL AS full_name_5,
	NULL AS supervisor_id_6,
	NULL AS employee_id_6,
	NULL AS full_name_6,
	NULL AS supervisor_id_7,
	NULL AS employee_id_7,
	NULL AS full_name_7
FROM employee Z, 
(
	SELECT DISTINCT 
		A.supervisor_id_1,
		A.employee_id_1,
		A.full_name_1,
		A.supervisor_id_2,
		A.employee_id_2,
		A.full_name_2,
		Z.supervisor_id AS supervisor_id_3,
		Z.employee_id AS employee_id_3,
		Z.full_name AS full_name_3
	FROM employee Z, 
	(
		SELECT DISTINCT 
			A.supervisor_id_1,
			A.employee_id_1,
			A.full_name_1,
			Z.supervisor_id AS supervisor_id_2,
			Z.employee_id AS employee_id_2,
			Z.full_name AS full_name_2
		FROM employee Z, 
		(
			SELECT DISTINCT 
				supervisor_id AS supervisor_id_1,
				employee_id AS employee_id_1,
				full_name AS full_name_1
			 FROM employee
			 WHERE supervisor_id = 0 OR supervisor_id IS NULL
		) A
		WHERE A.employee_id_1 = Z.supervisor_id
	) A
	WHERE A.employee_id_2 = Z.supervisor_id
) A
UNION
SELECT DISTINCT 
	A.employee_id_2 AS employee_id,
	A.supervisor_id_1,
	A.employee_id_1,
	A.full_name_1,
	A.supervisor_id_2,
	A.employee_id_2,
	A.full_name_2,
	NULL AS supervisor_id_3,
	NULL AS employee_id_3,
	NULL AS full_name_3,
	NULL AS supervisor_id_4,
	NULL AS employee_id_4,
	NULL AS full_name_4,
	NULL AS supervisor_id_5,
	NULL AS employee_id_5,
	NULL AS full_name_5,
	NULL AS supervisor_id_6,
	NULL AS employee_id_6,
	NULL AS full_name_6,
	NULL AS supervisor_id_7,
	NULL AS employee_id_7,
	NULL AS full_name_7
FROM employee Z, 
(
	SELECT DISTINCT 
		A.supervisor_id_1,
		A.employee_id_1,
		A.full_name_1,
		Z.supervisor_id AS supervisor_id_2,
		Z.employee_id AS employee_id_2,
		Z.full_name AS full_name_2
	FROM employee Z, 
	(
		SELECT DISTINCT 
			supervisor_id AS supervisor_id_1,
			employee_id AS employee_id_1,
			full_name AS full_name_1
		 FROM employee
		 WHERE supervisor_id = 0 OR supervisor_id IS NULL
	) A
	WHERE A.employee_id_1 = Z.supervisor_id
) A
UNION
SELECT DISTINCT 
	A.employee_id_1 AS employee_id,
	A.supervisor_id_1,
	A.employee_id_1,
	A.full_name_1,
	NULL AS supervisor_id_2,
	NULL AS employee_id_2,
	NULL AS full_name_2,
	NULL AS supervisor_id_3,
	NULL AS employee_id_3,
	NULL AS full_name_3,
	NULL AS supervisor_id_4,
	NULL AS employee_id_4,
	NULL AS full_name_4,
	NULL AS supervisor_id_5,
	NULL AS employee_id_5,
	NULL AS full_name_5,
	NULL AS supervisor_id_6,
	NULL AS employee_id_6,
	NULL AS full_name_6,
	NULL AS supervisor_id_7,
	NULL AS employee_id_7,
	NULL AS full_name_7
FROM employee Z, 
(
	SELECT DISTINCT 
		supervisor_id AS supervisor_id_1,
		employee_id AS employee_id_1,
		full_name AS full_name_1
	 FROM employee
	 WHERE supervisor_id = 0 OR supervisor_id IS NULL
) A

0 View this answer in context
Not what you were looking for? View more on this topic or Ask a question