Home / Support for EA Modelling With eaDocX / Insights / Using Excel to create an EA dashboard

Using Excel to create an EA dashboard

A simple example of an EA dashboard, to show how you can create your own and show the information in your EA model to your project team

Most EA models contain a heap of really useful information. Not just for the owners of the model, but for lots of other people in your project team who’d like to know what’s going on.

Trouble is, getting information to some of these people can be hard: they may not ‘get’ UML or SysML diagrams. They may be spreadsheet, or even graph people!

But what kinds of information do these ‘other’ people want?

What a lot of people want to know is just ‘how’s it going?’. Or is there anything going on which they need to know about, that will require them do do something? So they need a highly processed form of EA information. Very often, these same people are interested in time-trends. “Do we have any more issues than last week – are they getting fixed? Are we done creating requirements yet , or are they still being discovered?

We can address lots of these requirements with a ‘dashboard’ approach: just collecting some of the key indicators of the project together, and presenting them in a simple way, which lets readers see quickly where they need to concentrate.

The Objective

In creating this dashboard we had three objectives:

  • make it really quick to use: if there are 10 steps needed to look at the latest dashboard, then it just won’t get used
  • it should be flexible, to allow for any EA data to be presented in almost any style
  • it should have a time component: not just a snapshot of where we are now, but information which will let us see time trends.

The Bits

To make this work, you’ll need the following files:

  • The Exploring eaDocX.EAP EA repository
  • The Reports with Macros spreadsheet. This is available in two flavours: one with the macro code built in (which you might not be allowed to download) and one without (to which you’ll have to add the macro code yourself).
  • The ‘Exploring – Advanced Reporting’ Word document

All these are available to download from Exploring eaDocX Downloads

The User Experience

Quick and simple.

Once the example is setup, the user opens the report, hits ‘Generate’ in eaDocX, and the latest report appears, with all data updated. Easy.

The Setup

We’ve tried to make this as simple as possible, but there are still a few bits of EA, eaDocX, Word and Excel which need to work together to deliver the simple user experience we described above.

This example will just look at the time-trend of some Issues in a single package of the repository. It might equally well look at Requirements status, Use Case completeness or test progress, but we chose Issues.

First, download all the files from the website: repository, Word Document and Spreadsheet.

Now, a little bit of adjustment is needed, so that the setup will work on your machine with your file system.

  • Open the ‘Exploring eaDocX’ repository
  • Open the ‘Exploring – Advanced Reports’ word document in eaDocX.
    This will have two eaDocX sections:

    • ‘Raw_Data’ shows the current set of issues in the Package, so that you can check that the graph matches reality
    • Issues Trend’ has the Graph, which is pulled from a spreadsheet.
  • The Issues_Trend eaDocX report will point to the location of the spreadsheet which we want to insert. This currently points to a location on my hard drive, so needs to point to yours.
  • Double-click on the ‘Trend of Issues over time’ icon to edit the report
  • If you were able/allowed to download the Excel file with the macros, then select ‘Dashboard with Macros’, and skip to step 7
  • If you were not allowed/able to download the macro spreadsheet, then
    • Open the ‘Dashboard without macros’ file, and create a macro called ‘updateSummary’,
    • Copy & paste the code in Appendix A of the Word document. As you can see, all the macro does is, when called by the eaDocX Generator, puts a summary of the latest snapshot into a separate worksheet, ready to be graphed.
  • That’s it!
    Test it by adding a few more Issue elements to those in the  Model/Excel Reports/Data for Advanced Reports/Issues folder, or change the status of some others, and re-generate the Word document.

How it Works

Just in case you’re interested in the behind-the-scenes magic…

The eaDocX Word document has this section called ‘Issues_Trend’, which is an Excel Report.

This report defines which Excel spreadsheet to open. The spreadsheet in turn knows that it’s an eaDocX spreadsheet, so knows how to get itself updated from its EA repository.

The spreadsheet has 4 pages:

  • Issues” has the raw data from EA. This wil get refreshed each time the document gets generated, so don’t make any changes to this worksheet – they will get overwritten
  • Processed Data” summarises the ‘Issues’ page, and so also gets updated each time ‘Issues’ is changed, just like any other spreadsheet
  • When the macro runs, it just inserts a new line from ‘Processed data’ into ‘Summary‘, which
  • Summary Graph‘ makes into a chart.

When the document is re-generated, eaDocX:

  1. Finds the Excel Report Section
  2. Finds the spreadsheet to which it refers
  3. Updates that spreadsheet with the latest EA data. In this example, we just update one Worksheet, but you could have lots, each with it’s own set of EA data
  4. Finds that there is a Macro defined for this eaDocX Excel Report (in ‘Options’ tab), and runs that macro
  5. The macro could do anything you want. Pull data from another system, run a query, but in this case, it just  creates a new line of summary data in the ‘Summary’ sheet
  6. This in turn causes the Summary Graph to update
  7. Finally, eaDocX pulls the nicely-updated Summary Graph into the word document, and adds scaling and some titles, if required.

All that, to deliver a really simple user experience:

More Insights

eaDocX in the Cloud

6 April 2021

Enterprise Architect document creation for EA SAAS environments

