Template to document changes to be made to multiple systems

[Modesto Vega]

We have a diagram describing the context of a programme impacting various systems. We would like to use the Change Management functionality, specifically the Changes functionality (see Construct ribbon, Change management), and the Project Management functionality, specifically Decisions (see construct ribbon Project Management) to document the changes and decisions to each element in the diagram.

Ideally, we want to generate a document with a table documenting the scope as follows.

----------------------------------------------------------------------------------------------------------------
|Scope Item |Scope Item Type |Scope Item Description |In Scope? |Phase |Change |
----------------------------------------------------------------------------------------------------------------

I got a couple of questions about this:
1) Is there a way to generate such a table without using a fragment with a custom query?
2) How do we work around the absence of a current diagram macro?

P.S.: The elements shown in the diagram have different homes - i.e., they are in different packages.

[Geert Bellekens]

I got a couple of questions about this:
1) Is there a way to generate such a table without using a fragment with a custom query?

Probably not

2) How do we work around the absence of a current diagram macro?

The only way I know is to make sure you only have a single diagram of a specific type per package or object.
You can filter the template on diagram type as to exclude diagrams of other types.

Geert

[Modesto Vega]

V16.1 keeps throwing the following error "Error Processing XML document: invalid or illegal XML character" when attempting to generate a document using a fragment with a custom query as described above. I am obviously missing/forgetting something but do not know what is it that I missed/forgot.

[Geert Bellekens]

Probaly an "error" in your query.

Might be that you didn't provide an alias (e.g. select o.name + ' ' + o.alias from t_object o)

EA will convert try to convert the query results into an xml file, but that conversion isn't very smart and will fail if you provide the "wrong" query.

Geert

[Modesto Vega]

Thanks Geert. The only "erroneous" part of the query I can think of is the way I have aliased the fields (e.g., select o.name [Scope Item], o.notes [Scope Item Description] from t_object o). I'll try removing the spaces and using quotes tomorrow.

A problem I know we will have is that the query has 2 fields that I would like to alias as Note.Formatted but I think Sparx EA only accepts one.

[Geert Bellekens]

Thanks Geert. The only "erroneous" part of the query I can think of is the way I have aliased the fields (e.g., select o.name [Scope Item], o.notes [Scope Item Description] from t_object o). I'll try removing the spaces and using quotes tomorrow.

A problem I know we will have is that the query has 2 fields that I would like to alias as Note.Formatted but I think Sparx EA only accepts one.

Yeah, the spaces might be an issue. Best be safe with something like select o.name as ScopeItem, o.note as ScopeItemDescription
Why would you want give two fields the same name? That doesn't make any sense.

Geert

[Modesto Vega]

Thanks Geert, changes the aliases makes the "Error Processing XML document" message disappear. The next problem is that the template returns the entire repository not just the elements in the selected diagram. In fact, Sparx EA appears to ignore even a hardcoded and diag.ea_guid = '<Diagram GUI>'.

Why would you want give two fields the same name? That doesn't make any sense.

I may not have explained this very well. The query returns 2 formatted fields, the Notes field from t_object, and the Problem from t_objectproblems

[Geert Bellekens]

You can have as many formatted fiels as you want, as long as they have a different name.

A hardcoded guid will nog be ignored. Maybe you forgot a join?

To make it work for your template you'll have to use the package or object ID that is the parent of the diagram; alongside maybe the diagram type (if you have multiple diagrams underneatch the package or object)

If you post your actual query we might be able to spot any issues

Geert

[Modesto Vega]

The following query works fine, note the quotes around ScopeItemDefinition.Formatted and ProblemDescription.Formatted.

The hardcoded part of the SQL- i.e., and diag.ea_guid = '<DiagramGUID>' - was in the wrong join. Ideally, I would like to replace the hardcoded part of the SQL with something more dynamic. I will try your suggestion parent package suggestion.

Code: [Select]

select
 diagObjs.Sequence
,obj.Object_ID ScopeItemID
,obj.ea_guid ScopeItemGUID
,obj.Object_Type
,obj.Stereotype ScopeItemType
,obj.Name ScopeItem
,obj.Note "ScopeItemDefinition.Formatted"
,case obj.Phase
when '0.0' then 'Out of Scope'
when '0.1' then 'Impacted'
else 'In Scope'
end InScope
,obj.Phase
,prObjs.ProblemType
,prObjs.Problem "ProblemDescription.Formatted"
from t_object obj
inner join t_diagramobjects diagObjs
on obj.Object_ID = diagObjs.Object_ID
and obj.Object_Type <> 'Text'
inner join t_diagram diag
on diagObjs.Diagram_ID = diag.Diagram_ID
and diag.ea_guid = '<DiagramGUID>'
left outer join [dbo].[t_objectproblems] prObjs
on obj.Object_ID = prObjs.Object_ID
order by diagObjs.Sequence

