Volume 2: Solutions and Publishing disclaimer

Create the pivot diagram in Visio

1. 
   Start Visio. Create a new pivot diagram from the template File > New > Business > Pivot Diagram. This creates a new drawing and starts the Data Selector wizard.
   
2. 
   Because our data is in Excel format, choose Microsoft Office Excel workbook on the first page of the wizard.
   
3. 
   On the next page of the wizard browse to the location of the Excel data file. Choose Next.
   
4. 
   On the next page of the wizard specify the worksheet or range that you want to use. This is ‘Team Hitting$’ which is derived from the name of the worksheet in our data file.
   
5. 
   The first row of our data contains header names for each column of data. Make sure the check box on this page of the window is checked. Choose Next.
   
6. 
   On the next page you can filter the data if desired. Choose Select Columns. To filter the data, uncheck any columns that are not needed. Uncheck the column named ID. For this demo we will accept all of the data. Choose OK.
   
7. 
   Choose Next and Finish.
   
8. 
   The pivot diagram shown below is created.
   

9. 
   The three elements displayed include the name of the pivot diagram, the top level pivot node, and the data legend.
   
10. 
    In the top level pivot node, note that Games is the default field displayed. The value 4858 is the total number of games played by all of the teams in our data file. Visio chooses the first numeric column it finds (a column that it can total) to be the default column displayed. We’ll change this in a moment.
    

Pivot diagram basics

1. 
   Check that the Pivot Diagram window is showing. If it is not select Pivot Diagram > Show/Hide > Window.
   
2. 
   Select the top level pivot node. In the Pivot Diagram window under the Add Category choose League. This causes the selected node to have its data broken down by League.
   
3. 
   The diagram now appears as follows. Note that a Breakdown shape has been added labeled League. Also, another level of breakdown shapes have been added that breaks the data down for all AL and NL teams.
   

4. 
   The number of games for each league is different. Since all teams in baseball play the same number of games, this must mean there are a different number of teams in each league. In the Pivot Diagram window under Add Total, scroll down and mark the check box for Count. The Count field was automatically added to the Pivot data. It contains the number of records that make up each node. Since there is one record per team within this data set, the value of Count tells us how many teams are in each league.
   

Which team had the highest team batting average in baseball in 2006?

Now let’s start analyzing the data and find answers to the questions posed at the beginning of this demonstration.

1. 
   Right click the top level node and choose Add Category > Average. This is equivalent to choosing Average from Add Category in the Pivot Diagram window.
   
2. 
   This displays some nodes with the value for Average at the top of the node, but it doesn’t display a node for all 30 teams! Where is my data?
   
3. 
   Right-click the breakdown shape called Average and choose Sort. The Breakdown Options dialog is displayed. Since we want to find the team with the highest average choose Descending. Under Show note the checkbox “Limit items in this breakdown”. The default is for this to be checked and to have a value of 10. This is why we didn’t see a node for each team earlier. Since we only want to find the team with the highest average, we don’t need to see the data for all of the teams. Set the limit to a small value, say 3. This will tell us the top three teams and also allow us to check that the sorting direction is set correctly.
   

4. 
   The top three teams hit .287, .285, and .284 respectively. But what is the name of the team?
   
5. 
   Multi select the three nodes, right-click and choose Team.
   
6. 
   You can break down multiple shapes at one time! Now we have our top three teams: Minnesota Twins, New York Yankees, and Toronto Blue Jays.
   

Was the AL or NL the best hitting league that year?

To answer this question we need to accumulate the team batting averages by league.

1. 
   Before we start, go to the Pivot Diagram window and under Add Total check Average(Sum).
   
2. 
   Note that Average is now displayed within each node. At the team node level the value represents the value for one team as shown below.
   

However, at the top level node the average is the sum of the averages of all of the nodes beneath it. For an average, this not very helpful, but we’ll see how to fix this in a moment.

3. 
   Right-click the top level node and choose League.
   

4. 
   What does this tell us? Unfortunately, not much! If the number of teams in each league were the same we could say that the NL has a higher average, but we still don’t know what that average is. The number we really want to see is the average of the averages. Mathematically this may not be the best calculation, but for our purposes will be very close and from this we will be able to answer our questions.
   
