Visio 2007: PivotDiagram Tips and Tricks, part 2

  • Article

In the first PivotDiagram tips post, I talked about some of the alternate ways to create a new PivotDiagram. In this post, I’ll cover an alternate way to filter your data.

Filtering by Using “Promote”

After creating your diagram and hooking up your data source, the most frequent first step we see is filtering down the data set to include just the information you want. It is possible to filter by using a dialog (drop down any of the menus in the PivotDiagram window, then click on “Configure Column”) but I find that to be a bit of a pain. It is much easier to just use “Add Categories” to set up a grouping that includes the data you want, then select the shape you want and use the “Promote” command to make that shape the root shape. This effectively filters the data quickly and easily.

For an example, I’ll connect up to a data source containing some simple animal data and filter the data down to only the horses. This is the data I’ll use (I've also attached it to this post):

Here’s the step by step:

  1. Create the PivotDiagram.
  2. Use “Add Category” to break the data down by the category by which you want to filter (in this case, Animal).
  3. Select the shape containing the specific data you want (in this case, Horse).
  4. Use the “Promote” command (under “Other Actions” in the PivotDiagram window).
  5. Now the data has been filtered down to only the horses in the data set (note that the count below in the totals is only 4).
  6. Now your data set is filtered and you can continue working normally. Here I add the Name category.

More Filtering by Using “Promote” and “Merge”

Following the above steps does a simple filter to one value in one column in the original data source.

You can also filter by multiple columns by adding multiple categories to the PivotDiagram, then selecting and promoting a single shape on a lower level. If you are familiar with SQL or another query language, think of this as creating an AND filter for the values that shape represents. In the diagram below, if you promote the highlighted shape you’ll have a filter that is “WHERE Animal=’Horse’ AND Gender=’Male’”.

Sometimes you also want to be able to allow two or more values from a single column in your filter. You can do this as well, by using the “Merge” command before you “Promote”. “Merge” is a great feature by itself (I’ll cover it in more detail in a later post) but it is particularly useful when doing this type of filtering. Using the same example, now we’ll filter the data down to include both cats and dogs.

  1. Again, create the PivotDiagram and add the Animal category.
  2. Now, select the Cat and Dog shapes and select “Merge” under “Other Actions” in the PivotDiagram window. This will create a merged shape representing all of the cats and dogs in the data set.
  3. Select the newly created shape (“Dog, Cat”) and Promote it.

Animals.xls