[Modesto Vega]

This also works, although Sparx EA is stubbornly returning the wrong diagObjs.Sequence; it almost seems to be caching the old numbers.

Code: [Select]

select
 diagObjs.Sequence
,obj.Object_ID ScopeItemID
,obj.ea_guid ScopeItemGUID
,obj.Object_Type
,obj.Stereotype ScopeItemType
,obj.Name ScopeItem
,obj.Note "ScopeItemDefinition.Formatted"
,case obj.Phase
when '0.0' then 'Out of Scope'
when '0.1' then 'Impacted'
else 'In Scope'
end InScope
,obj.Phase
,prObjs.ProblemType
,prObjs.Problem "ProblemDescription.Formatted"
from t_object obj
inner join t_diagramobjects diagObjs
on obj.Object_ID = diagObjs.Object_ID
and obj.Object_Type <> 'Text'
inner join t_diagram diag
on diagObjs.Diagram_ID = diag.Diagram_ID
and diag.package_Id = #PACKAGEID#
left outer join [dbo].[t_objectproblems] prObjs
on obj.Object_ID = prObjs.Object_ID
order by diagObjs.Sequence

[Modesto Vega]

Without a current diagram macro, what we are trying to achieve does not seem to be possible. Having found a 2016 thread about the same subject, I am a bit puzzled (but not surprised) about Sparx Systems resistance to introduce this feature.

The query below, with a corrected where clause (and diag.package_Id = #PACKAGE# instead of #PACKAGEID#), does not exactly document the elements in the only diagram of the only type included for document generation. It also appears to do 2 unwanted things:
1) document any other elements within the package, and
2) more or less randomly pick a diagram to return diagObjs.Sequence ignoring the diagram type included for document generation. In other words, if I have 2 diagrams of different types - e.g., A and B - in the same package and document generation excludes type B the query still returns diagObjs.Sequence for the excluded diagram and does not return diagObjs.Sequence for the included diagram.

The only way to fully meet our documentation requirement, list the elements in the right order, is to have only one diagram per package. I am not sure Geert meant this when he wrote:

[SNIP]
The only way I know is to make sure you only have a single diagram of a specific type per package or object.
You can filter the template on diagram type as to exclude diagrams of other types.

Code: [Select]

select
 diagObjs.Sequence
,obj.Object_ID ScopeItemID
,obj.ea_guid ScopeItemGUID
,obj.Object_Type
,obj.Stereotype ScopeItemType
,obj.Name ScopeItem
,obj.Note "ScopeItemDefinition.Formatted"
,case obj.Phase
when '0.0' then 'Out of Scope'
when '0.1' then 'Impacted'
else 'In Scope'
end InScope
,obj.Phase
,prObjs.ProblemType
,prObjs.Problem "ProblemDescription.Formatted"
from t_object obj
inner join t_diagramobjects diagObjs
on obj.Object_ID = diagObjs.Object_ID
and obj.Object_Type <> 'Text'
inner join t_diagram diag
on diagObjs.Diagram_ID = diag.Diagram_ID
and diag.package_Id = #PACKAGE#
left outer join [dbo].[t_objectproblems] prObjs
on obj.Object_ID = prObjs.Object_ID
order by diagObjs.Sequence

[Modesto Vega]

Just an update on this diag.package_Id = #PACKAGEID# is the correct code.

Sparx is of course consistently /i] inconsistent, this help page tells you to use #Package# (https://sparxsystems.com/enterprise_architect_user_guide/16.1/the_application_desktop/creating_filters.html) and this one tells you to use #PACKAGEID# (https://sparxsystems.com/enterprise_architect_user_guide/16.1/model_publishing/create_a_custom_sql_query.html). The 2nd page also tells you that #DIAGRAMID# is also an option but it does not work on Template Fragment.

The 1st page appears to apply to searches while the 2nd page appears to apply to custom SQL Queries. Love the consitency.

[Geert Bellekens]

The only way to fully meet our documentation requirement, list the elements in the right order, is to have only one diagram per package. I am not sure Geert meant this when he wrote:

[SNIP]
The only way I know is to make sure you only have a single diagram of a specific type per package or object.
You can filter the template on diagram type as to exclude diagrams of other types.

I did, except that you can also choose to use the Element section, and have one diagram underneath an element instead of the package.

And that you can have multiple diagrams, as long as you only have a single diagram of a specific type (e.g. Class Diagram) underneath your package or element.

In that case you would have to add a filter for the right diagram type on both your template as your SQL Query.

Geert

[Modesto Vega]

Thanks Geert, I think this (my emphasis) is the missing piece in the puzzle. The query does not include the diagram type only the template.

[Snip]
You would have to add a filter for the right diagram type on both your template as your SQL Query.