5. 
   Look through the Pivot Diagram window and browse the Add Total section. Find Average(Sum), hover over it with the mouse and note that there is a drop down associated with it. From the drop down choose Avg. This adds to our pivot diagram the average of the averages as seen below.
   

Now we can answer the question as to which was the better hitting league. The AL had the higher team average at 0.274 as compared to the NL average of 0.264.

Display the batting average as a data bar

Let’s use the Data Graphics features of Visio to display the batting average as a data bar.

1. 
   If the Data Graphics window is not displayed, choose Data > Display Data on Shapes to turn it on.
   
2. 
   Within the Data Graphics window is displayed all of the data graphics that are part of the currently active drawing. We will modify the one being used on this page. If you can’t tell which is being used then right-click a Data Graphic in the Data Graphics window and choose Select Shapes that use this Graphic. This selects the shapes in the pivot diagram that have this graphic assigned to them.
   
3. 
   Once the correct data graphic has been found, right-click the data graphic and choose Edit Data Graphic. The Edit Data Graphic dialog is displayed.
   

4. 
   Choose New Item and Data Bar. The New Data Bar dialog is displayed. We will use this item to display our calculated average.
   

5. 
   Set Maximum Value to 1, since the value for average should be a number between 0 and 1.
   
6. 
   Set Label to Batting Average.
   
7. 
   Click in the Value Format and then click on the ... button. This displays the Data Format dialog. Set the category to Number and set the number of decimal places to 3.
   
8. 
   Under Data Fields choose Average(Avg). Choose OK to close the Edit Data Bar dialog.
   
9. 
   In the Edit Data Graphic dialog select the data graphic that is displaying Text for the data Average(Avg). Choose Delete. Choose OK again to close the Edit Data Graphic dialog.
   
10. 
    If all is successful, the diagram should update with the modified data graphic as follows.
    

Who were the top 5 run producing teams in baseball?

After what we’ve learned so far, you should have a pretty good idea about how to answer this.

1. 
   Right-click the top level node and choose Team.
   
2. 
   In the Pivot Diagram window under Add Total, uncheck Games, Average, and Count.
   
3. 
   Note that the calculation for Batting Average also disappears since it is dependent upon the values Average.
   
4. 
   Check Runs(Sum). The only field shown in the Data Graphic now is Runs.
   
5. 
   Right-click the Team breakdown shape and choose Sort. Choose the Sort by field to be Runs(Sum), choose Descending, and set the number of nodes to display to 5.
   
6. 
   The top run producing teams are: New York Yankees(930), Cleveland Indians(870), Chicago White Sox(868), Philadelphia Phillies(865), and Atlanta Braves(849).
   

Is there a correlation between the top home run hitting teams and the teams with the highest number of runs scored?

In other words, does hitting more home runs generally produce more runs? After all, it is runs scored that win games, not the number of home runs hit!

To answer this question we will find the top five Home Run teams in all of baseball and compare those five teams against the five found earlier to see how many are in common.

We could just make a different pivot on the data we already have, but the comparison would be easier if we can display the two results side-by-side.

1. 
   Within the same drawing choose Insert > Pivot Diagram and insert the data from the same data file. Visio creates a new top level pivot node and automatically places it in some empty space on the drawing page. Note that the pivot label is 'Team Hitting$'-2 or something similar.
   
2. 
   If it is not there already, move the new pivot node to the right hand side of the drawing. This will help with the final layout later.
   

The new pivot node will have its own default data graphic and the fields selected by using Add Totals in the Pivot Diagram window are independent of the fields chosen for the first instance of this data.

3. 
   Make sure that the newly inserted Pivot node is selected. In the Pivot Diagram window, under Add Totals, turn off any checked boxes and turn on HR(Sum). The only field shown in the data graphic should now be HR.
   
4. 
   Right-click the top level node and choose Team.
   
5. 
   Right-click the Team breakdown shape and choose Sort. Sort by Runs(Sum) and select Descending and limit the display to 5 nodes.
   
