Please enable JS

Build Sparx EA Charts and Reports

June 19, 2018

Prolaborate provides a powerful yet intuitive tool to build interactive reports and charts based on Sparx Enterprise Architect data in minutes.

In this guide, we will look at how you can build these dynamic reports and charts. We will be looking only at the capabilities of these widgets in this guide, please refer to Dashboard Designer to learn about its general functionalities.

EA Charts

Click on Menu > Dashboards. Click on Add New to add a dashboard or click on Edit icon on an existing dashboard. Click on Add Widget, select EA Chart widget from the list of widgets and click on Add New Widget.

The steps involved are explained below.

Enter Query

You can select the type of chart you want to build and enter the queries for them in this step.

Chart Name

You can give a name to the chart which will define its purpose.

Chart Type

You can create 4 kinds of charts. Pick one of

  1. Pie
  2. Donut
  3. Bar and
  4. Stacked Bar

Chart Query

The chart will be built on this query.

Mandatory alias that needs to be used in queries for pie and donut charts is “Series”. For example, select object_type as series from t_object

Mandatory aliases that need to be used in queries for bar and stacked bar charts are “Series” and “GroupName”. Optionally, you can use the alias “ChartValue” if you want to display an integer value from a tagged value field instead of counts on bars. For example, select object_type as series, stereotype as groupname from t_object

Result Query

If you want to make the chart clickable, enable the Clickable Chart toggle. Then you can enter the result query. The result of this query is what will be displayed when you click on a chart.

Mandatory aliases that need to be used remain same as above but they need to be used as shown in the example. Here are the respective result queries for the chart query examples shown above:

  1. select name, object_type, stereotype where object_type = ‘<series>’
  2. select name, object_type, stereotype where object_type = ‘<series>’ and stereotype = ‘<groupname>’

Additionally, you can use the following aliases:

  1. If you want Links on Item Names, add alias "classguid" to the GUID. For example: select name,ea_guid as classguid from t_object where object_type = ‘<series>’
  2. If you want to see Icons, add these aliases Base Type and Stereotype to Type and Stereotype respectively. For example: select name, object_type as basetype, stereotype as stereotype from t_object where object_type = ‘<series>’ and stereotype = ‘<groupname>’
  3. If you don’t want to see a column but you need to use it as an Alias as mentioned above, use “hide_” prefix. For example, the following query will give the same result as above but Base Type and Stereotype columns will not be shown in the result: select name, object_type as hide_basetype, stereotype as hide_stereotype from t_object where object_type = ‘<series>’ and stereotype = ‘<groupname>’
Note: Sample queries and their results are given in the upcoming sections.

Configure Chart

You can configure the look and feel of the chart in this step. This step varies depending on the type of chart.

Pie Chart

The settings are:

  1. Legends Active/ Inactive – If disabled, the legend will not be shown
  2. Legends position – The options are Right, Bottom and Inset. You can change the options and check how it works in Chart Preview.

Donut Chart

The settings work in the same way as Pie Chart.

Bar Chart

Bar Chart specific settings are:

  1. Chart

    You can control whether the chart should show a shadow, width of bars and space between the bars.

  2. Axis

    Configure how the text should be displayed in the x-axis and y-axis of the charts.

  3. Interaction

    Enable Zoom if you want to zoom the chart.

  4. Tooltip

    Configure whether to show tooltips and if they should be grouped when shown.

  5. Grid

    Enabled grids if needed.

Stacked Bar Chart

The settings work in the same way as Bar Chart.

