Sparx Systems Forum

Enterprise Architect => Automation Interface, Add-Ins and Tools => Topic started by: pbank on March 22, 2018, 12:51:54 am

Title: [SOLVED] How to parametrize custom SQL queries in document fragments?
Post by: pbank 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:

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?

Title: Re: How to parametrize custom SQL queries in document fragments?
Post by: Geert Bellekens on March 22, 2018, 01:13:16 am

Quote from: pbank on March 22, 2018, 12:51:54 am

(I know about #PACKAGEID# and #OBJECTID# but those seem to be discarded if the Fragment is part of a Template for a Model Document)

I don't think that statement is correct. I use #PACKAGEID#, #OBJECTID# and #DIAGRAMID# all the time in SQL fragments, and I always use master/model document for code generation.

Geert

Title: Re: How to parametrize custom SQL queries in document fragments?
Post by: pbank on March 22, 2018, 01:41:11 am

Quote from: Geert Bellekens on March 22, 2018, 01:13:16 am

I don't think that statement is correct. I use #PACKAGEID#, #OBJECTID# and #DIAGRAMID# all the time in SQL fragments, and I always use master/model document for code generation.
Geert

Well, I don't know what I'm doing wrong then. I added the package ID to SQL query accordingly:
(https://i.imgur.com/EAnQyvk.png)

This is the content of the fragment:
(https://i.imgur.com/BzJrjFm.png)

The template basically justs refers to it:
(https://i.imgur.com/waHvorm.png)

All risk mitigation measures are located as requirements in a package in the model:
(https://i.imgur.com/YBAqAnK.png)

If I select this package as shown in the image above and generate a document from the template directly using the (https://i.imgur.com/uYLPmeS.png) button, this works as expected.

But if I have a Model Document which uses the template and the package
(https://i.imgur.com/SBeSDkE.png)
and I select the Model Document, press F8 and run a report, the result stays empty.
The same applies if I generate the report from the Master Document.

?  :-\

Title: Re: How to parametrize custom SQL queries in document fragments?
Post by: pbank on March 22, 2018, 01:48:30 am

I might note that the Fragment and the Template both are named "KLS_RiskMitigationCoverageTemplate".
I don't guess this should be a problem but I just renamed the template to avoid any trouble from coming from that.
It doensn't change anything though.

Title: Re: How to parametrize custom SQL queries in document fragments?
Post by: pbank on March 22, 2018, 02:06:19 am

Ok, consider this as solved.
I gave the template and the fragment different names - which however did not solve the issue at first.
But after I restarted EA, it magically worked like it should!
I will note for myself to restart EA the next time before fiddling with something which does not work like it should...


So in conclusion, I might not parametrize the "RMM*" conversion string in the SQL query, but since all affected requirements are located in a distinct package, using #PACKAGEID# will do the same job for me.

Geert, thank you very much for your answer! It helped me to keep following the right approach on this.  :)

Title: Re: [SOLVED] How to parametrize custom SQL queries in document fragments?
Post by: Geert Bellekens on March 22, 2018, 03:03:12 am

You're welcome, glad I could help. :)

Geert