When working with Sparx EA I often want to generate a report to discuss a set of Elements in the context of their relationship to other elements.
I’ve made the following query that returns:
- Connected Elements: to the current element (current <- other)
- Connected Elements: from the current element (current -> other)
- Child Elements: directly under the current element in the project browser, not grandchildren etc.
- Child Elements: identified in the current element’s child diagram(s). E.g. the current element is a process element with a process diagram underneath it, all activities in that process diagram would be listed.
- Parent Element: direct parent of the current element
- Parent Element: indirect, the current element appears in a diagram that is a child diagram of the parent element.
- In the notes of the current element as hyperlinks.
SQL Statement
Uses the ORACLE function LISTAGG to create a list of hyperlinks
SELECT ElementType, LISTAGG(Alias_Formated,', ') WITHIN GROUP (ORDER BY Alias) ["List.Formatted"] FROM ( SELECT UNIQUE t_object.Alias, t_object.ea_guid, t_object.Stereotype, t_object.Object_ID, SUBSTR(t_object.Alias,0,INSTR(t_object.Alias,'-')-1) AS ElementType, CONCAT('<a href="$element://', CONCAT(t_object.ea_guid,CONCAT('"><font color="#0000ff"><u>', CONCAT(t_object.Alias,'</u></font></a>')))) AS Alias_Formated FROM t_object WHERE t_object.Object_ID IN ( SELECT t_connector.Start_Object_ID AS Object_ID FROM t_connector WHERE t_connector.End_Object_ID = #OBJECTID# UNION ALL SELECT t_connector.End_Object_ID AS Object_ID FROM t_connector WHERE t_connector.Start_Object_ID = #OBJECTID# UNION ALL SELECT t_object.Object_ID FROM t_OBJECT WHERE t_object.ParentID = #OBJECTID# UNION ALL SELECT t_diagramobjects.Object_ID FROM t_diagramobjects INNER JOIN t_diagram ON t_diagramobjects.Diagram_ID = t_diagram.Diagram_ID WHERE t_diagram.ParentID = #OBJECTID# UNION ALL SELECT t_object.Object_ID FROM t_object qParent INNER JOIN t_object ON qParent.Object_ID = t_object.ParentID WHERE t_object.Object_ID = #OBJECTID# UNION ALL SELECT t_object.Object_ID FROM t_object INNER JOIN t_diagram ON t_diagram.ParentID = t_object.Object_ID INNER JOIN t_diagramobjects ON t_diagramobjects.Diagram_ID = t_diagram.Diagram_ID WHERE t_diagramobjects.Object_ID = #OBJECTID# UNION ALL SELECT t_object.Object_ID FROM t_object WHERE t_object.ea_guid IN ( SELECT to_char(REGEXP_SUBSTR(partial_str, '\{(.*?)\}', 1, 1)) AS found_GUID FROM ( SELECT REGEXP_SUBSTR(str, '[^\$]+', 1, LEVEL) AS partial_str FROM (SELECT t_object.Note AS str FROM t_object WHERE t_object.Object_ID = #OBJECTID#) CONNECT BY LEVEL <= LENGTH(REGEXP_REPLACE(str, '[^\$]+')) + 1 ) WHERE LENGTH(partial_str) > 0 ) ) ORDER BY ElementType ) GROUP BY ElementType
Result
The result in my report is a table of the “related” elements grouped by element type. The hyperlinks use Sparx EA bookmarks in the document to link to the details about that element. Currently debating if this should be updated to link to our WebEA server.
bACTV | bACTV-SE0002, bACTV-SE0003, bACTV-SE0035, bACTV-SE0036, bACTV-SE0039, bACTV-SE0281, bACTV-SE0482 |
bEVNT | bEVNT-0093, bEVNT-0094, bEVNT-SE0002, bEVNT-SE0028, bEVNT-SE0048, bEVNT-SE0104 |
bFUNC | bFUNC-SE0001 |
bOBJ | bOBJ-D0003, bOBJ-D0011, bOBJ-D0112 |
bSRVC | bSRVC-0002 |
shREQ | shREQ-D0002, shREQ-D0125, shREQ-D0126 |