Sparx Systems Forum

Enterprise Architect => General Board => Topic started by: TheBlueSki on February 15, 2024, 09:18:17 pm

Title: SQL Query - extracting tag iformation and headings.
Post by: TheBlueSki on February 15, 2024, 09:18:17 pm

My level of SQL experience is not great (better on the managing database cluster end), but I'm a quick learner and have a lot of experience with pretty much everything else.  Enterprise Architect is new to me, but I'm trying to help some people out with it and improve the information they can pull out of it.  I have an SQL query which does produce some of the results I need, however I wish to also pull out in columns some of the tag information.
What I have so far is this – (which also deals with the fact we have a duplicate model, so I don’t want to pull double the results).
SQL Example –

Select t_object.Name as Name,
          MIN(t_object.Object_ID) as Object_ID,
           t_object.Note as Description,
           t_object.Phase as Phase from t_object
inner join t_objectproperties on t_object.Object_ID = t_objectproperties.Object_ID
where t_object.Object_Type = ‘Requirement’
and t_object.Sterotype = ‘Supporter’
and t_object.Status = ‘Approved’
and t_object.Phase = ‘1’
group by t_object.Name
order by t_object.Name, MIN(t_object.Object_ID)

(With this query I get the columns I need, Name, Object_ID, Description and Phase).  What I would also like is to pull some of the tag information out under heading which is seen below.  But I’m unsure how to do this.  It’s in there somewhere t_objectproperties.Value.

“Tag Names”                             “Values”
Compliance extra detail                              <memo>
Compliance statement                                text
Compliance target                                      Compliant
Design Response                                        text
Hardware Requirements                              No
MVP                                                          No
RMT                                                          No
Specialist Testing                                        No
Verification Method                                     Inspection

Any ideas or suggestions please? Many thanks.

Title: Re: SQL Query - extracting tag iformation and headings.
Post by: ea0522 on February 15, 2024, 09:29:36 pm

One option I use very often in these cases is the Excel Import/Export tool of Geert Bellekens (see https://bellekens.com/ea-excel-import-export/ (https://bellekens.com/ea-excel-import-export/)).
It also contains a configuration sheet with SQL you can change yourself which could be used as an example to create the specific export you need.
I, for instance, altered it to only export information on certain stereotypes.

Title: Re: SQL Query - extracting tag iformation and headings.
Post by: TheBlueSki on February 15, 2024, 09:45:04 pm

Thank you I will give that a try.

Title: Re: SQL Query - extracting tag iformation and headings.
Post by: rupertkiwi on February 16, 2024, 12:52:51 pm

This is a script which can be used in VBA in Excel. Just select the elements you want in the project browser and run the script:

'Lists the Taggedvalues and their values for the selected elements in the Project Browser
Sub taggedvaluelist()
    Dim repository As EA.repository
    Set eaapp = GetObject(, "EA.App")
    Set repository = eaapp.repository
    Dim selectedElements As EA.Collection
    Set selectedElements = repository.GetTreeSelectedElements()
    Dim p As Integer, j As Integer
    Dim selectedElementCount, i
    selectedElementCount = selectedElements.Count
    p = 2
    Set outputws = Worksheets("TaggedValuesList")
    outputws.Rows("2:" & Rows.Count).ClearContents
    If selectedElementCount > 0 Then
        For i = 0 To selectedElementCount - 1
            Dim theelement As EA.element
            Set theelement = selectedElements.GetAt(i)
            Dim tags As EA.Collection
            Set tags = theelement.taggedvalues
            If tags.Count = 0 Then
            outputws.Cells(p, 1) = theelement.Name
            outputws.Cells(p, 2) = theelement.StereotypeEx
            outputws.Cells(p, 5) = theelement.ElementID
            p = p + 1
            End If
            For j = 0 To tags.Count - 1
                Dim currentTag As EA.taggedvalue
                Set currentTag = tags.GetAt(j)
                outputws.Cells(p, 1) = theelement.Name
                outputws.Cells(p, 2) = theelement.StereotypeEx
                outputws.Cells(p, 3) = currentTag.Name
                outputws.Cells(p, 4) = currentTag.Value
                outputws.Cells(p, 5) = currentTag.ElementID
                outputws.Cells(p, 6) = currentTag.PropertyID
                p = p + 1
                Debug.Print theelement.Name & ":" & currentTag.Name & ":" & currentTag.Value
            Next
        Next
    End If
End Sub

Title: Re: SQL Query - extracting tag iformation and headings.
Post by: Geert Bellekens on February 18, 2024, 08:57:40 pm

You simply have to join t_objectproperties multiple times to get the tagged values

Something like this

Code: [Select]

select o.Name, tv1.Value as DesignResponse, tv2.Value as Compliance
from t_object o
left join t_objectproperties tv1 on tv1.Object_ID = o.Object_ID
and tv1.Property = 'Design Response'
left join t_objectproperties tv2 on tv2.Object_ID = o.Object_ID
and tv2.Property = 'Compliance'
Geert

Title: Re: SQL Query - extracting tag iformation and headings.
Post by: TheBlueSki on February 27, 2024, 10:38:28 pm

That worked very well.  Do you think there is away to get a count out of the number of rows returned.  That I can set as a variable and atatched to a report or EA template?

Title: Re: SQL Query - extracting tag iformation and headings.
Post by: TheBlueSki on March 05, 2024, 02:52:04 am

Putting a COUNT(*) as COUNT from(

<<<The SQL query>>>

) mytable

Did the trick, but probably not the best way.