Page Three

Up to this point, the data has been presented in a two-dimensional format; with the data in the grid being displayed by rows. If we place one of the current Row fields into the Column drop area, we will essentially create a multidimensional view that can greatly expand our perspective and understanding of the data.

Figure 10: The start of a multidimensional view.

1. Move Salesperson from the Row drop to the Column drop.

2. Drilldown on the ExtendedPrice for Ernst Handel, under Andrew Fuller ($10,703.63).

3. Click on the Invoice Pivot View tab to return to the Pivot View.

Additional fields can be added to any of the drop areas to further expand on the information that is displayed. Say we want to take our analysis a step further, to include listing specific products that the salespeople sold to the selected customers.

Figure 11: Filtering by product helps to minimize the record set.

1. Drag ProductName to the right side of Customer.CompanyName (2nd position) in the Row drop area.
2. Click on the drop-down arrow at the very right end of the ProductName header to display a list of all products in the database.
3. Remove the checkmark from the All box at the top of the list and select the following products:

Products
Aniseed Syrup Boston Crab Meat Chef Antons Cajun Seasoning Chef Anton's Gumbo Mix Filo Mix Grandma's Boysenberry Spread Jack's New England Clam Chowder	Laughing Lumberjack Lager Longlife Tofu Louisiana Fiery Hot Pepper Sauce Louisiana Hot Spiced Okra Northwoods Cranberry Sauce Outback Lager Perth Pasties	Sasquatch Ale Sir Rodney's Marmalade Sir Rodney's Scones Steeleye Stout Teatime Chocolate Biscuits Tofu Vegie-spread

4. Select the Filter button to add the visual filter to the existing filter.
5. Select the Collapse All button on the toolbar, once again.
6. Expand the Ernst Handel row and drill-down on any cell for Sir Rodney's Scones under Andrew Fuller. This time only 2 records are displayed in the drilldown, because the cell that was selected is based on just those specific records (narrower focus).
7. Tab to any of the previous drilldowns to compare records.
8. Show the ProductName by clicking the checkbox in the View Properties panel.
9. Select the Best Fit All button from the toolbar.

10. Click on the Invoice Pivot View tab to return to the Pivot View.

Conditional highlights in the Pivot View can be very helpful where an immediate visual reference is desired. Multiple highlights can be used simultaneously to visually separate and bring attention to two or more conditions. Likewise, multiple conditional highlights can also be used in a manner that compliments each other.

Figure 12: Conditionally highlighting any cell that has Discount >= 10%.

Suppose we want to see what discounts our sales people are giving to their customers. We could set up a conditional highlight that would flag all instances where a particular threshold has been met. For this example, we will start with 10% ([Discount] >= 0.10).

1. Select the E3 button on the toolbar to start the wizard.
2. Select New Conditional Highlight, name it "10 Percent Discount", and click Next.
3. On the first Field Properties screen, double-click on Discount in the Fields list to begin the expression.
4. Switch to the Keywords tab.
5. Expand the Logic category and double-click on ">=" (greater than or equal to) to add it to the expression.
6. Type "0.10" to complete the expression.
7. Select Next to proceed to the second Field Properties screen.
8. In the Field section, select Discount from the Name dropdown list. This indicates which field(s) will be highlighted.
9. In the Cell Style section, select Vertical from the Gradient drop-down menu and Yellow from the Color End drop-down menu. The selected settings can be previewed at the bottom of the screen before applying them.
10. To apply the highlight select Finish.

The new conditional highlight is listed in the Highlights section of the View Properties panel. Browse around the grid to confirm that the Discount cell is highlighted for every instance where the discount is greater than or equal to 10 percent.

Again, these are summary figures; in reality, we would have to drill-down to the underlying records to see the actual discounts given per sales transaction. This example is merely intended to illustrate the flexibility and functionality of conditional highlights.