6. 
   We now have the top five home run hitting teams identified. They are: Chicago White Sox(236), Atlanta Braves(222), Cincinnati Reds(217), Philadelphia Phillies(216), and New York Yankees(210).
   
7. 
   Now we have all of the data. Let’s use the layout tools to put the data visually side-by-side.
   
8. 
   Select the top level node for 'Team Hitting$'. Choose PivotDiagram > Layout Direction > Left-to-Right.
   
9. 
   Select the top level node for 'Team Hitting$'-2. Choose PivotDiagram > Layout Direction > Right-to-Left.
   
10. 
    In this view it is now easier to see that 4 of the 5 top run producing teams are also in the top five in hitting home runs.
    

Displaying a calculated field in the pivot diagram

Sometimes you may want to display a calculated field as part of the data in the pivot diagram. This can be done with Data Graphics!

Let’s display a bar chart showing how a team’s hits break down between singles, doubles, triples, and home runs. A close look at the data reveals that the number of singles is not included as a separate item in the data, but it can be derived by subtracting the number of doubles, triples, and home runs from the total hits.

1. 
   Collapse the pivot diagram to a single node.
   
2. 
   In the Pivot Diagram window, under Add Total, turn on Hits and turn off any other displayed data.
   
3. 
   In the data graphics window select the data graphic being used for this pivot diagram and choose Edit Data Graphic.
   
4. 
   From the Edit Data Graphic dialog, select New Item and then choose Data Bar from the drop down list.
   
5. 
   From the New Data Bar dialog set Callout to Multi-bar graph.
   
6. 
   From the New Data Bar dialog in Data field, scroll to the bottom and choose More fields. The Field dialog is displayed. Select Custom Formula and then enter the following as the custom formula:
   

={Hits}-{2B}-{3B}-{HR}

7. 
   Choose OK to close the dialog.
   

8. 
   From the New Data Bar dialog set the following values:
   

Max=1200

Value Position=Interior-Rotated

Label Position=Bottom

Label 1=S

Field 2=2B

Label 2=2B

Field 3=3B

Label 3=3B

Field 4=HR

Label 4=HR

9. 
   Choose OK to close the dialog.
   

A portion of the pivot diagram is shown below. The data displayed in the first bar labeled “S” for single is the calculated value.

Exploring Drill-down

Create a PivotDiagram

To create a PivotDiagram, you can use the PivotDiagram template, or you can insert a Pivot Node shape into any Microsoft Office Visio Professional 2007 diagram.

When you create a PivotDiagram, the Data Selector Wizard guides you to point to a data source. The data is then imported into a top PivotDiagram node, which is displayed in your drawing. You then expand the top node to show the data that you want to analyze.

Note:

It is a good idea to make sure that the top cell in each column in your Excel worksheet is a column heading rather than a row of data. Headings aren't required, but they make the completed PivotDiagram much easier to understand.

Try it!

1. 
   Choose File > New > Business > PivotDiagram.
   
2. 
   Follow the steps in the Data Selector Wizard.
   
   1. 
      Choose Microsoft Office Excel Workbook.
      
   2. 
      On the next screen, choose Browse and traverse to …/Samples/Sales data.xls
      
   3. 
      Choose Finish.
      

After you click Finish, the following three shapes appear on the drawing page:

1. 
   A data legend containing information about the data source
   
2. 
   A text box for the name of the PivotDiagram
   
3. 
   The top node, which contains the imported data set
   

3. 
   Click the top node on the drawing page, and in the PivotDiagram window, under Add Category, click the category (usually a column in your data source) by which you want to group your data.
   

Note:

You may need to maximize the PivotDiagram window to see all of the features.

To further expand the new subnodes to expose other levels, click the node that you want to expand and repeat step 3.

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 all of the PivotDiagrams in a drawing, in the PivotDiagram window, under Actions, click Other Actions, and then click Refresh All.

To refresh only one PivotDiagram, select it in the drawing. In the PivotDiagram window, under Actions, click Refresh Data.

Try it!

