Prev Next

How to Define a Custom SQL Fragment

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

Step

Action

See also

1

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

Document Templates Creating a Template Fragment

2

In the Document Template Editor, right-click on the Template background and click on the icon.

The 'Document Options' dialog displays.

3

Select the 'Custom Query' page.

4

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

5

In the main text field on the tab, type the SQL Query that is to be run on your model.

You can use #<macro>#s as string substitutions, as for other SQL Search Queries.

Your 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 reference the ID of the element, diagram or Package currently being processed by the document template, using the #OBJECTID#, #DIAGRAMID# 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}     for .eap (JET) repositories, or {Elements.Hyperlink} for other types of repository

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

     {guid}LABEL

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:

     {123-456-7890}Class2

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 #Concat t_object.ea_guid, t_object.Name#

     AS [BaseClassName-Hyperlink]                      (for .eap files; for other repositories use    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'

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.

It is possible to force a field to be processed as a hyperlink or as formatted notes, by providing a column in this format:

    custom >

     {Name}

     <fieldname>.Formatted      (or <fieldname>.Hyperlink)       (for all repositories, excluding .eap)

     < custom

In your custom SQL Query statement, you must use an alias matching the template field name; for example (in MySQL):

     SELECT ea_guid AS CLASSGUID, Object_Type AS CLASSTYPE, Name, Note as "Note.Formatted" FROM t_object

     Where Object_ID=#OBJECTID#

Create Search Definitions

6

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

7

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

Setting Sections for Reporting

8

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

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

9

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.

10

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.

11

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

Adding Fragments to a Document Template

Notes

  • An Element Filter will not apply to the 'Custom' section in a Template Fragment
  • In your SQL statements you can reference model details using:
         -  #OBJECTID# returns the ID of the element currently being processed by the document template
         -  #PACKAGEID# and #Package# return the ID of the Package currently being
            processed by the document template
         -  #Branch# gets the IDs of the child Packages of the current Package being processed,
            working recursively down to the lowest level of sub-Package; this is only valid when
            processing a Package - elements return a 0 value
         -  #UserName# gets the name of the user logged into version control
         -  #Author# takes the user name from the 'Author' field in the 'General' page of the
            'Preferences' dialog, so the defined search can be performed on objects created
            by that user (this value can be manually re-set in the 'Preferences' dialog)
  • You can test your SQL Query using the SQL Editor in the Model Search window

Learn more