Sample Pie/Donut Charts created from Sparx EA Model

  1. Applications Lifecycle

    Chart Query

    select o.ea_guid as Classguid, o.Object_type as BaseType, o.Stereotype as Stereotype, o.Name As GroupName, tv1.Value as Series from (t_object o left join t_objectproperties tv1 on tv1.Object_ID = o.Object_ID) where tv1.Property='Lifecycle'

    Chart Shown on Dashboard

    Result Query

    select o.Stereotype as Stereotype, o.Name As Name, tv1.Value as Lifecycle, o.ea_guid as Classguid, o.Object_type as BaseType from (t_object o left join t_objectproperties tv1 on tv1.Object_ID = o.Object_ID) where tv1.Property='Lifecycle' and tv1.Value = '<series>'

    Table on Click on Chart

  2. Application Health

    Chart Query

    select o.ea_guid as Classguid, o.Object_type as BaseType, o.Stereotype as Stereotype, o.Name As GroupName, tv1.Value as Series from (t_object o left join t_objectproperties tv1 on tv1.Object_ID = o.Object_ID) where tv1.Property='Health Indicator'

    Chart Shown in Dashboard

    Result Query

    select o.Stereotype as Stereotype, o.Name As Name, tv1.Value as HealthIndicator, o.ea_guid as Classguid, o.Object_type as BaseType from (t_object o left join t_objectproperties tv1 on tv1.Object_ID = o.Object_ID) where tv1.Property='Health Indicator'and tv1.Value = '<series>'

    Table on Click on Chart

Sample Bar/Stacked bar Charts created from Sparx EA Model

  1. Application by Ownership and Vendor

    Chart Query

    select otv.Property as groupname, otv.Value as series
    from ( t_object o
    left join t_objectproperties otv on otv.Object_ID = o.Object_ID)
    where otv.Property in ( 'Business Unit', 'IT Unit', 'Vendor')

    Chart Shown in Dashboard

    Result Query

    select o.Name as Name, otv.Property as Property, otv.Value as Value, o.ea_guid as
    Classguid, o.Object_Type as BaseType, o.Stereotype as Stereotype
    from ( t_object o
    left join t_objectproperties otv on otv.Object_ID = o.Object_ID)
    where otv.Property in ( 'Business Unit', 'IT Unit', 'Vendor')
    and otv.Value = '<series>'

    Table on Click on Chart

  2. Applications by Security Classification

    Chart Query

    select tv1.Value as Series, tv2.Value as GroupName
    from ((t_object o
    left join t_objectproperties tv1 on tv1.Object_ID = o.Object_ID)
    left join t_objectproperties tv2 on tv2.Object_ID = o.Object_ID)
    where tv1.Property='Security Classification'
    and tv2.Property ='Lifecycle'

    Chart Shown in Dashboard

    Result Query

    select o.Name as Name, otv1.Value as SecurityClassification, otv2.Value as Lifecycle, o.ea_guid as Classguid, o.Object_Type as BaseType, o.Stereotype as Stereotype from (( t_object o left join t_objectproperties otv1 on otv1.Object_ID = o.Object_ID) left join t_objectproperties otv2 on otv2.Object_ID = o.Object_ID) where otv1.Property = 'Security Classification'And otv2.Property = 'Lifecycle' and otv1.Value = '<series>'

    Table on Click on Chart

EA Reports

Select EA Reports widget from the list of widgets in the Dashboard. You can create a report from Sparx EA data either through Custom filters or through Custom Queries.

Configure Reports using Custom Filters

Creating a report involves two steps:

  1. First you need to select the packages from which you want to generate the report from.
  2. Then you can apply the filters. You can filter by Type, basetype and stereotype.

Step 1

Enable Select One Level toggle if you want to select a package and its contents up to one level. Enable Select All Levels toggle if you want to select all the items under it (including sub packages and elements in it) when you select a package.

For example, enable Select All Levels and click on Business Domain to select everything under it.

Step 2

The following filters are available:

  1. Type

    The types available are:

    • All
    • Diagrams
    • Packages
    • Elements
    • Linked Documents
  2. Base Type

    You can either enter the base type or choose from the dropdown

  3. Stereotype

    Enable Show Configured Properties to show all the properties you have configured from the Form designer in Modeling Languages for the chosen stereotype. This will work only if you are giving only one Stereotype.

    To know more about Modeling Languages, please refer to its User Guide