1. 
   Open the file Sales data.xls. This is the file containing the data used to create the pivot diagram.
   
2. 
   Modify the data by adding additional records or changing some values.
   
3. 
   In Visio, refresh the data and note the updates in the Pivot Diagram.
   

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 third quarter is no longer of interest, you can conceal the Qtr3 node:

Try it!

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

After closing the dialog, only Qtr4 data is presented in the drawing since this is the only data that meets the criteria chosen.

Note:

Any filters that have been created are displayed in the data legend shape.

3. 
   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.

Try it!

1. 
   Select any node of the PivotDiagram.
   
2. 
   In the PivotDiagram window, under Add Total, select or clear the check boxes for the data that you want to show or hide.
   
3. 
   In the PivotDiagram window, under Add Total, right-click Sales and choose Avg. Average sales is now displayed on the pivot nodes. Note that this column can also be configured. Change the configuration so that only sales greater than $600 are displayed.
   

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.

Try it!

In the PivotDiagram window, under Add Total, right-click Sales and choose Avg. Average sales is now displayed on the pivot nodes. Note that this column can also be configured. Change the configuration so that only sales greater than $600 are displayed.

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

Try it!

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

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.

Try it!

1. 
   Create a new pivot diagram.
   
2. 
   Import the data from the Northwind database in the Samples directory.
   
3. 
   When prompted for “What table do you want to import?”, choose Product Sales for 1997.
   
4. 
   Drill into the pivot diagram to display some data. For example, on the first level display CategoryName and on the second level display ShippedQuarter.
   

Limit the number of nodes displayed in all levels

Try it!

1. 
   Click the top node of the PivotDiagram that you want to change.
   
2. 
   On the PivotDiagram menu, click Options.
   
3. 
   In the PivotDiagram Options dialog box, under Data options, select the Limit items in each breakdown check box.
   
4. 
   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

Note:

The Visio Help file says this works for a single level, but experimentation shows that changing the number of nodes displayed is not limited to a single level, but instead is applied to all levels below the selected shape.

Note:

Selecting a Breakdown shape or selecting the pivot node just above it and then changing the number of nodes to be displayed are equivalent.

Try it!

1. 
   In your diagram, select the Breakdown shape of the level that you want to limit.
   
2. 
   On the PivotDiagram menu, click Sort.
   
3. 
   In the Breakdown Options dialog box, under Show, select the Limit items in this breakdown check box.
   
4. 
   In the number box, type the maximum number of nodes that you want to show.
   

Note:

The number of nodes displayed cannot be less than 2.

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

PivotDiagram window

The PivotDiagram window appears when you create a PivotDiagram or when you insert a PivotDiagram into any Microsoft Office Visio Professional 2007 drawing.

Add Category

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.

Try it!

1. 
   Select a pivot node shape in the drawing. In the Add Category window right-click ShippedQuarter and choose Select All. This selects all pivot node shapes displaying ShippedQuater data, regardless of which shape was initially selected.
   

Edit Data Graphic  This option opens 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).

Add Total

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, the Configure Dimension dialog box), where you can rename the column and 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).

Pivot Diagram ribbon tab

Pivot Diagram menus are separated into six groups on the ribbon



Data, Format, and Layout groups of Pivot Diagram ribbon


Arrange, Sort & Filter, and Show/Hide groups of Pivot Diagram ribbon

Data group:

Refresh  Refresh the data in an existing PivotDiagram. To choose which diagram to refresh, select the shapes in the pivot diagram .

Refresh All  Use this list item to refresh data for all PivotDiagrams in the drawing.

Change Data Source  Displays the Data Selector dialog where a different data source can linked.

Data Options  Use this list item to open the PivotDiagram Options dialog where you can choose what information about the diagram and how many items in each breakdown will show, change automatic refresh settings, and change the data source. The dialog box is shown below.



The PivotDiagram Options dialog

Format group:

Apply Shape  When you create a PivotDiagram in Microsoft Office Visio Professional 2007, each node appears as a rectangle. You can add a more visually interesting shape to the node from any open stencil.