Learn More

Producing a RACI report

11 March 2021

Producing a RACI report from data held in EA, mapping stakeholders to any project deliverables is straightforward with eaDocX.

Learn More

Where's the best place to start modelling?

19 October 2020

Advice for the new modeller #1 - Where do you start?

Learn More

Process based model styles

25 August 2020

How to use BPMN and UML to make models which last.

Learn More

Models matter - nearly as much as deliverables

25 August 2020

Models matter - nearly as much as model deliverables

Learn More

Improving model quality using a Sparx EA reference model

25 August 2020

Maintain quality with tools to find and fix mechanical errors in your modelling

Learn More

Fixing your meta-model

25 August 2020

Advice for the new modeller #3 – Fixing your meta-model

Learn More

How much domain modelling is enough?

25 August 2020

Advice for the new modeller #2 – (not) Melting the Pan

Learn More

What needs to be included in your EA model content?

25 August 2020

Advice for the new modeller #4 – (not) Modelling The World

Learn More

Knowing when to give up

25 August 2020

Knowing when to step back makes for better Business Analysts

Learn More

Using UML icons for more useful models

25 August 2020

Diagram Graphics for Adults

Learn More

Using Enterprise Architect to document decision making

25 August 2020

Make your models more useful for future modellers

Learn More

Beck’s Map: an EA model abstraction example

25 August 2020

Possibly the best model abstraction in the world

Learn More

Use case, package or process?

25 August 2020

A time for Packages

Learn More

Simplifying ideas in a BPMN Process Diagram

25 August 2020

How to find the right number of ideas to include in your model.

Learn More

Explaining EA Sparx Systems to non-modellers

25 August 2020

Model driven analysis - the best way to define what we do?

Learn More

How to simplify BPMN Data Models

25 August 2020

Why simplifying your diagrams can actually make them more informative.

Learn More

Create useful models using the Sparx EA Tool

18 August 2020

Advice for the new modeller #3 - Producing useful outputs with your new EA tool.

Learn More

Beginners guide to Enterprise Architect software

18 August 2020

Our advice for new EA modellers

Learn More

UML Business Analyst Solutions

14 August 2020

Using UML to resolve inconsistencies, gaps and overlaps.

Learn More

Cleaning: How to Simplify Enterprise Architecture Models

14 August 2020

Model Curation Techniques # 1 - Cleaning your EA model before you let other people see it

Learn More

Including Sparx EA Model Provenance

14 August 2020

Sparx EA model help to explain to others why your models look the way they do.

Learn More

What to include in your enterprise architect documentation

29 July 2020

How to create documents which communicate your ideas efficiently and effectively to stakeholders.

Learn More

Organising an Enterprise Architect Model

28 July 2020

Seven ways to organise your EA models so that other people can understand them

Learn More

Choosing Your UML Subset

27 July 2020

Narrowing down the modelling ideas in your Enterprise Architect model to make consistent, understandable models.

Learn More

Webinar: Using Interactive Documents to Collate Sparx EA Model Feedback

17 July 2020

A webinar from the EA Global Summit 2020.

Learn More

Webinar: How to successfully scale up your Enterprise Architect team

30 June 2020

A webinar from the EA Global Summit 2020.

Learn More

Navigating Models: Enterprise Architect Help and Techniques

16 April 2020

EA Model Curation Techniques #2 - Making models easy to navigate.

Learn More

Validation: Improving your Enterprise Architecture Model Structure

16 April 2020

EA Model Curation Techniques #3 - Validating your model

Learn More

Modelling techniques for business architecture software to explain general patterns

16 April 2020

Using object diagrams to explain general patterns with specifics.

Learn More

How to create personalised documents using Sparx document generation tools

16 November 2018

A guide to creating documents from Sparx EA models so everyone can engage with your work, project or deliverables.

Learn More

Document or Enterprise Architect Views?

21 May 2018

Create documents to be used by non-EA users instead of model views.

Learn More

Improve your enterprise architecture model using colour

25 April 2018

Careful use of colour and shape makes models easier to digest.

Learn More

Model curation techniques for EA Sparx Systems

6 March 2018

How cleaning, navigating and validating your EA model makes sharing and collaborating much more effective.

Learn More

The most re-used model components in Sparx Enterprise Architect.

20 October 2017

Why is the project summary the most read element in an EA model?

Learn More

Creating a Glossary: Business Analyst Terminology

12 October 2017

Streamline your business understanding with shared definitions.

Learn More

Business Process Analyst Styles

11 October 2017

Find out if you are a 'hard' or 'soft' Business Analyst.

Learn More

How to Print Sparx EA Connectors

18 October 2016

Structure your documents using the connectors in your Sparx model.

Learn More

Using Multi-hop relationships to display Branch/Merge with EA13

11 August 2016

One of the most common requests we see from new EA users is: "Why can’t I do branch/merge with EA?

Learn More

Compare pricing and features

Choose the eaDocX edition that’s right for you and your team

Compare Pricing

Download a free trial

Take a free, no obligation, 30-day trial of eaDocX. Discover for yourself why it’s the world’s best-selling Enterprise Architect extension.

Download