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.