Using Excel to create an EA dashboard

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.

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.

• 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: