Create a PivotDiagram in Visio

A PivotDiagram is a collection of shapes arranged in a tree structure that helps you to analyze and summarize data in a visual, easy-to-understand format. It starts out as a single shape, called a top node, that contains information imported from a worksheet, table, view, or cube. You can break the top node into a level of subnodes to view your data in various ways.

To understand categories, levels, and nodes, think of each element in the context of your data source:

Each column of your data source is either nonnumeric or numeric.
Think of the nonnumeric columns as categories — like Quarter, Salesperson, or Phone Number. Any of these can become a level under the top node. Note that the values under a column like Phone Number might be numbers, but they are not numbers that can be totaled or otherwise summarized.
Think of the numeric columns as data such as numbers or currency that can be summed or averaged, or that have minimum or maximum values.
Think of each node as a group of rows from your data source that have a common value in a specified column. For example, for the category "Quarter," all the rows with QTR4 would be grouped into a single node.

Create a PivotDiagram

Open Visio.
In the Business template/category, click Pivot Diagram > Create.
Follow the steps in the Data Selector Wizard.

After you click Finish, the following three shapes appear on the drawing page:
- A data legend containing information about the data source
- A text box for the name of the PivotDiagram
- The top node, which contains the imported data set
Click the top node on the drawing page, and in the PivotDiagram tab on the ribbon, under Add Category, click the category (usually a column in your data source) by which you want to group your data.

The PivotDiagram window, that appears to the left of the PivotDiagram drawing has two lists— Add Category and Add Total.

The Add Category list shows categories in your data source by which you can group your data. The categories correspond to columns for most data sources (dimensions for Microsoft SQL Server Analysis Services). To break a node down into subnodes, click a node in your PivotDiagram drawing, and then click a category in the Add Category list.

Each category has four available features. Under Add Category, right-click the category name to access the following features:

Add <category name> This option does the same thing as clicking the name — it breaks the selected node into subnodes by that category.
Select all This option selects all the nodes in the drawing that are broken down into that category.
Edit Data Graphic This option open the Edit Data Graphic dialog box, where you can customize the appearance of your data on the nodes.
Configure Column This option opens the Configure Column dialog box (for SQL Server Analysis Services, the Configure Dimension dialog box), where you can filter the rows of your data to a subset that meets your criteria. To remove the filter, reset the box in the Show data where column to (Select Operation).

The Add Total list shows the columns in your data source that can be summarized and listed in the nodes. For example, you may want to show the total sales made by each sales person while also showing the number of orders that each sales person handled.

In addition to the columns from your data source, Add Total includes a Count item (except for SQL Server Analysis Services), which counts the number of rows from your data source that are represented in each node.

Each item in the Add Total list has six available features. Right-click the item name to access the following features:

