Page Four

The order in which groups and subgroups are organized can be easily changed at anytime with a simple drag-and-drop procedure.

Figure 16: Reordering the nested groups.

1. Swap the positions of Salesperson & ProductName in the Group drop area by dragging and dropping one of the fields over the other. The data is instantly reorganized so that now the main group is ProductName and the subgroup is Salesperson.
2. Expand Jack's New England Clam Chowder, and then further expand Salesperson: Andrew Fuller.
3. Expand Sir Rodney's Scones, and then further expand Salesperson: Andrew Fuller. The line items displayed are still the same as before, only, the grouping order has changed.

Depending on the analyzing criteria, the order in which the data is grouped can be very important to properly interpreting and thoroughly understanding the information depicted.

For the instances where the desired filtering can not be accomplished visually, there is an advanced option in the E3 Wizard for filtering data fields using mathematical expressions. For this example we will say that we want to know all instances where customers paid 20% or more for Freight, relative to the ExtendedPrice ([Freight] / [Extended Price] >= 0.20).

Figure 17: Building and advanced filter in E3.

1. Select the E3 button on the toolbar to start the wizard.
2. On the first screen, select New Advanced Filter and then click on Next.
3. On the Field Properties screen, double-click on Freight in the Fields ist to begin the expression.
4. Press the "/" key to specify division.
5. Double-click on ExtendedPrice to add it to the expression.
6. Select the Keywords tab at the top left corner of the screen to reveal a list containing four categories of equation building tools.

7. Expand the Logic category and double-click on >= (greater than or equal to) to add it to the expression.
8. Type "0.20" to complete the expression. Errors in any part of the expression can be corrected using standard editing procedures.

9. To apply the filter select Finish. Once again, the number of records has been greatly reduced
10. Select the Expand All Groups button on the toolbar to display all line items.

A quick review of the remaining records confirms that for each of these transactions the amount charged for freight was indeed "equal to or greater than" 20% of the extended price.

Conditional highlights use visual indicators (colors and fonts) to draw attention to data in a field, which meet certain user-specified expression-based conditions.

Figure 18: A conditional highlight in action.

Draw from the last example: Looking over the results currently displayed in the grid, you may have noticed several sales transactions in which customers paid more for shipping then was paid for the actual items purchased. Concerned for the interests of our customers, we may want to perform some further investigations to determine how often this situation occurs. Therefore, we want to know all instances where customers paid 100% or more for Freight, relative to the ExtendedPrice ([Freight] / [Extended Price] >= 1.00). Notice that the expression is almost exactly the same as the one used in the previous example.

1. Select the E3 button on the toolbar to start the wizard.
2. Select New Conditional Highlight, name it "Over Price Freight", and click Next.
3. On the first Field Properties screen, switch to the Fields tab.
4. Double-click on Freight in the Fields list to begin the expression.
5. Press the "/" key to specify division.
6. Double-click on ExtendedPrice to add it to the expression.
7. Switch to the Keywords tab.
8. Expand the Logic category and double-click on ">=" (greater than or equal to) to add it to the expression.
9. Type "1.00" to complete the expression.
10. Select Next to proceed to the second Field Properties screen.
11. In the Cell Style section, select Vertical from the Gradient drop-down menu and Red from the Color End drop-down menu. The selected settings can be previewed at the bottom of the screen before applying them.
12. To apply the highlight select Finish.

The conditional highlight makes it easy to spot the transactions where the shipping costs were as much or more than the cost of goods sold.

Recheck Shippers.CompanyName in the Fields section of the View Properties panel to show the "offending" shippers. The corresponding column (field) is reinserted into the grid, making it possible to determine which shippers are responsible for the excessive freight costs. We could go even further to determine which shippers most often over charged, but that is beyond the scope of this demonstration.

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

Back to tutorials