Custom SQL Fragments

You can create a Template Fragment to return data from an SQL Query. You do this by defining an SQL Query in the Document Options of the Template Fragment, then adding fields within the Custom section of your Template Fragment that refer to the columns returned by the SQL Query. The Query is DBMS dependent and so might vary according to the DBMS you are using.

Create an SQL Query Fragment



See also


Open or create a Template Fragment in the Document Template Editor.

Document Templates

Creating a Template Fragment



In the Document Template Editor, right-click on the Template background and select File | Document Options.

The Document Options dialog displays.




Select the Custom Query tab.




In the Template Fragment type panel, select the Custom SQL radio button.




In the main text field on the tab, type the SQL Query that is to be run on your model. This Query must return one or more columns; for example:


SELECT DocName AS TemplateName, Author AS TemplateLocation

FROM t_document

WHERE DocType = 'SSDOCSTYLE' and (IsActive = 1)

The SQL Query can also reference the ID of the element or Package currently being processed by the document template, using the #OBJECTID# or #PACKAGEID# macros; for example:


SELECT Count(Object_Type) AS CountOfActors

FROM t_object

WHERE Object_Type = "Actor" and Package_ID = #PACKAGEID#



You can also make your column entries into hyperlinks to the element entries in the generated document. The column header field (in the template text itself, in the Custom panel) must contain the suffix -Hyperlink; for example:


Elements-Hyperlink      (Elements.Hyperlink is also valid for non-Jet repositories)


The column entries, or values, must have the format:




In this format, {guid} is the GUID of the element to link to and LABEL is the hyperlink text (such as the element name), either manually inserted or returned from another command. For example:




In the generated report this displays as Class2, which links to the description of Class2 in the document.


This example returns the hyperlinked name of each base (parent) Class of the element currently being reported:


SELECT t_object.ea_guid CONCAT t_object.Name
AS [BaseClassName-Hyperlink]

FROM t_object, t_connector

WHERE t_connector.Start_Object_ID = #OBJECTID#
AND t_object.Object_ID = t_connector.End_Object_ID

AND t_connector.Connector_Type = 'Generalization'


(For Access repositories, replace CONCAT with &. For SQL repositories the correct syntax is:

CONCAT (t_object.ea_guid,t_object.Name))


This query could return multiple entries, in which case the entries are reported one per line.

You can, if required, have multiple Hyperlink query statements, separated by commas.

You can hyperlink to reported elements, attributes, operations, diagrams and Packages.




Click on the OK button to close the Document Options dialog.




In the Document Template Editor, in the Sections panel, select the Custom checkbox to generate the Custom section.


Setting Sections for Reporting


Within the Custom section, right-click and select the Insert Custom Field context menu option.

A prompt displays for the name of the field to create.




In response to the prompt, type the name of the column that is being returned by your Query; for example, CountOfActors.

Click on the OK button.




Repeat steps 8 and 9 for each column that you want to include in your report.

Add any other formatting and content you need, to the Template Fragment.




Save the Template Fragment, and add it to a normal document template.

Adding Fragments to a Document Template



·An Element Filter will not apply to the Custom section in a Template Fragment
·You can test your SQL Query using the SQL Editor in the Model Search window

Learn more

Learning Center topics

·Alt+F1 | Enterprise Architect | Reporting | RTF Template Fragments |
·Create a SQL based Fragment 1
·Create a SQL based Fragment 2