Edit Data Graphic  This command opens the Edit Data Graphic dialog where you can change the placement and style of the data in your shapes.

Note:

A Data Graphic can be assigned to multiple shapes, so when the Data Graphic is modified, all shapes that use that Data Graphic are updated .

Layout group:

Re-layout All  Use this list item to refresh the layout after adding or removing nodes. The Re-layout can also be set to automatic.

Layout Direction enables the change in direction of the layout of the pivot diagram. The following options are available:

• 
  Top-to-Bottom (default)
  
• 
  Bottom-to-Top
  
• 
  Left-to-Right
  
• 
  Right-to-Left
  

Layout Alignment enables the change in alignment of the layout of the pivot diagram.

The following options are available when Top-to-Bottom or Bottom-to-Top Direction is chosen:

• 
  Left (default)
  
• 
  Center
  
• 
  Right
  

The following options are available when Left-to-Right or Right-to-Left Direction is chosen:

• 
  Top
  
• 
  Middle
  
• 
  Bottom
  

Arrange group:

Move Left/Up shifts the selected pivot node left for nodes laid out horizontally or up for nodes laid out vertically

Move Right/Down shifts the selected pivot node right for nodes laid out horizontally or down for nodes laid out vertically

Collapse  Use this list item to merge child nodes back into their parent node. This command operates only on the selected shape. To reopen the node, select the shape and choose the category to be displayed.
Promote  To show only part of an expanded PivotDiagram, you can promote a child node to the top node. This will be covered in more detail in a later section.

Merge  This list item combines data from multiple nodes into one. To merge nodes, select them and, on the PivotDiagram menu, click Merge. For example, when displaying sales data for products, display the top selling products separately and Merge the remainder into a single node.

Unmerge  To separate merged nodes, click the merged node and, on the PivotDiagram menu, click Unmerge.

Note:

A merged node has multiple entries in its title bar.

Sort & Filter group:

Sort & Filter  You can use the Sort feature to change the default order of the subnodes in a PivotDiagram. This is very useful if you want to more prominently display nodes maximum values. For example, if sales data is presented by sales person, the sales person with the most sales can be listed first.

Filter  This is the same as choosing Configure Column from the Add Category section of the Pivot Diagram window.

Show/Hide group:

Show Window toggles the display of the Pivot Diagram window.

Title toggles the display of the title of the data source for the Pivot Diagram

Data Legend toggles the display of the Data Legend shape which shows information about the data source and which filters are currently applied

Breakdown Shapes toggles the display of the breakdown shapes which are positioned between a parent node and the children of that node

Collapsing and Merging nodes

Techniques such as Collapse, Merge, and Delete are ways to change how the data is displayed in a pivot diagram. None of these techniques are destructive to the data itself. For example, if a pivot node is deleted (other than the topmost node), it can be easily regenerated from its parent again by displaying its category.

Combine PivotDiagram nodes using Merge

You can combine the data from multiple nodes into a single node in your PivotDiagram.

For example, let's say that you have quarterly sales data broken down by salesperson, but 3 of your 10 salespeople have been with the company for only a few weeks. It is too soon to compare the new salespeople to the more experienced staff, so you decide to combine their nodes into one.

1. 
   Hold down the SHIFT key and select the nodes that you want to combine.
   
2. 
   On the PivotDiagram tab, click Merge.
   

To separate the nodes again, click the merged node and, on the PivotDiagram tab, click Unmerge.

Note:

A merged node has multiple entries in its title bar.

A merged node does not persist if the parent is collapsed and then reopened or if the merged node is deleted and then the parent reopened.

Other techniques for combining/collapsing nodes

1. 
   Use the Collapse command to close all child nodes for the selected shape.
   
2. 
   Any node can be deleted from the display simply by selecting the node and choosing Delete. Selecting a node and deleting it removes both the node and all of its children.
   
3. 
   Use Configure Column to filter data from being displayed. The filter will have to be removed in order to see the data again.
   

Promote Nodes to Root

To show only part of an expanded PivotDiagram, you can promote a child node to the top node. The promotion process applies filters that hide the data above the promoted node.

