Adding a Pivot Table allows you to display summary data with the ability to filter the row or column data. This can be especially useful to view collections of performance data in one visual display and change the way it is represented while automatically calculating the changes.
The exercises used are based on a testing database. You will need to adjust your values of your entries (when applicable) to accommodate the data in your database. Results displayed will differ than those in the exercises.
-
Select the appropriate Visualizer with the left mouse click and drag it to the desired location on the Display Grid. You will see a small plus sign next to your cursor when dragging the Visualizer. For this example, the first Visualizer is Pivot Table.
-
The Visualizer will appear with sizing squares. The sizing squares allow you to adjust the size of the Visualizer. Select the square and drag to the appropriate size.
The center sizing square is used to move the entire Visualizer around on the Designer Grid. The cursor will turn to a 4-way arrow. Be sure to make enough room and adjust the size of the Pivot Grid to accommodate the information.
- After you have the Visualizer in the appropriate location, you can utilize the Properties area to make changes to the Pivot Grid. You can make changes to the following:
- Initially Expanded - This feature can be toggled between True (Expanded) and False (Not Expanded). Set at True for this example.
-
Pivot Columns - This feature allows you to set the Pivot Columns to be displayed and what type of value. See below for detailed use.
The Pivot Columns change and are directly dependent on the Query chosen. Each Query has different columns to select from.
- Query - This feature allows you to select the Query that provides the selectable Pivot Columns. See below for detailed use.
- Query Parameter Value - Provides the available values for Look up Types. Query Parameters are directly related to the specific Query chosen. See detailed used below.
- Title - Enter an appropriate Title for the Metric. The Title will show up above the Metric as a heading. For this example, enter Expense Activities.
-
Click on the ellipses button in the Query field and select an appropriate Query. For this example, select Expense Activity Query.
-
Click the OK button.
Click here to learn more about Queries.
- Click on the ellipses button in the Query Parameter Value field.
-
Review the parameters available. You can make selections for specific entries to filter (narrow) the information displayed. For this example we will accept the defaults.
- Click the OK button.
-
Return to the Pivot Columns field and click on the ellipses button to display the Pivot Column Collection Editor.
- The next decision is to decide which of the columns are to be used in the Pivot Grid. By default, each Member is set as False on Visibility. This means the column will not be displayed. For this example, set the following Visibility to 'True':
- Expense_Expenses_Code
- Expenses_Amount
- Expense_Billed_Amount
- Employee_Name
- Expenses_Status
- Each of the Columns also contains an Area field. This allows you to select where you want the Column to be placed in the Pivot Grid. Row/Area is set by default. Leave the Area at the default, Row Area. Other selections include:
- Column Area
- Filter Area
-
Data Area
You can select placement of the Area after publishing the Pivot Grid by dragging and dropping the columns into the separate areas.
- You also have the option to change the label Display Name of the specific column. This is an option that changes what is displayed on the Dashboard. Leave the default for this exercise.
- On each of the Summary Type, the default is set to Count. Depending on the type of data contained in the column AND the way you want it expressed, you can make a selection from the list. Below are the listed types. Leave the default for this example.
- Count - Displays the number of records.
- Sum - Sums the column values.
- Min - Displays the minimum value of the column data.
- Max - Displays the maximum value of the column data.
- Average - Displays the average of the column values.
- StdDev - The standard deviation is the measure of the dispersion of a collection of numbers. If you take the mean of a data value, are the values closely clustered around the mean or are they spread more widely. The smaller the std dev the more closely clustered the numbers.
- StdDevP - Calculates the standard variation based on the entire population rather than a sample of the population (StdDev).
- Var - Value at risk for sample population.
- VarP - Value at risk for entire population.
-
Custom - Not used or supported.
You can see at this point that there is a multitude combinations that can be utilized to create a Pivot Grid. Be sure to refer to your initial plan on what information is needed in the Pivot Grid.
-
Review the Properties and click the Save button in the Dashboard tool bar.
-
Click the Preview Tab to actively display the Pivot Grid with data.
-
Notice that the Pivot Grid is composed of two pages, with navigation links both above and below the data area. Hovering over the Pivot Grid reveals the parameters used. There are three areas to drag and drop functionality. This allows you to select a column header and move it to the location to execute the area function. For example, drag and drop the Expenses_Expense_Code column to the Drop Filter Fields Here area.
Small white arrows appear as indicators of placement for moving columns within the Pivot Grid.
-
Using the combo box arrow in the moved column, you can toggle specific Expense Codes to be displayed in the Pivot Grid.
When making changes, the browser page will refresh to execute the changes made. This may require to you scroll the dashboard page depending on the location of the Visualizer.
- Click the OK button once you have made the changes.
- The changes will be reflected in the Pivot Grid after the refresh. You can move the column back by performing the same actions. The Data area and Column Fields area also function the same way, allowing you to filter and group the Pivot Grid information to your customized needs.
Summary
The Pivot Grid visualizer offers extensive flexibility in representing database information. Flexible filtering and sorting features and providing robust summary types presents a powerful tool for firm performance measurement. Preparation and design will markedly help reduce the publishing process.