I had a similar requirement.
I don't know how to do it in EA, but here is some SQL trickery to do the same thing.
In this example - the "MY top package" is the top level package you want to retrieve the details for.
This script should handle any number of levels. I've tested with 4 fine.
The BIG limitation - is it assumes the package IDs are numbered in depth first order, ie:
100 Package 1
101 Child 1.1
102 Child 1.2
103 Child 1.2.1
104 Package 2
105 Package 3
106 Child 3.1
etc....
with some order by clauses - you might be able to get it the way you want
(The reason mine where in correct numerical order is I did a bulk CSV import then created the missing t_package rows in one operation)
=-=-=-=-=
/* This creates a table to return the MY hierarchy in a nested view, with levels and descriptions */
drop table MY_hierarchy
create table dbo.MY_hierarchy
(
object_id int primary key clustered,
parent_object_id int null,
depth int null,
hierarchy varchar(255) null
)
/* Seed the table with the top level MY parent */
insert into MY_hierarchy (object_id, parent_object_id, depth, hierarchy)
select object_id, null, 0, '/' from t_object where object_type = 'Package' and name = 'MY top package'
/* Populate the MY_hierarchy table with the child and parent object_id of each MY before populating the depth
and level information. The object_type = 'Package' stops picking up hyperlinks in diagrams which have a pdata1
value of the package (diagram) they link to, which causes a duplicate PK on insert of the child */
insert into MY_hierarchy (object_id, parent_object_id)
select c.object_id, p.object_id
from t_object as c, t_object as p
where c.object_type = 'Package' and c.stereotype = 'MY'
and ltrim(str(c.package_id)) = p.pdata1
and p.object_type = 'Package'
/* for each MY - create its depth and hierarchy of parent IDs */
WHILE EXISTS (SELECT * FROM MY_hierarchy WHERE Depth Is Null)
UPDATE T SET T.depth = P.Depth + 1,
T.hierarchy = P.hierarchy + Ltrim(Str(T.parent_object_id,6,0)) + '/'
FROM MY_hierarchy AS T, MY_hierarchy as P
WHERE T.parent_object_id = P.object_id
AND P.Depth>=0
AND P.hierarchy Is Not Null
AND T.Depth Is Null
/* Then return the nested MY tree */
SELECT Space(a.Depth*16) + b.Name AS MY, a.depth as "Level", b.note as "Description"
FROM MY_hierarchy as a, t_object as b
where b.object_id=a.object_id
ORDER BY a.hierarchy + Ltrim(Str(a.object_id,6,0))
=-=-=-=-=
Then finally, you can create a view to display the hierarchy which users can dump into MS Access, excel
/* Note: Run the Create table to retrieve MY hierarchy.sql script first
to create and populate the required table */
create view dbo.MY_Hierarchy_View
as
SELECT top 500 case depth when 1 then b.name else '' end as "MY_level_1",
case depth when 2 then b.name else '' end as "MY_level_2",
case depth when 3 then b.name else '' end as "MY_level_3",
case depth when 4 then b.name else '' end as "MY_level_4",
a.depth as "Level", b.note as "Description", b.author as "Architect_Responsible"
FROM MY_hierarchy as a with (NOLOCK), t_object as b with (NOLOCK)
where b.object_id=a.object_id
and depth >= 1
ORDER BY a.hierarchy + Ltrim(Str(a.object_id,6,0))
(credit - the above is courtesy of
www.sqlteam.com)
Cheers,
David.