Sum This option adds the numeric values of all the rows contained in each node.
Avg This option calculates the average of the numeric values of all the rows contained in each node.
Min This option shows the minimum value of all the rows contained in each node.
Max This option shows the maximum value of all the rows contained in each node.
Count This option shows the number of rows contained in each node.
Configure Column This option opens the Configure Column dialog box (for SQL Server Analysis Services, it's the Configure Dimension dialog box), where you can filter the rows of your data to a subset that meets your criteria. To remove the filter, reset the box in the Show data where column to (Select Operation).

Filter the data in a PivotDiagram

Each node in a level of a PivotDiagram represents a group of rows in your data source that share a common value. For example, a node for the fourth quarter (Qtr4) would contain all of the numeric data for the rows that have Qtr4 in the Quarter column.

You can use filters to choose which nodes appear in a given level. For example, if the data for the first quarter is no longer of interest, you can conceal the Qtr1 node:

In the PivotDiagram window, under Add Category, right-click the data (category name) that you want to filter, and then click Configure Column.
In the Configure Column dialog box, under Filter, under Show data where: <category name>, select the operations in the leftmost column, and type the values into the rightmost column to specify the data that you want to work with.

Only the nodes with information that meets the criteria that you selected appear in the drawing.

To remove the filter, reset the operations in the leftmost column of the Show data where list to (Select Operation).

Note: If your data source is a SQL Server Analysis Services cube, right-click the item that you want to filter, click Configure Dimension, and in the Configure Dimension dialog box, click Configure Level. In the Configure Level dialog box, under Filter, under Show data where category name, select the operations in the leftmost column, and type the values into the rightmost column to specify the data that you want to work with.

Customize the data in PivotDiagram nodes

When you expand a PivotDiagram node, a default set of data is displayed in each node. You can change which numeric data is displayed, the name of that data, and how numeric data is summarized.

Show or hide specific data

When you show or hide the numeric data (usually a column in your data source), the change is applied to all of the nodes in the selected PivotDiagram.

Select any node of the PivotDiagram.
In the PivotDiagram window, under Add Total, select or clear the check boxes for the data that you want to show or hide.

Change how numeric data is summarized

The default data shown in a PivotDiagram node is the sum of the first column from your data source. You can change the summary function from Sum to Average, Min, Max, or Count.

Note: This does not apply to SQL Server Analysis Services.

In the PivotDiagram window, under Add Total, right-click the item that you want to change, and choose the summary function that you want to apply.

Change the names of items in the Add Category and Add Totals lists

In the PivotDiagram window, under Add Category or Add Total, right-click the item that you want to change, and then click Configure Column.
In the Name box of the Configure Column dialog box, type a new name.

To revert to the original name, click Use Source Name.

Limit the number of nodes in levels

You can choose how many nodes to display in a single level or in all levels of your PivotDiagram. This is helpful if you are working with large worksheets, tables, or views and you don't need to see all of the data broken into separate nodes.

For example, you may want to show only the first 5 nodes in a level of salespeople or the first 20 nodes in a level of orders.

Limit the number of nodes displayed in all levels

Click the top node of the PivotDiagram that you want to change.
On the PivotDiagram tab, in the Data group, click the Data Dialog Box Launcher.
In the PivotDiagram Options dialog box, under Data options, select the Limit items in each breakdown check box.
In the Maximum number of items box, type the maximum number of nodes that you want to show.

For each level, a new node with an ellipsis (...) in its title bar appears in your PivotDiagram. These shapes contain the hidden nodes for each level.

Limit the number of nodes displayed in a single level

In your diagram, select the Breakdown shape of the level that you want to limit.
On the PivotDiagram tab, in the Sort & Filter group, click Sort & Filter.
In the Breakdown Options dialog box, under Show, select the Limit items in this breakdown check box.
In the Maximum number of items box, type the maximum number of nodes that you want to show.

A new node with an ellipsis (...) in its title bar appears in your PivotDiagram. This shape contains the hidden data for that level.

Automatically re-order all of the nodes in a level

Use the Sort & Filter button on the PivotDiagram tab to change the order of the nodes in a level according to a value in the data. For example, let's say you have a top node with sales broken down by salesperson. You can order the salesperson nodes by name or by how much profit each salesperson made.

Select the Breakdown shape for the level that you want to sort.
On the PivotDiagram tab, click Sort & Filter.
In the Breakdown Options dialog box, select a category (usually a column from your data source) and an aggregation (like Sum, Min, or Max) by which to order the nodes, and then click OK.

Manually move a single node within a level

Click the node that you want to move.
On the PivotDiagram tab, click Move Left/Up or Move Right/Down.

Refresh the data in a PivotDiagram

You can refresh the data in an existing PivotDiagram. If your drawing contains more than one PivotDiagram, you can refresh one or all of them.

To refresh a PivotDiagram, select it in the drawing. On the PivotDiagram tab, in the Data group, click Refresh.
To refresh all of the PivotDiagrams in a drawing, click the arrow under Refresh and then click Refresh All.