For example, let's say your PivotDiagram shows the sales data broken down by financial quarter, with your fourth-quarter data broken down by salesperson, but you need to focus on the wide difference between the sales made by two different salespeople. You can promote the node for the fourth quarter so all the nodes above it are hidden.

Promote a node to top node

In your drawing, click the node that you want to promote and, on the PivotDiagram tab, click Promote.

The filters that were applied to promote the child node appear in the data legend. Only the promoted node and its children are now visible in the PivotDiagram.

Revert to original top node

You can revert to the original top node by removing the filters that were applied.

1. 
   In the PivotDiagram window, under Add Category, right-click the category that you want to remove the filters from, and then click Configure Column. Right-click the categories that have filters applied to them. These categories are listed in the data legend shape in your drawing. If you don't see the data legend shape, expand the Data group in the PivotDiagram tab. In the PivotDiagram Options dialog box, select the Show data legend and Show applied filters check boxes.
   
2. 
   In the Configure Column dialog box, under Filter, under Show data where category name, click the first column in each row, and then click (Select Operation).
   

Lab 2.4: Pivot Diagram – Filter a Sales database to analyze business information

The lab provides an in depth look at working with pivot diagrams and should take about 45 minutes.

Scenario:

You have a meeting in 45 minutes and your boss asks you to review the past years sales data and asks that you present the following data:

1. 
   What are the annual sales by category? Are there any insignificant contributors?
   
2. 
   Which were the top 5 selling products for the year?
   
3. 
   Did their sales increase or decrease from quarter to quarter?
   
4. 
   Which of these five had the best 4^th quarter? (We want to show just these five in the diagram and sorted within the 4^th quarter.)
   
5. 
   Which were the bottom 5 selling products for the year?
   
6. 
   Did their sales increase or decrease from quarter to quarter?
   
7. 
   Do category sales change much from quarter to quarter?
   

Approach for answering each question:

Create a pivot diagram from the data source Northwind.mdb in the Labs folder. For the Table choose Product Sales for 1997.

From the Pivot Diagram window, under Add Totals, make sure that Product Sales is checked.

1. 
   From the top level pivot node, pivot on CategoryName. The sales data is displayed in the pivot nodes.
   
2. 
   From the top level pivot node, pivot on ProductName. Sort the results to find the top five.
   
3. 
   Merge these five products together.
   

Promote them. This filters all other data out of the diagram.

Now break the data down by product and by quarter. Experiment with the layout features to make the data more presentable.

4. 
   Sort the data for the 4th quarter to the best selling of the top five products for that quarter.
   

Look at the Legend shape to identify the filters that were applied when the data was Promoted.

Remove all filters. In the Pivot Diagram window right-click ProductName and choose Configure Column

5. 
   From the top level pivot node, pivot on ProductName. Sort the results to find the bottom five.
   
6. 
   Merge the bottom five together. Delete the other pivot nodes at the ProductName level. You should now have the top level node and one merged node for the bottom five products at the ProductName level.
   

Is this equivalent to the Merge and Promote process we did earlier? Right-click the merged node and pick ShippedQuarter. Do we get just the data for the five lowest selling products?

No, because while deleting a node takes that image out of the picture, the data is still part of the pivot data so subsequent operations may bring it back. When we did step 3, the promotion process applied a filter, which stays in effect until removed.

7. 
   From the top level pivot node, pivot on CategoryName. Break each category down by quarter.
   

Directory: download
download -> Performance Tuning Guidelines for Windows Server 2008 May 20, 2009 Abstract
download -> Northern England’s set-jetting locations
download -> Bbc archives: Beyond the programme download
download -> Occupational health and safety
download -> Dynatest 3031 lwd light Weight Deflectometer
download -> Forth coming competitive exams
download -> English Olympiad Tasks: 10 th form Аудіювання, 10 клас. Текст
download -> Brush High School Morning Announcements Friday, September 05, 2014 all school
download -> Year 9 The Arts — Music
download -> Years 7 and 8 standard elaborations — Australian Curriculum: Music draft

Download 0.72 Mb.

Share with your friends: