• Seilevel Team

    Here’s the Team

    Welcome to our Seilevel Photo Op.

My Favorite “Hidden” Visio Feature: Extracting Data from Visio Drawings

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

image001

 

 

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:

image003

 

After clicking “New”, Visio launches a wizard which allows you to choose options for the report:

image005

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:

 

image007

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:

image009

 

You can also set up a default sort, filter, and subtotals:

image011

Finally, just choose a location and save the file.  You can also save the report within the drawing itself:

image013

I’ve only used the Excel version of the report, but it also allows the option for XML, HTML, or as a Visio Shape:

image015

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.

 

image017

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.

14 Responses to My Favorite “Hidden” Visio Feature: Extracting Data from Visio Drawings

  1. Scott Helmers January 8, 2013 at 9:29 am #

    Good article — excellent summary and real world example of Visio reports.

    #2 on your wishlist does exist in Visio 2007 Professional (and later). The feature is called data linking: you can link a diagram to an external data source, e.g., Excel, Access, SQL, and then refresh the diagram when the data changes. You can either refresh manually or automatically at a preset time interval. Take a look at Session 8 here http://visio.microsoft.com/MVPsessions or http://office.microsoft.com/en-us/visio-help/import-data-to-shapes-in-your-drawing-HA102749268.aspx. (BTW, the first link refers to Visio 2010 and the second to Visio 2013, but the data linking features work the same way in Visio 2007.) Let me know if you have any questions.

  2. James Hulgan January 16, 2013 at 4:27 pm #

    I do too! Glad it was helpful, and thanks for stopping by our blog!

  3. James Hulgan January 16, 2013 at 4:30 pm #

    Hi Scott. Thanks for your comment. I learn something new every day! I’m going to check out data linking and give it a try–this will be a huge timesaver for me, and will also allow me to manage some of the associated data and traceability much more easily. I really appreciate you stopping by and commenting.

  4. Bill Manas November 6, 2014 at 5:46 am #

    Thank you for your post, but is it possible to categorize the functions in the report by the containers?

  5. Hossein Zati May 29, 2016 at 1:55 am #

    Hi

    thank you so much for your valuable post. I have a drawing with so many critical “Comments” on each shape. Would you please guide me what’s the best way to manipulate these comments by exporting them into a hierarchical export of shapes to excel worksheets?

    it is very important to have comments in each shape report to manipulate them and if possible to import the revised comments into drawing to be replaced with the previous comments. it is not feasible when I am going to do this manually.

    thanks in advance

  6. Ed Johnson January 4, 2017 at 2:53 pm #

    I hate to say it but it looks like this feature was removed from Visio 2013 :(.

  7. Rafael Hernández January 30, 2017 at 9:05 pm #

    Guys, this is available at Visio 2016. Please go to Review tab > Shape Reports and enjoy!

  8. Olga May 8, 2017 at 2:35 am #

    Hi, just find your article. thank you very much!
    I wonder if there is a way to export screen tips linked to the shape.
    Do you know?

  9. Derek Korff June 25, 2017 at 6:07 am #

    Thanks, you saved me a lot of time.

  10. James Hulgan August 24, 2017 at 1:18 pm #

    Hi Olga,

    I haven’t tried exporting screen tips–admittedly I don’t know if I’ve ever used them. You could try adding one and using the Shape Reports feature to see if they show up. Thanks for stopping by!

    James

  11. Cindy March 2, 2018 at 7:12 am #

    Absolutely ACE!! thank you

  12. Denis Bergeron March 12, 2018 at 1:49 pm #

    In Visio 2016, the only format available for saving is VRD, no XML output.

  13. James Hulgan April 6, 2018 at 3:14 pm #

    Thanks, Denis. I need to update my post. Visio in O365 has updated their functionality significantly. Under the “Review” tab, the “Shape Reports” functionality provides more or less the same extract as previous versions, including XML. I skipped right to 365 from Visio 2013 so am not as familiar with how 2016 works!

Leave a Reply

Your email address will not be published. Required fields are marked *