PivotDiagrams, part 1


Hey, what’s up?  I’m back from a little break after the Visio Partner Conference.  Today I’m going to talk about the last of the major data features we are doing in Visio 12:  PivotDiagrams.  You can think of PivotDiagrams as similar to PivotTables in Excel.  They allow you to work with data where you want to see groups and subtotals.  The difference, of course, is that a PivotDiagram does this visually as a diagram:



They are great when you need to communicate the key pieces of information in your data to other people in a very visual way.  It’s also easy to drill into your data with PivotDiagrams and find exactly the right parts of data that you want to present.  PivotDiagrams can take advantage of all of the power of Data Graphics that I’ve been showing you in previous posts, so you can create some graphically rich ways to show your data.


So how do PivotDiagrams work?  They are a new diagram type in Visio 12, so to use one you simply select the PivotDiagram template in the startup screen.  This triggers the Data Selector wizard that lets you choose which data source you want to connect to (PivotDiagrams are always connected to external data).  PivotDiagrams can connect to Excel, Access, SQL Server, SQL Server Analysis Services, SharePoint lists, and other OLE/DB or ODBC data sources.When you select your data source, you end up with a single shape in your PivotDiagram that shows you the sum total of all of the rows in your data.  Here we are looking at data showing the performance of different call center locations for a corporation:



By default we pick a total for you to get you started, but you can easily change which totals are displayed over in the PivotDiagram task pane. 



Simply check different totals and they will appear in the diagram (similar to PivotTables). In this case, we are adding totals for the Solve Rate of customer calls:



Once you have the totals that you want to display, the next step is to choose how you want to break out your data into groups and subtotals.  To do this you use the “Add Category” control in the task pane. Simply click on one of the categories to break out the currently selected shape.  In this example, I’m breaking out the totals by the different types of calls:  Hardware, Software, etc:



You can keep on breaking out the subtotals as well.  So I could select the “Software” shape, and break these totals out by the different priorities of the calls:



So you can see how it’s easy with PivotDiagrams to drill into your data and find the key pieces that you want to communicate.  You can also re-pivot the data by a different category.  You can select the root shape, and choose a different category to drill on.  This will remove all of the other shapes and drill in on the new category (in this case the call center locations).



Now this is showing comparative data for the different call center locations around the U.S. We can go in and add some Data Graphics to show the data in richer, more visual ways.  In this case, the Customer Satisfaction with the different call centers can be visualized as a speedometer data bar, and we can also show a trend arrow indicating if customer sat is going up or down:



We could leave the PivotDiagram like this — it’s already showing us some important information in a visual, easy-to-understand way.  But since this information is geographically related, it might be even easier to understand if we placed it on a map.  This brings us to another important point about PivotDiagrams:  you can easily customize the layout to better communicate the data.  While they start out looking like a tree diagram, similar to an organizational chart, this is just a starting point for you to customize to suit your needs.  In this case, you can simply select and delete the connectors between the shapes, drag out a U.S. map shape to the page, and place the different PivotDiagram shapes in their appropriate locations around the map:




Once you end up with a graphic that you are happy with, you can save it and refresh it on a regular basis as data changes.  This is an easy way to create a graphical report that can be distributed to everyone on your team to keep people updated on current status.  In future posts, I’ll show you some of the pre-defined PivotDiagram reports that we are shipping as part of Office 2007.


Comments (13)

  1. John Ingres says:

    I am confused. This is the kind of stuff we do with Excel, Reporting Services or MapPoint. Visio is used for designing, not reporting.

    If at least the diagram done in Visio could be used/feeded at runtime by a .NET application (without having Visio installed on the workstation), I could see potential.

    It IS nice stuff, but in the wrong product IMHO.

  2. John Ingres says:

    Is it possible to integrate this technology with Reporting Services?

  3. MSDNArchive says:

    Thanks for the comments, John.   I would say that this may be a different direction for Visio than many people are used to, but we have had reporting functions in the product for many releases — Visio is not only about designing.  In addition to the standard reports feature in Visio, we also allow you to import schedule information from Project, calendar information from Outlook, and organizational chart information from a number of data sources including Exchange.  These are all examples of using Visio more as a way of reporting on information than on simply designing something.

    PivotDiagrams are an extension of these types of capabilities, but done in a much more general way.  The key here is that some data is easier to understand when it is visualized as a diagram, and Visio is the best application for creating diagrams.

    As far as integrating this technology with Reporting Services, I’d love to hear the types of scenarios you are thinking would be valuable.

    Thanks,

    Eric

  4. John Ingres says:

    Eric,

    Finding scenarios that works with Reporting Services is easy;) We provide software for hundreds of call centers…just look at your call center example on this page.

    Many reports have more values when presented in an iconographic way. Numbers over a map, stop light charts, profit per call centers, % split per profit sites, etc.

    Showing profits, commissions, etc on a domain organization chart is easier to sell and easier to grasp by anybody looking at it.

    If we could design in Visio and use the template in Reporting Services, we would get fancy attractive reports easily. Like doing a paint job on old reports, data is always the same, but the presentation can be much improved. For the moment to do the same thing we have to customize each report extensively; and then it is not compatible with Reporting Services anymore.

    Reporting Services can export to Excel…why not to Visio too?

  5. Andrew says:

    How about a Visio component for Reporting Services?  Similar to a charting component, this would allow you to drag in a visio object, and set properties to define things like Pivot Diagrams, or dynamically created org charts.

    Hopefully these pivot diagrams will not go the way of MS Data Analyzer – a cool onscreen tool with limited reporting capabilities.

  6. Eric,

    As a matter of fact, I was struggling to acheive this not too long ago. Having cracked open the Edson book and trying to integrate Excel/Access wit Visio is not the easiest thing to do off the shelf.

    Working with engineers that are familiar with Visio and maybe more comfortable with it than Excel, I feel there is ample room for Visio to be an interactive reporting tool.

    Looking forward to more.

    peter

    btw..is mai lan still part of the Visio community?

  7. EH says:

    yeah, RS integration would be great

    a export/rendering engine for Visio would be a killer feature

  8. MSDNArchive says:

    I’m not dead yet!  Just took a little hiatus!

  9. Along with the rest of the 2007 Office system, Visio 2007 Beta 2 is now available for public download…

  10. Eric posted a little bit about PivotDiagrams and what you can do with them. I’d like to add a little…