Now, say we want an additional highlight to flag any instance where a salesperson gave a discount of 15% or greater ([Discount] >= 0.15). We need to keep in mind the potential conflict with the previous condition, since 15% is greater than 10%.

Figure 13: Adding a conditional highlight for Discount >= 15%.

1. Select the E3 button on the toolbar to start the wizard.
2. Select New Conditional Highlight, name it "15 Percent Discount", and click Next.
3. On the first Field Properties screen, switch to the Fields tab.
4. Double-click on Discount in the Fields list to begin the expression.
5. Switch to the Keywords tab.
6. Expand the Logic category and double-click on ">=" (greater than or equal to) to add it to the expression.
7. Type "0.15" to complete the expression.
8. Select Next to proceed to the second Field Properties screen.
9. In the Field section, select Discount from the Name dropdown list.
10. In the Cell Style section, select Vertical from the Gradient drop-down menu and Orange from the Color End drop-down menu.
11. To apply the highlight select Finish.

The new conditional highlight is listed in the Highlights section (it also has a checkmark in the Show box), yet it has no effect on the cells in the grid where the Discount is obviously greater than or equal to 15 percent. The reason for this is that conditional highlights are applied and prioritized in the order in which they are created. In this case, the "10 Percent Discount" highlight was created before the "15 Percent Discount" highlight. Therefore, the "10 Percent Discount" highlight has priority, and subsequently, overrides the "15 Percent Discount" highlight. Fortunately, the priority of conditional highlights can be easily changed at any time.

1. Click on the "15 Percent Discount" name in the Highlights section of the View Properties.
2. Select on the up arrow located in the bottom right corner of the Highlights section.

Now that the "15 Percent Discount" highlight has priority, we can immediately see that the anticipated cells are properly highlighted in an orange gradient. Conveniently, the cells in which the discount is greater than or equal to 10 percent, but less than 15 percent, are still highlighted in yellow. This illustrates how multiple conditional highlights can be manipulated to compliment each other.

We will create one more conditional highlight to reinforce the previous concepts, as well as illustrate the ease of editing a highlight. Continuing with our previous scenario, say that we want to highlight in red any instances where our salespeople are giving customers 20 percent or greater discounts ([Discount] >= 0.20).

Figure 14: Adding a conditional highlight for Discount >= 20%.

1. Select the E3 button on the toolbar to start the wizard.
2. Select New Conditional Highlight, name it "20 Percent Discount", and click Next.
3. On the first Field Properties screen, switch to the Fields tab.
4. Double-click on Discount in the Fields list to begin the expression.
5. Switch to the Keywords tab.
6. Expand the Logic category and double-click on ">=" (greater than or equal to) to add it to the expression.
7. Type "0.20" to complete the expression.
8. Select Next to proceed to the second Field Properties screen.
9. In the Cell Style section, select Horizontal from the Gradient drop-down menu and Green from the Color End drop-down menu.

10. To apply the highlight select Finish.

This time, the new conditional highlight is immediately visible in the grid, even though it has the least priority. Because we left the Name drop-down set to "All" in the Fields section of the wizard, the new highlight is still applied to all cells that do not conflict with preceding conditional highlights. This is just one simple example that illustrates how conditional highlights can be combined to visually identify multiple data values of interest.

We will say that we did not really mean to highlight "All" fields on the last conditional highlight that we created.

Figure 15: Setting the conditional highlight priority.

1. Click on the corresponding Edit button in the Highlights section to modify the 20 Percent Discount.
2. Select Next to continue to the next screen.
3. In the Field section, select Discount from the Name dropdown list.
4. To apply the changes select Finish.

Move the "20 Percent Discount" highlight to the top of the Highlights section, so that it has top priority:

1. Select 20 Percent Discount and then click on the up arrow button.
2. Repeat the previous step.

This concludes the Pivot View section of the tutorial. You now should have a good enough understanding of the Pivot to begin comfortably exploring the rest of the Pivot interface.

Back to tutorials