Create table from doc into EA in sysml then recreate same through Doc Generation

[EugeneP]

Hello all,

I am populating an existing table into EA using sysml with the desire to generate the same table back in an EA generated document. My Table has multiple rows and 7 columns. I inputted the data into EA using the table name as the block, and contents as attributes. As the number of fields for attributes is limited, I used tags on the attribute to enter data that needed different fields than those provided. I am having trouble with the doc generation. The standard template does not let me create a table with tagged value fields in the same table as attribute fields. I created a fragment with a SQL query to join t_attribute and t_attributetag and placed the fragment within a table between attribute> <attribute, but that made the table fail. I can create the full table in the fragment, but the attribute elements repeat for every tag and it makes the table unusable as a deliverable.  I've considered using blocks with containment connectors for elements that are currently attributes enabling me to use current tagged values as attributes of that block, but the problem seems to be the same - table generation in a document/report that uses different level of elements in the same table, whether its attribute/tagged values or objects/attributes.

Is there a different structure I should consider? Is there a way to group tagged values such that I get one row of attribute values (thinking one merged row) for as many rows of tagged values as are associated with that attribute (to prevent attribute values from repeating for every row of tagged values)? Any other suggestions?

Here's my fragment query:

select att.Name, att.Type, att.[Default] as 'Value', t_attributetag.Property as TagName, t_attributetag.VALUE as TagValue, att.Notes as Notes

From t_attribute att

left join t_attributetag
   on att.ID=t_attributetag.ElementID
      
inner join t_object
   on  att.Object_ID = t_object.Object_ID   
   where att.Object_ID = #OBJECTID#

THANK YOU!

Regards,

Eugene

[Geert Bellekens]

Eugene,

If you need the values of different tagged values in the same record, then you need to join multiple times with t_attributeTag

Code: [Select]

select att.Name, att.Type, att.[Default] as 'Value'
, tv1.VALUE as Tag1, tv2.VALUE as Tag2
, att.Notes as Notes
From t_attribute att
left join t_attributetag tv1 on att.ID= tv1.ElementID
and tv1.Property = 'Tag1'
left join t_attributetag tv2 on att.ID = tv2.ElementID
and tv2.Property = 'Tag2'
inner join t_object on  att.Object_ID = t_object.Object_ID   
where att.Object_ID = #OBJECTID#
Geert

[EugeneP]

Thanks Geert for the solution! I've been playing with it and it works, but unfortunately only for one table as I have to fix the name of the tag (ex. precision). If I have multiple tables within one report with different tags, I would have to have a different query and fragment for each table.

To make the solution more scalable, is there a way to have the query name the column header and provide its value so that each table can have different tags? or group all the tags in one merged cell?

If anyone has other ideas, I'd love to know about them.

Thanks again!

Regards,

Eugene

[Geert Bellekens]

Yes, each table in your report is a different fragment and query. I don't see why that would be problematic.

If you want you can group multiple values into a single cell.

For SQL Server you can use the FOR XML PATH syntax

https://stackoverflow.com/questions/31211506/how-stuff-and-for-xml-path-work-in-sql-server
I've used this in a couple of templates.

Geert