parent child hierarchy

[Former Member]

Hello all

We have the complete hierarchies stored in one table as parent and child values. Is there any way we can implement the hierarchy in the universe with just parent and child information? I think we need to flatten the hierarchies but correct me if i'm wrong.

Any help would be very much appreciated.

Thanks and regards

hk

[Former Member · Accepted solution]

Hi,

You can effectively flatten the hierarchy and choose a number of fixed levels.

I have samples using derived tables.

Didier

[Former Member · Accepted solution]

hi Didier Mazoue

Thanks for replying. if you could provide me with those sample that would be great.

[Former Member · Accepted solution]

Hi,

Here is a derived table that flatten the H.R. hierarchy on Foodmart SQL Server:

SELECT DISTINCT A.Employee_Id, A.Employee_Name, A.Manager_Id, A.Manager_Name, A.Level_Depth, 	
	MIN(CASE B.Employee_Id
		WHEN A.Employee_Id THEN 'FALSE'
		ELSE 'TRUE'
	END) as Is_Last_Level
	FROM
	(
		SELECT DISTINCT Z.Employee_Id as Employee_Id, 
		full_name AS Employee_Name, 
		CASE Z.Employee_Id 
			WHEN A.Employee_Id THEN A.Manager_Id
			ELSE A.Employee_Id
		END as Manager_Id, 
		CASE Z.Employee_Id 
			WHEN A.Employee_Id THEN A.Manager_Name
			ELSE A.Employee_Name
		END AS Manager_Name,
		CASE Z.Employee_Id
			WHEN A.Employee_Id THEN A.Level_Depth
			ELSE A.Level_Depth + 1
		END as Level_Depth
		FROM Foodmart.dbo.Employee Z,
		(
			SELECT DISTINCT Z.Employee_Id as Employee_Id, 
			full_name AS Employee_Name, 
			CASE Z.Employee_Id 
				WHEN A.Employee_Id THEN A.Manager_Id
				ELSE A.Employee_Id
			END as Manager_Id, 
			CASE Z.Employee_Id 
				WHEN A.Employee_Id THEN A.Manager_Name
				ELSE A.Employee_Name
			END AS Manager_Name,
			CASE Z.Employee_Id
				WHEN A.Employee_Id THEN A.Level_Depth
				ELSE A.Level_Depth + 1
			END as Level_Depth
			FROM Foodmart.dbo.Employee Z,
				(	
				SELECT DISTINCT Z.Employee_Id as Employee_Id, 
				full_name AS Employee_Name, 
				CASE Z.Employee_Id 
					WHEN A.Employee_Id THEN A.Manager_Id
					ELSE A.Employee_Id
				END as Manager_Id, 
				CASE Z.Employee_Id 
					WHEN A.Employee_Id THEN A.Manager_Name
					ELSE A.Employee_Name
				END AS Manager_Name,
				CASE Z.Employee_Id
					WHEN A.Employee_Id THEN A.Level_Depth
					ELSE A.Level_Depth + 1
				END as Level_Depth
				FROM Foodmart.dbo.Employee Z,
				(	
					SELECT DISTINCT Z.Employee_Id as Employee_Id, 
					full_name AS Employee_Name, 
					CASE Z.Employee_Id 
						WHEN A.Employee_Id THEN A.Manager_Id
						ELSE A.Employee_Id
					END as Manager_Id, 
					CASE Z.Employee_Id 
						WHEN A.Employee_Id THEN A.Manager_Name
						ELSE A.Employee_Name
					END AS Manager_Name,
					CASE Z.Employee_Id
						WHEN A.Employee_Id THEN A.Level_Depth
						ELSE A.Level_Depth + 1
					END as Level_Depth
					FROM Foodmart.dbo.Employee Z,
					(	
						SELECT DISTINCT Z.Employee_Id as Employee_Id, 
						full_name AS Employee_Name, 
						CASE Z.Employee_Id 
							WHEN A.Employee_Id THEN A.Manager_Id
							ELSE A.Employee_Id
						END as Manager_Id, 
						CASE Z.Employee_Id 
							WHEN A.Employee_Id THEN A.Manager_Name
							ELSE A.Employee_Name
						END AS Manager_Name,
						CASE Z.Employee_Id
							WHEN A.Employee_Id THEN A.Level_Depth
							ELSE A.Level_Depth + 1
						END as Level_Depth
						FROM Foodmart.dbo.Employee Z,
						(	
							SELECT DISTINCT Z.Employee_Id as Employee_Id, 
							full_name AS Employee_Name, 
							CASE Z.Employee_Id 
								WHEN A.Employee_Id THEN A.Manager_Id
								ELSE A.Employee_Id
							END as Manager_Id, 
							CASE Z.Employee_Id 
								WHEN A.Employee_Id THEN A.Manager_Name
								ELSE A.Employee_Name
							END AS Manager_Name,
							CASE Z.Employee_Id
								WHEN A.Employee_Id THEN A.Level_Depth
								ELSE A.Level_Depth + 1
							END as Level_Depth
							FROM Foodmart.dbo.Employee Z,
							(	
								SELECT DISTINCT Employee_Id, 
								full_name AS Employee_Name, 
								NULL as Manager_Id, 
								NULL AS Manager_Name,
								1 as Level_Depth
								FROM Foodmart.dbo.Employee where supervisor_id = 0
							) A
							WHERE Z.supervisor_id = A.Employee_Id
							OR Z.Employee_Id = A.Employee_Id
						) A
						WHERE Z.supervisor_id = A.Employee_Id
						OR Z.Employee_Id = A.Employee_Id
					) A
					WHERE Z.supervisor_id = A.Employee_Id
					OR Z.Employee_Id = A.Employee_Id
				) A
				WHERE Z.supervisor_id = A.Employee_Id
				OR Z.Employee_Id = A.Employee_Id
			) A
			WHERE Z.supervisor_id = A.Employee_Id
			OR Z.Employee_Id = A.Employee_Id
		) A
		WHERE Z.supervisor_id = A.Employee_Id
		OR Z.Employee_Id = A.Employee_Id
	) A,
	(SELECT distinct Employee_Id FROM Foodmart.dbo.Employee where Employee_Id IN (SELECT DISTINCT supervisor_id FROM Foodmart.dbo.Employee)) B
GROUP BY A.Employee_Id, A.Employee_Name, A.Manager_Id, A.Manager_Name, A.Level_Depth
.

Hope this could help.

Regards,

Didier

[Former Member]

Crystal can consume the hierarchy directly with the parent - child links. Web Intelligence does not offer that feature, but there are choices other than flattening that you can consider. I wrote about several different ways to handle hierarchies and did a presentation at the Business Objects user conference last year.

All of my past conference presentations are here: http://www.dagira.com/conference-presentations/

Look for the one named "Universe Models for Recursive Data." I have detailed many of the solutions as blog posts as well, but have not completed them all. Even so, the presentation would hopefully give you some ideas.

[Former Member]

hi Dave

thanks for replying i went through the pdf and understood the other ways of implementing the hierarchy.

My requirement needs to have effective drilling from the top to the lowest leaf of the hierarchy. As i understand by the material flattening seems to be the option i can have for that purpose ( please correct me if i'm wrong).

[Former Member]

Flattening is the easiest choice for using the native or natural drilling feature of Web Intelligence, yes. But you can "drill" using the OpenDocument function on the tree structure quite nicely too.