Sample Reports created using Custom Filters from Sparx EA Model

  1. List of all diagrams in the Model

    Configuration

    Result

  2. List of All Elements with Stereotype Name as Activity (Table Columns as configured in Form Designer)

    Configuration

    Result

Configure Reports using Custom Queries

You can change the filter type to Custom Query to enter SQL queries and get its results in the report in Dashboard.

If you want to see a subreport based on the data in your report, you can enter a query in “Enable Subreport” field. All the columns you would be using in your “where” part of sub report query must be in “select” part of report query.

For example, if you want to use object_type and stereotype in your “where” statement of subreport query, they must be part of your “select” statement in Report query.

Report query:

select name, object_type as basetype, stereotype as sttype from t_object

Subreport query:

select name, status, priority from t_object where object_type = ‘<basetype>>’ and stereotype = ‘<sttype>’

You can use the following aliases if necessary:

  1. If you want Links on Item Names, add alias classguid to the GUID. For example: select name,ea_guid as classguid from t_object
  2. If you want to see Icons, add these aliases Base Type and Stereotype to Type and Stereotype respectively. For example: select name, object_type as basetype, stereotype as stereotype from t_object
  3. If you don’t want to see a column but you need to use it in your “Where” query or as an “alias” as mentioned above, use “hide_” prefix. For example, the following query will give the same result as above but Base Type and Stereotype columns will not be shown in the result: select name, object_type as hide_basetype, stereotype as hide_stereotype from t_object

Sample Reports created using Custom SQL Queries from Sparx EA data

1. Applications List by Lifecycle

Report Query select o.Name as Name, o.ModifiedDate as Modified, otv.Value as Lifecycle, o.ea_guid as CLASSGUID, o.Object_Type as hide_BaseType, o.Stereotype as hide_Stereotype from ( t_object o left join t_objectproperties otv on otv.Object_ID = o.Object_ID) where otv.Property = 'Lifecycle'

Report in Dashboard

Subreport Query

select o.Name as Name, o.ModifiedDate as Modified, otv.Value as Lifecycle, o.ea_guid as CLASSGUID, o.Object_Type as hide_BaseType, o.Stereotype as hide_Stereotype from ( t_object o left join t_objectproperties otv on otv.Object_ID = o.Object_ID) where otv.Property = 'Lifecycle' and otv.value = '<Lifecycle>'

Complete Subreport

2. Application Health Check

Query

select o.Name as Name, tv1.Value as HealthIndicator, o.ea_guid as Classguid, o.Object_Type as hide_BaseType, o.Stereotype as hide_Stereotype from (t_object o left join t_objectproperties tv1 on tv1.Object_ID = o.Object_ID) where tv1.Property='Health Indicator'

Report in Dashboard

Subreport Query

select o.Name as Name, tv1.Value as HealthIndicator, o.ea_guid as Classguid, o.Object_Type as BaseType, o.Stereotype as Stereotype from (t_object o left join t_objectproperties tv1 on tv1.Object_ID = o.Object_ID) where tv1.Property='Health Indicator' and tv1.value = '<HealthIndicator>'

Complete Subreport

View Complete Report

Click on View All from Dashboard widget to see the full report.

You will be taken to the Table view.

From Table view, you can

  1. Show 10, 25, 50 or 100 entries at a time
  2. Search for any text
  3. Show/Hide columns from the table which you feel are not necessary
  4. Download the list in xlsx, csv and pdf formats
  5. View the list in full screen
  6. Search for text from any single column
  7. Click on the Name of an element to see its details
  8. Double click on the Name to go to its Overview page

You can switch to Thumb view. Click on View details to see the details.

You can also switch to List view. Click on the Name to see its details.

Note: Notes can be seen only from List view.
Any questions? Contact us