How to add Enumeration Table under corresponding Attributes in Custom Documents in Sparx Enterprise Architect?

Question

In Sparx Enterprise Architect I am trying to document a class model containing classes and attributes. Some attribute have a simple data type (e.g. Flag) and some have an enumeration type. All types (simple and enumeration) are modelled in separate classes. The model is stored in a single file with .qea extension ("repository as a file"), which is a SQLite database. Below the example of a class "Holiday" with several attributes. For example, Attribute "isDeferred" has a simple data type "FLAG". "holidayDurationType" has data type "DAYDURATIONTYPE" that is an enumeration. Both data types are described in a separate class as shown below.

My goal is to generate a document that lists the following:

Class A
Attribute A1
Type A1 (simple data type or enum)
Attribute A2
Type A2 (simple data type or enum)
...

Here is a PDF example of the document I try to generate:

(1) If the type is a simple data type, I only need the name (e.g. FLAG) of the data type class (see Attribute "isDeferred" above).

(2) If the type is an enumeration, I need the name, notes and enumerations (= attributes) of the data type class. The enumerations shall be displayed in a two column table. Column 1 shows the enumeration code (1,2...) and column 2 shows the notes (see Attribute "holidayDurationType" above).

To solve this I created a Custom Document Template that includes a Custom Template Fragment containing a Custom SQL query.

Custom Document Template "UserManual" Edit: Thanks to @Gert Bellekens' answer, the custom template code has been updated to provide the whole attribute section as a template fragment in the element > section.

package >
element >
   Class "{Element.Name}"
   {Element.Notes}
   {Template - Attribute_and_DataType}
< element
< package

Custom Template Fragment "Attribute_and_DataType"
Edit: Thanks to @Gert Bellekens' answer, the custom fragment code has been updated to include the whole attribute and data type information in a custom template fragment.

custom >
   Attribute "{AttName}"
             {AttDesc.Formatted}

             Domain: {AttType}
             {TypeDesc.Formatted}
             {Enums.Formatted}
< custom

SQL query in Template Fragment Options\Custom Query\Custom SQL Edit: Thanks to @Gert Bellekens' answer, the custom SQL code in the custom template fragment has been enriched to show all enumeration values and their description for each attribute that has an enumeration data type.

select 
  a.Name as 'AttName', 
  a.Notes as 'AttDesc.Formatted', 
  a.Type as 'AttType', 
  e.Note as 'TypeDesc.Formatted',
  (select group_concat(CAST(ev.[Default] + ':   ' + coalesce(ev.Notes, 
  '') as text), char(10)) 
      from t_object e 
      inner join t_attribute ev on ev.Object_ID = e.Object_ID
      where e.Object_ID = a.Classifier
      and e.Object_Type = 'Enumeration'
      ) as 'Enums.Formatted'
from 
  t_attribute a
inner join 
  t_object e on a.Classifier = e.Object_ID
where 
  a.Object_ID = #OBJECTID#

The result I receive when I drag and drop a Class (e.g. "Holiday") into a document and apply the Custom Template "UserManual", looks as follows.

Edit: Thanks to @Geert Bellekens the document generation works now and produces all the information needed. However, I do not manage to bring the enumerations (code and notes) into a table like in the PDF example above.

I tried the following:

1. Added a table to the template fragment and moved Enums.Formatted in the table field.

Template fragment

The result looks as follows:

There are two problems with this approach.
(1) an empty table is generated for all attributes with simple data type (e.g. FLAG).
(2) There is, of course, no separate column and row for each enumeration value/description as the SQL query produced just one text (group_concat, CAST(..., as text)) with line breaks (char10).

Is there a way to adjust the SQL query so that enumeration value and description go to separate columns and rows (e.g. markup tags in SQL query so that EA will render it as a 2 column table)? Any ideas are much appreciated.

2. Adjusted the SQL query and template fragment:

SQL query

select
    a.Name as 'AttName', 
    a.Notes as 'AttDesc.Formatted', 
    a.Type as 'AttType', 
    e.Note as 'TypeDesc.Formatted', 
    ev.[Default] as 'EnumCode',
    ev.Notes as 'EnumDesc.Formatted'
from 
    t_attribute a
inner join 
    t_object e on a.Classifier = e.Object_ID
left join 
    t_attribute ev on ev.Object_ID = e.Object_ID
where 
    a.Object_ID = #OBJECTID#

Template fragment

The result looks as follows:

There are two problems with this approach:
(1) empty table for attributes with simple data type.
(2) each enumeration value/description is in a separate table in a duplicate attribute section.

Any ideas how get it solved with this approach?

3. Had a look at the Sparx EA Template Selector. I was wondering if I could create two template fragments, one for the simple data types (no table) and one for the enumeration data types (with table).
   The problem here to my understanding is that the template selector fragment takes the type and stereotype of the selected element to decide which template will be applied. As the type and stereotype of the element (Class) I drag and drop into the document is always "Class"/"EMLClassObj", there is no differntiation possible.

Any ideas how to solve it with a Template Selector?

Or am I completely on the wrong way and need to start with a different approach (like custom scripts)?

Answer 1

You can't add template fragments under an attribute> section as they can only accept ObjectID, PackageID or DiagramID as a parameter.

The alternative is to provide the whole attributes section as a template fragment and add that under the element> section.

The difficulty here is that you want to show all possible enumeration value, and their description, for each attribute. That makes the query a bit more complex as you'll have to roll-up the enumeration values and notes into a single row.

A possible query could be

select a.Name, a.Notes as 'Description.Formatted'
,(select group_concat(CAST(ev.Name + ': ' + coalesce(ev.Notes, '') as text), char(10)) 
    from t_object e 
    inner join t_attribute ev on ev.Object_ID = e.Object_ID
    where e.Object_ID = a.Classifier
    and e.Object_Type = 'Enumeration'
    ) as 'enums.Formatted'
from t_attribute a
where a.Object_ID = #OBJECTID#

You need the .Formatted to make sure the document generation interprets the markup in the notes. The CAST as text is needed to avoid truncation when you have a lot of enum values.