Show Posts
1
Automation Interface, Add-Ins and Tools / [SOLVED] How to parametrize custom SQL queries in document fragments?
« on: March 22, 2018, 12:51:54 am »
Hello there,
tl;dr:
I like to have a comparison string within a custom SQL query of a Fragment to be customizable.
(I know about #PACKAGEID# and #OBJECTID# but those seem to be discarded if the Fragment is part of a Template for a Model Document)
Long story:
In our project we have requirements and risk mitigation measures (RMM) which are in the end both requirement elements in the EA model.
Both, ordinary requirements and risk mitigation measures are 'realized' by creating a realization relationship from a component and then changing the status of the requirement element to 'realized' (a proprietary status value I've added to the project types).
In my report document I need to have a table of all risk mitigation measures including the respective realizing components for each mitigation measure.
Like this:
The report document is generated using a Master Document which holds a set of various Model Documents.
My first approach was to define an own search which finds all t_objects of type 'Requirement' and where the name is like 'RMM*', which is passed as the SearchValue and then have a template which gives information about all connectors of the thereby found elements.
But since the risk mitigation measure elements might as well have additional trace associations, dependencies and lot of other relationships, this did not work as desired.
So my current approach is to have a Document Fragment with a custom SQL query like
SELECT t_object.ea_guid AS CLASSGUID, t_object.Object_Type AS CLASSTYPE, t_object.Name as sqlRMM, o.Object_Type as sqlTYPE, o.Name as sqlREALIZEYBY, sPackage.Name as sqlFROM, gPackage.Name as sqlIN
FROM ((((t_Object
inner join t_Connector as c on c.End_Object_ID = t_object.Object_ID)
inner join t_Object as o on o.Object_ID = c.Start_Object_ID)
INNER JOIN t_Package as sPackage ON o.Package_ID = sPackage.Package_ID)
INNER JOIN t_Package as gPackage ON sPackage.Parent_ID = gPackage.Package_ID)
where t_object.Object_Type = 'Requirement' and t_object.status like 'Realized' and t_object.Name like 'RMM*' and c.Connector_Type = 'Realisation'
order by t_object.Name
This searches for all requirements with a name like 'RMM*' and outputs the names of the corresponding realizing objects.
The fragment is referenced in a document template
This approach works quite well, but the problem is, that the prefix 'RMM' does not apply to all of our projects and the whole EA model shall become a basis for a company wide architecture template.
So I need to have the 'RMM*' expression to be customizable somewhere without having my co-workers to deal with complicated SQL queries deep down in some fragments.
Any ideas how to achieve this?
tl;dr:
I like to have a comparison string within a custom SQL query of a Fragment to be customizable.
(I know about #PACKAGEID# and #OBJECTID# but those seem to be discarded if the Fragment is part of a Template for a Model Document)
Long story:
In our project we have requirements and risk mitigation measures (RMM) which are in the end both requirement elements in the EA model.
Both, ordinary requirements and risk mitigation measures are 'realized' by creating a realization relationship from a component and then changing the status of the requirement element to 'realized' (a proprietary status value I've added to the project types).
In my report document I need to have a table of all risk mitigation measures including the respective realizing components for each mitigation measure.
Like this:
| Risk mitigation measure | is realized by |
| RMM-100001 Supervise motor speed | Component Control CPU Firmware |
The report document is generated using a Master Document which holds a set of various Model Documents.
My first approach was to define an own search which finds all t_objects of type 'Requirement' and where the name is like 'RMM*', which is passed as the SearchValue and then have a template which gives information about all connectors of the thereby found elements.
But since the risk mitigation measure elements might as well have additional trace associations, dependencies and lot of other relationships, this did not work as desired.
So my current approach is to have a Document Fragment with a custom SQL query like
SELECT t_object.ea_guid AS CLASSGUID, t_object.Object_Type AS CLASSTYPE, t_object.Name as sqlRMM, o.Object_Type as sqlTYPE, o.Name as sqlREALIZEYBY, sPackage.Name as sqlFROM, gPackage.Name as sqlIN
FROM ((((t_Object
inner join t_Connector as c on c.End_Object_ID = t_object.Object_ID)
inner join t_Object as o on o.Object_ID = c.Start_Object_ID)
INNER JOIN t_Package as sPackage ON o.Package_ID = sPackage.Package_ID)
INNER JOIN t_Package as gPackage ON sPackage.Parent_ID = gPackage.Package_ID)
where t_object.Object_Type = 'Requirement' and t_object.status like 'Realized' and t_object.Name like 'RMM*' and c.Connector_Type = 'Realisation'
order by t_object.Name
This searches for all requirements with a name like 'RMM*' and outputs the names of the corresponding realizing objects.
The fragment is referenced in a document template
Code: [Select]
package>
{Template - RiskMitigationCoverageTemplate}
<packageand the document template is the template for a Model Document of the Master Document.This approach works quite well, but the problem is, that the prefix 'RMM' does not apply to all of our projects and the whole EA model shall become a basis for a company wide architecture template.
So I need to have the 'RMM*' expression to be customizable somewhere without having my co-workers to deal with complicated SQL queries deep down in some fragments.
Any ideas how to achieve this?