Create a Custom SQL Query

Macro Substrings

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.

The SQL Query can reference the ID of the element, diagram or Package currently being processed by the document template, using the macros:

• #OBJECTID#
• #DIAGRAMID#
• #PACKAGEID#

For example:

• SELECT Count(Object_Type) AS CountOfActors
  FROM t_object
  WHERE Object_Type = "Actor" and Package_ID = #PACKAGEID#

There are a number of other useful macros like:

• #Concat <value1>, <value2>, ...#
• #WC#  Wildcard for your specific Database type.

For more details see the Create Search Definitions Help topic.

Formatted Notes (excluding .eap)

For notes fields, like the Element Notes (t_object.note), the formatting is stored as rich text. It is possible for a note field to be processed from raw data to readable rich text by providing a column in this format:

  {<fieldname>.Formatted}

This is defined in the Custom section in the Template. For example:

• custom >
      {Note.Formatted}
  < 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#

Formatted Notes for .eap repositories

For formatted notes using .eap file repositories the format requires:

{<fieldname>.Formatted} 

For example:

• custom >
       {Note.Formatted}         
  < custom

In your custom SQL Query statement format the field-alias as '[<fieldname>-Formatted]'. For example:

• SELECT ea_guid AS CLASSGUID, Object_Type AS CLASSTYPE, Name, Note AS [Note-Formatted]
  FROM t_object
  Where Object_ID=#OBJECTID#

Hyperlinks (excluding .eap)

You can make column entries into hyperlinks referencing other entries in the generated document. The column header field (in the template text, in the 'Custom' panel) must use the format:

<fieldname>.Hyperlink

For example:

• custom >
         {Type.Hyperlink}
  < custom

The SQL column entries, or values, must have the format:  {guid}<LABEL>

For example:

• SELECT #Concat ea_guid, Name# as "Type.Hyperlink"
  FROM t_object
  WHERE Object_ID=#OBJECTID#;

Note: this uses the #Concat ...# to concatenate the GUID reference and the outward expression of the hyperlink.

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:

     {570CFDAB-00A4-48d9-AE87-9CD6920418C0}Class2

In the generated report this displays as a hyperlink '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"            
  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.

Hyperlinks for.eap repositories

The Hyperlink field name format for .EAP repositories requires:

• custom >
         {<fieldname>.Hyperlink}
  < custom

And the SQL uses: [<fieldname>-Hyperlink]:

• SELECT #Concat t_object.ea_guid, 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'