Now, we will look at the object components of the Inventory dashboard. When we click on the white area of our sheet object, we can see that again we have a container object, but this container only contains text and lines and two list boxes, one for the fiscal year and one for the fiscal month. What about the charts? Also, what is that looking like a complicated list box in the right-hand side quadrant?
When we open the properties of the object in the upper-right-hand side corner, the Inventory On Hand Report, we can see that the object is actually a Pivot Table. We have not worked with a Pivot Table object before:
Figure 7-5: Closeup of the Pivot Table icon on the General tab
Since this is the first time that we have worked with a Pivot Table, open Properties by right-clicking and choosing Properties in the context menu so that we can see what is unique about a Pivot Table object.
On the Dimensions tab, we can see that we are using three Dimensions:
- Product Group Desc
- Product Type Desc
- Product Sub Group Desc
These correspond to the three columns of text data in the Pivot Table:
On the Expression tab, we have three expressions that correspond with the three columns of numbers in the Inventory On Hand Report Pivot Table:
- Inventory Units
- Inventory Value
- Inventory Turns
On the Sort tab, we notice that Product Group, the first on the list, is set to sort by STATE ascending and then Text A-> Z, which means Product Group will first sort at that level by the first measure value and then alphabetically.
The next one is the Presentation tab. There are settings on the Presentation tab that we need to pay attention to in order to understand what the Pivot Table is doing. When we have Product Group highlighted, we can see that we also have the following status:
- The checkbox for Partial Sum is checked—this tells the Pivot Table to display our measures when subgroups are included.
- The checkbox for Allow Pivoting is checked—this option must be selected to allow the usual pivoting function of a pivot table.
- Use the checkbox for Vertical Text on Column Labels to rotate the column header to a vertical orientation. In this case, it is not checked.
- The checkbox for Selection Indicators is checked, and this causes a colored indicator to display in the header of any field dimension where a selection has been made—a small green-filled circle. This is shown in the following figure:
Figure 7-6: The small green-filled dot next to the selected dimensions
- The checkbox for Always Fully Expanded disallows the collapse of dimensions by clicking on the minus icons and is not used in this Pivot Table.
- The checkbox for Suppress Expansion Icons in Print suppresses the plus icon (+) and minus (-) icon for expand and collapse visibility when printing the sheet object. It is not selected in this Pivot Table.
- The checkbox for Suppress Zero-Values eliminates columns or rows that contain only zeros from the table display. We are using zero suppression for our display.
- The checkbox for Suppress Missing eliminates columns or rows that are empty from the table display. They are visible but grayed out and will not let us change the setting.
- The checkbox for Populate Missing Cells maps cells in cross tables representing missing combinations of dimensions to a regular null value, allowing us to use expression testing for null. This is in use in this Pivot Table.
- The Null Symbol and Missing Symbol text boxes allow us to enter the preferred display symbol for null and missing data.
Figure 7-7: Presentation tab of the Pivot Table Properties wizard
On the right-hand side, the Subtotals group of radio buttons is used to set the display of totals and subtotals in the Inventory On Hand Report Pivot Table. The Multiline Settings section tells the Pivot Table whether or not to wrap text within the groupings.
On the lower-left-hand side quadrant, we have the Inventory Profile Chart. Its colors are consistent with the other charts on the sheet. Therefore, at first glance, we would think that the orange bars would be Inventory Turns, but they actually represent a count of items. Now, we will examine how this range is created for the two measures represented by the orange-colored and the blue-colored bars.
When we right-click and choose Properties for the Inventory Profile Chart, we see that our dimension is Class Turns. If we close Properties, right-click on the white space to bring up the context menu, and choose New Sheet Object and the object type of List Box. With the selection of Class Turns, we can see that the Class Turns dimension already has the ranges built-in the following screenshot:
Figure 7-8: List Box of Class Turns members
There is not much to learn about the Class Turns dimension except that we can create our own lists and then turn around and use them in dimensions. Actually, the only item of interest that shows us something new under Inventory Profile Properties is the formula for Number of Items found on the Expressions tab. The formula is:
Count(Distinct [Item-Branch Key])
This is interesting because it shows us how to create a Count Distinct formula to get the count of unique items in a set.