Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Tips Database Forum Rss Feed

» Database Journal Home
» Database Articles
» Database Tutorials
MS Access
SQL Scripts & Samples
» Database Forum
» Slideshows
Free Newsletters:

News Via RSS Feed

Rss Feed

Database Journal |DBA Support |SQLCourse |SQLCourse2

Featured Database Articles


Posted Jan 8, 2007

Design and Documentation: Introducing the Visio 2007 PivotDiagram - Page 4

By William Pearson

Next, we will add the State and City levels, as well another filter, as we move closer toward our desired documentation with the PivotDiagram.

15.  Click the United States sub node on the canvas to select it, as we did earlier for the top node.

16.  In the PivotDiagram task pane, within the Add Category selection list, once again, click Customer: State-Province.

The PivotDiagram updates once again, this time reflecting the addition of the State - Province level.

17.  Right-click the State-Province level, appearing between the United States node and the newly added State-Province nodes.

18.  Select Configure Level ... once again, from the context menu that appears.

The Configure Level dialog opens.

19.  Substitute the following for the current occupant of the Name box:


20.  Select “equals,” as before, within the top filter selector.

21.  Type the following into the box to the immediate right of the selector (now containing “equals:”):


22.  Select “or” within the selector to the left and below the selector within which we have chosen “equals.”

23.  Select “equals,” within the filter selector to the immediate right, and directly underneath, the selector containing “equals” from before.

24.  Type the following into the box to the immediate right of the selector now containing “equals:”


The Configure Level dialog appears, with our modification and additions, as shown in Illustration 19.

Illustration 19: Building a Filter Expression for a (Newly Named) Level ...

25.  Click OK to accept the new filter definition, and to dismiss the Configure Level dialog.

The PivotDiagram updates, as before, and reflects the effects of the filter upon the members of the recently added level, as well as its modified name, as depicted in Illustration 20.

Illustration 20: The PivotDiagram Updates for the Newly Added Filter ...

Next, we will add the City level to our filtered State selection. But before going further, we will make an adjustment to the default Options settings for the PivotDiagram.

26.  Select both the Alabama and Georgia sub nodes on the canvas, by clicking Alabama, holding down the SHIFT key, and then clicking Georgia. (You can also “lasso” them to select both, after the manner used in many Windows applications.)

The outline surrounds the sub nodes as shown in Illustration 21.

Illustration 21: Selecting the Alabama and Georgia Sub Nodes ...

27.  Select PivotDiagram -> Options ... from the main menu, as depicted in Illustration 22.

Illustration 22: Select PivotDiagram -> Options ... from the Main Menu

The PivotDiagram Options dialog opens.

28.  In the Data options section (appearing in the lower half of the PivotDiagram Options dialog), uncheck the Limit items in each breakdown setting. (The default for this setting is “checked,” with the associated Maximum number of items specified as “20.”)

29.  Check the box to the immediate left of Import member properties, in the Data options section just above the Limit items in each breakdown setting we have just deactivated.

30.  In the Diagram options section (appearing in the upper half of the PivotDiagram Options dialog), uncheck the Show data legend setting.

The default for the Show data legend setting is “checked;” We are simply removing the legend for our present purposes. (In the real world, I often prefer customized legends for aesthetic, and other, reasons.)

31.  Substitute the current text in the Title box with the following, more descriptive expression:

SE  United States Subanalysis Path

The PivotDiagram Options dialog appears, with our modifications, as shown in Illustration 23.

Illustration 23: PivotDiagram Options Dialog with Modifications

Note the relative ease with which we are able to entrain member properties into the PivotDiagram, where they will appear with the next refresh, once we take the next step.

32.  Click OK to accept modifications and to dismiss the PivotDiagram Options dialog.

We return to the PivotDiagram, where the two State sub nodes remain selected.

33.  Ensuring that both the Alabama and Georgia sub nodes on the canvas are still selected, click Customer: City within the Add Category selection list of the task pane.

The PivotDiagram updates once again, this time reflecting the addition of the City level, as depicted in Illustration 24.

Illustration 24: The City Level Members Appear – “High Level” View

It hardly escapes notice that the PivotDiagram has exploded at the City level. A close inspection of the Customer Counts within the Cities, however, reveals that many of these contain zeros. This is to be expected because, according to management, Adventure Works has only recently begun selling in these areas. As this is the case, we might take this opportunity to make our PivotDiagram more compact; while we may not necessarily want to completely hide member Cities with no Customers, we can use another feature of the PivotDiagram to at least “consolidate” presentation for the affected Cities.

34.  Simultaneously “lasso” select the following City sub nodes (those that indicate zero Customers) that lie beneath the Alabama City level:

  • Florence
  • Huntsville
  • Mobile
  • Montgomery

35.  Select Other Actions within the PivotDiagram task pane.

36.  Select Merge from the items that appear within the dropdown selector, as shown in Illustration 25.

Illustration 25: Merging the Selected Group of Sub Nodes

The affected Cities are merged into a single sub node, as depicted in Illustration 26.

Illustration 26: The Alabama Cities without Customers Appear in a Merged Sub Node

37.  Perform the same steps for the Georgia City sub nodes that indicate zero Customers. (To select non-contiguous Cities, click on each, while depressing the CTRL key.)

The lowest set of sub nodes (children of the City level) within the current PivotDiagram appear as shown in Illustration 27.

Illustration 27: Merged Sub Nodes alongside Independent Cities with Customers ...

MS SQL Archives

Latest Forum Threads
MS SQL Forum
Topic By Replies Updated
SQL 2005: SSIS: Error using SQL Server credentials poverty 3 August 17th, 07:43 AM
Need help changing table contents nkawtg 1 August 17th, 03:02 AM
SQL Server Memory confifuration bhosalenarayan 2 August 14th, 05:33 AM
SQL Server Primary Key and a Unique Key katty.jonh 2 July 25th, 10:36 AM