On a recent project, I found myself cobbling together spreadsheets which included a lot of the data elements from some of my Visio diagrams. One such case occurred early on in the project, when I was reviewing a feature tree with stakeholders, and attempting to map branches of the feature tree to various Subject Matter Expertts. Furthermore, I wanted to use the feature tree as a starting point for planning the work effort by matching the various branches to project deliverables. It was painstaking, not to mention error-prone, to copy/paste or reproduce the text of each of the 200+ branches into a structured Excel spreadsheet. About halfway through the process of transforming a mammoth feature tree into a spreadsheet, I began thinking to myself, “Surely, I’m not the first person who has attempted to do this. Certainly the good folks at Microsoft have already come up with a solution to the same problem.” My intuitions were correct, I discovered as I began browsing through the various menus in Visio. I stumbled upon the “Reports” option in the “Data” menu (I still use Visio 2007. Forgive me):
Clicking on this opens a menu which allows you to create your own reports as data extracts in excel. You can see I already have some reports defined, but you can click on “New” to create one from scratch:
After clicking “New”, Visio launches a wizard which allows you to choose options for the report:
Clicking “Advanced” further allows you to limit the shapes used in the report. I’ve found this useful when there are a lot of connecting shapes (e.g., arrows) that I don’t really care to report on as they do not contain much useful information:
It’s also possible to filter out certain shape properties, although I usually run the report with all of them and just hide the unwanted columns, in case I find the data to be useful later:
You can also set up a default sort, filter, and subtotals:
Finally, just choose a location and save the file. You can also save the report within the drawing itself:
I’ve only used the Excel version of the report, but it also allows the option for XML, HTML, or as a Visio Shape:
Here is a sample of the report output. As you can see, it is nicely formatted with column headers. You can now sort/filter your data as appropriate and use it as you like. There are some data elements that I don’t really use (Height, Width, X Location, Y Location), but “Displayed Text” (the user-defined label on the shape) and “Master Text” (the type of shape as defined in the stencil master) I almost always use.
The Visio reporting functionality has become an indispensible tool in my requirements work. I’ve also used it to extract data from Process Flows and other models. It’s useful for whenever you need to map data from a drawing to other requirements artifacts, and can help you build traceability between steps in process flows and requirements statements, for example. In fact, we recently used data extracts of all the process flows on a project to create a roles and responsibilities matrix by mapping process flow steps to SMEs.
Some things I wish the reporting function out of Visio would do that it doesn’t (Microsoft Product Managers, are you listening?)
- More sophisticated structuring of the data. My feature tree typically has Level 1, 2, and 3 branches. Other than manually and creatively sorting/color-coding the data, there is no way to determine parent-child relationships. To allow better presentation and analysis, it would be great if there were something that would visually present some of the structures that are in many of the diagrams. There may be a workaround by tweaking some of the data associated with Visio shapes in my drawings, but that is fairly time-consuming.
- Import of changes to excel spreadsheet reports to the diagram. During my reviews of data extracts, I often find errors or items that have changed based on new information. If I were able to edit drawing data in a spreadsheet during the review, and have the drawing reflect the changes, it would prevent me from having to maintain a Visio drawing AND a spreadsheet.
- The ability to consolidate data from multiple drawings in a single report. I often create a multitude of process flows and other models for any given project, and might need to trace requirements or business owners to various steps. Rather than having to manually merge several dozen reports together, it would be a huge time-saver if Visio was able to report on several drawings at once, and aggregate the data by each drawing.