Author Topic: Export requirement tag text outputs <memo>  (Read 12721 times)

Hurra

  • EA User
  • **
  • Posts: 183
  • Karma: +0/-0
    • View Profile
    • Find me at LinkedIn!
Export requirement tag text outputs <memo>
« on: September 07, 2021, 04:27:38 pm »
Hi!

If I export our requirements as CSV I can add TagNotes_text to show the tagged value text, which is of type memo, as text.

However, we find the CSV-export a bit limited of fields/values to export. So we use the Office MDG Excel-export. But I can't get the tagged value (memo) to show it's actual value, it always outputs <memo>. I tried all kinds of stuff.

Does anyone know how I can get the actual string to print instead of <memo>?

Can I furhter customize the built-in CSV-export?

Is it about time to start working on our own export solution?

Thanks!
always learning!

Geert Bellekens

  • EA Guru
  • *****
  • Posts: 13256
  • Karma: +554/-33
  • Make EA work for YOU!
    • View Profile
    • Enterprise Architect Consultant and Value Added Reseller
Re: Export requirement tag text outputs <memo>
« Reply #1 on: September 07, 2021, 04:59:46 pm »
Writing your own export can start really simple.

Write and SQL search, and use the standard functions to export the results to CSV (make sure to select all results before exporting)
Since v15 this works reasonably well, even for things like newlines etc...

A step further is to write a script to export stuff to .CSV or Excel.

Here's an example of such a script that exports directly to Excel: https://github.com/GeertBellekens/Enterprise-Architect-VBScript-Library/blob/master/Projects/Project%20A/Reports%20and%20exports/LDM%20complete%20overview.vbs

Another possibility is to use my Excel import/export tool. That lets you write an SQL query to define what you want to export: https://bellekens.com/product/ea-excel-import-export/

Geert

Hurra

  • EA User
  • **
  • Posts: 183
  • Karma: +0/-0
    • View Profile
    • Find me at LinkedIn!
Re: Export requirement tag text outputs <memo>
« Reply #2 on: September 13, 2021, 11:04:27 pm »
Writing your own export can start really simple.

Write and SQL search, and use the standard functions to export the results to CSV (make sure to select all results before exporting)
Since v15 this works reasonably well, even for things like newlines etc...

A step further is to write a script to export stuff to .CSV or Excel.

Here's an example of such a script that exports directly to Excel: https://github.com/GeertBellekens/Enterprise-Architect-VBScript-Library/blob/master/Projects/Project%20A/Reports%20and%20exports/LDM%20complete%20overview.vbs

Another possibility is to use my Excel import/export tool. That lets you write an SQL query to define what you want to export: https://bellekens.com/product/ea-excel-import-export/

Geert

Thank you Geert!

I have started, and got quite a bit finished! But I think I'm stuck right now...

This outputs a simple Excel which works fine:
Code: [Select]
case otPackage :
{

// Code for when a package is selected
var thePackage as EA.Package;
thePackage = Repository.GetTreeSelectedObject();

var packageElements as EA.Collection;
packageElements = thePackage.Elements;

Session.Output("Print excel");
var excelApp = new ActiveXObject("Excel.Application");
var excelSheet = new ActiveXObject("Excel.Sheet");


Session.Output("  Excel reference created");
excelSheet.Application.Visible = false;


// Create headers
var header = new Array();
header = ["Name", "ID", "Text", "Package"];

// Add headers to Excel
for ( i = 0; i < header.length; i++)
{
excelSheet.ActiveSheet.Cells(1, i+1).Value = header[i];
}

// SQL-search to find elements to export
var queryString = "SELECT requirement.ea_guid AS CLASSGUID, requirement.Object_Type AS CLASSTYPE, requirement.Name AS Name, reqID.Value AS ReqID, reqText.Notes AS ReqTEXT, package.Name AS Package FROM t_object AS requirement INNER JOIN t_package AS package ON package.Package_ID = requirement.Package_ID INNER JOIN t_objectproperties AS reqText on reqText.Object_ID = requirement.Object_ID INNER JOIN t_objectproperties AS reqID on reqID.Object_ID = requirement.Object_ID WHERE requirement.Package_ID = 3205 AND reqText.Property = 'text' AND reqID.Property = 'id'";
var queryResult as EA.Collection;
queryResult = Repository.SQLQuery(queryString);
Session.Output(queryResult);
var DOMDoc = XMLParseXML(queryResult);
var elementNames = XMLGetNodeTextArray( DOMDoc, "//Row/Name" );
var reqIDs = XMLGetNodeTextArray( DOMDoc, "//Row/ReqID" );
var reqTEXTs = XMLGetNodeTextArray( DOMDoc, "//Row/ReqTEXT" );
var package = XMLGetNodeTextArray( DOMDoc, "//Row/Package" );

for ( i = 0; i < elementNames.length; i++ )
{
excelSheet.ActiveSheet.Cells(i+2, 1).Value = elementNames[i];
excelSheet.ActiveSheet.Cells(i+2, 2).Value = reqIDs[i];
excelSheet.ActiveSheet.Cells(i+2, 3).Value = reqTEXTs[i];
excelSheet.ActiveSheet.Cells(i+2, 4).Value = package[i];
}

excelSheet.SaveAs("H:\\exportedExcel.xlsx");
excelSheet.Application.Quit();
excelSheet = "";
Session.Output("Print excel done!");

break;
}


As you might have figured out I start the script through the project browser, by right-clicking on a package. My thought was to use #BRANCH# in the SQL-search to also get all the elements in sub-packages. In the model search/find in project window this query is exactly what I'm looking for:

Code: [Select]
SELECT requirement.ea_guid AS CLASSGUID, requirement.Object_Type AS CLASSTYPE, requirement.Name AS Name, reqID.Value AS ReqID, reqText.Notes AS ReqTEXT, package.Name AS Package
FROM t_object AS requirement
INNER JOIN t_package AS package ON package.Package_ID = requirement.Package_ID
INNER JOIN t_objectproperties AS reqText on reqText.Object_ID = requirement.Object_ID
INNER JOIN t_objectproperties AS reqID on reqID.Object_ID = requirement.Object_ID
WHERE requirement.Package_ID IN (#Branch#)
AND reqText.Property = 'text'
AND reqID.Property = 'id'

However, the IN (#Branch#) doesn't seem to work with the Repository.SQLQuery(...).

As for now I have 'hard-coded' in a Package_ID and get all the elements in that specific package. Do you have any suggestion how I could use #Branch#? Am I doing something wrong?

What I'm after is to find all elements (or to be specific requirements) in the package, including all sub/child-packages, all the way down.

I would appreciate a nudge in the right direction!

Thank you!
always learning!

Geert Bellekens

  • EA Guru
  • *****
  • Posts: 13256
  • Karma: +554/-33
  • Make EA work for YOU!
    • View Profile
    • Enterprise Architect Consultant and Value Added Reseller
Re: Export requirement tag text outputs <memo>
« Reply #3 on: September 13, 2021, 11:25:22 pm »
See the function getPackageTreeIDString(package) in this script:https://github.com/GeertBellekens/Enterprise-Architect-VBScript-Library/blob/master/Framework/Utils/Util.vbs

That basically does the same as #Branch#

Geert

Hurra

  • EA User
  • **
  • Posts: 183
  • Karma: +0/-0
    • View Profile
    • Find me at LinkedIn!
Re: Export requirement tag text outputs <memo>
« Reply #4 on: September 14, 2021, 07:46:19 pm »
See the function getPackageTreeIDString(package) in this script:https://github.com/GeertBellekens/Enterprise-Architect-VBScript-Library/blob/master/Framework/Utils/Util.vbs

That basically does the same as #Branch#

Geert

Thank you!

I'm almost there...

I had a slighlty different approach, because at first I didn't quite understand what you did. But I think I ended up with basically the same thing, but more buggy and not fully working  ;D

I initiate the function like this:
Code: [Select]
var allPackages = new Array();
allPackages[0] = thePackage.PackageID;
allPackages = getChildPackageIDs(allPackages, allPackages);
Session.Output("RETURNED: " + allPackages);
var queryString = "SELECT ... IN (" + allPackages.join(", ") + ")

Then my function getChildPackageIDs:

Code: [Select]
function getChildPackageIDs(allPackageIDs /* : Array of PackageIDs */, parentPackageIDs /* : Array of PackageIDs */) //Output: Array of PackageIDs
{
// Array.join doesn't work if array only has one value, how do I improve this temporary solution?
if (parentPackageIDs.length == 1)
{
parentPackageIDs.push("9999999");
}

var sqlQuery = "SELECT package.Package_ID FROM t_package package WHERE package.Parent_ID IN (" + parentPackageIDs.join(", ") + ")";
var queryResult = Repository.SQLQuery(sqlQuery);
var DOMDoc = XMLParseXML(queryResult);
var childPackageIDs = XMLGetNodeTextArray( DOMDoc, "//Row/Package_ID" );

allPackageIDs = allPackageIDs.concat(childPackageIDs);

// Without this the function returns once per iteration and over-writes the recursive allPackageIDs...?
if ( childPackageIDs.length > 0)
{
getChildPackageIDs(allPackageIDs, childPackageIDs)
}
if ( childPackageIDs.length == 0 )
{
Session.Output("RETURN: " + allPackageIDs)
return allPackageIDs;
}
}

Some problems.

1. I can't use Array.join if the array onlye has one value. I tried to have an if-statement in the var sqlQuery, but didn't get it to work, so a temporary solution is to add a 'dummy' PackageID. Any suggestions how I can improve that?

2. Without the if-statements in the end of the function, the returned allPackageIDs wrote over each other, with the final allPackageIDs just containing the top-level childpackages.

3. If I print out the return of the function in the last if-statement, Session.Output("RETURN: " + allPackageIDs), this is the correct array of PackageIDs. However, when I print the returned array after I intiate the function Session.Output("RETURNED: " + allPackages) (first code-box) it returns undefined, and the query obviously doesn't work then.
Edit: found a missing ; on the line allPackages = getChildPackageIDs(allPackages, allPackages), but same issue.

How can it be that the return is correct in the function, but the actually returned array is undefined?

From System Output:
RETURN: 3205, 9999999, 3354, 3355, 3357, 3356   

RETURNED: undefined

Thank you for your excellence guidance  8)
« Last Edit: September 14, 2021, 08:41:23 pm by Hurra »
always learning!

Geert Bellekens

  • EA Guru
  • *****
  • Posts: 13256
  • Karma: +554/-33
  • Make EA work for YOU!
    • View Profile
    • Enterprise Architect Consultant and Value Added Reseller
Re: Export requirement tag text outputs <memo>
« Reply #5 on: September 14, 2021, 08:38:01 pm »
1. You can either build your SQL query string differently if there's only one element in your array, or you can do it this way. But in the latter case I would use "0" as dummy value. "99999" might one day be an actual packageID. "0" will never.

I'm not sure about your other questions. I would have to debug them to really understand the problem.

Geert

Hurra

  • EA User
  • **
  • Posts: 183
  • Karma: +0/-0
    • View Profile
    • Find me at LinkedIn!
Re: Export requirement tag text outputs <memo>
« Reply #6 on: September 14, 2021, 08:44:08 pm »
1. You can either build your SQL query string differently if there's only one element in your array, or you can do it this way. But in the latter case I would use "0" as dummy value. "99999" might one day be an actual packageID. "0" will never.

I'm not sure about your other questions. I would have to debug them to really understand the problem.

Geert

Yeah, I had 0 first. But then I found a bunch of child packages. But I don't get how a parent package can have ID 0?

I will try to solve this undefinied issue. Will update if I find the problem.
always learning!

qwerty

  • EA Guru
  • *****
  • Posts: 13584
  • Karma: +396/-301
  • I'm no guru at all
    • View Profile
Re: Export requirement tag text outputs <memo>
« Reply #7 on: September 14, 2021, 08:52:48 pm »
Parent packace = 0 means you have the root in hands.

q.

Geert Bellekens

  • EA Guru
  • *****
  • Posts: 13256
  • Karma: +554/-33
  • Make EA work for YOU!
    • View Profile
    • Enterprise Architect Consultant and Value Added Reseller
Re: Export requirement tag text outputs <memo>
« Reply #8 on: September 14, 2021, 09:03:07 pm »
of course. Maybe add "and package.Parent_ID <> 0" to your query? That way you could still use "0" as the dummy value.

Hmm, or you can try "-1" as dummy. I think that should work as well...

Geert

Hurra

  • EA User
  • **
  • Posts: 183
  • Karma: +0/-0
    • View Profile
    • Find me at LinkedIn!
Re: Export requirement tag text outputs <memo>
« Reply #9 on: September 21, 2021, 07:15:06 pm »
Parent packace = 0 means you have the root in hands.

q.

of course. Maybe add "and package.Parent_ID <> 0" to your query? That way you could still use "0" as the dummy value.

Hmm, or you can try "-1" as dummy. I think that should work as well...

Geert

Yeah, I figured I try that, and it works.

Regarding the problem I had with the recursive function, I removed the last if statement, and in the first if statement I updated the allPackageIds variable. Seems to work fine now.

Code: [Select]
if ( childPackageIDs.length > 0)
{
allPackageIDs = getChildPackageIDs(allPackageIDs, childPackageIDs);
}
return allPackageIDs;

For those interested I added an alternative SQL search which finds all elements in the package diagrams. We have these two alternative queries because how we have modelled. The elements in the package is a subset of the requirements, and the diagram elements is the sum of all requirements.

I don't know if this makes sense without knowing the specifics, but here's the query for finding all elements in the package diagrams:

Code: [Select]
var queryString = "SELECT requirement.ea_guid AS CLASSGUID, requirement.Object_Type AS CLASSTYPE, requirement.Name AS Name, requirement.Version AS Version, reqID.Value AS ReqID, reqText.Notes AS ReqTEXT, package.Name AS Package " +
"FROM t_object AS requirement " +
"INNER JOIN t_diagramobjects AS diagramObjects on requirement.Object_ID = diagramObjects.Object_ID " +
"INNER JOIN t_diagram AS diagram on diagramObjects.Diagram_ID = diagram.Diagram_ID " +
"INNER JOIN t_package AS package ON package.Package_ID = diagram.Package_ID " +
"INNER JOIN t_objectproperties AS reqText on reqText.Object_ID = requirement.Object_ID " +
"INNER JOIN t_objectproperties AS reqID on reqID.Object_ID = requirement.Object_ID " +
"WHERE diagram.Package_ID IN (" + packageIDsQuery.join(",") + ") " +
"AND reqText.Property = 'text' " +
"AND reqID.Property = 'id' " +
"ORDER BY len(reqID.Value), reqID.Value";

Thank you for your help! <3
always learning!