Creating Pivots with Numeric Data

A List Report can be pivoted and then displayed as either a table or chart. Pivot charts and pivot tables summarize published data visually in the Page Designer tool.

A pivot in Canvas Reports can work with both numeric data and textual data.

When working with numeric data, you will choose an aggregate function to apply to the data. An aggregate function takes multiple values and returns a single value. Examples include Sum, Average, Count, and Last. If you include fields (dimensions) in your pivot, it will perform that calculation for every unique value of that field.

For example, you could get the average performance rating for a company’s geographic locations. For each unique location, it would take all the performance rating values and get the average. This data can be charted if desired. Therefore, the pivot has "summarized" the data.

When working with textual data, you will also choose an aggregate function to apply to the data, but they do not perform a calculation. The function still takes multiple values and returns a single value. Examples include Concatenate, Count, First. If you include fields (dimensions) in your pivot, it will perform a summarization for every unique value of that field.

For example, you could list an employee dependents names in a single cell, regardless of the number they have by utilizing the concatenate function in a pivot.

The Pivot Query Designer allows you to pivot data around certain fields in a query. To build the pivot:

1. Decide what you want to measure and drag the appropriate data columns onto the canvas. You can include more than one Pivot Measure.
2. Decide how you want to pivot the data and use the Pivot Measure Function box to select the Pivot Function from the dropdown menu. Pivot function options vary based on the types of fields you are using. Functions include the following: Count, Distinct Count, First, Last, Sum, Average, Minimum, Maximum, Concatenate, and Distinct Concatenate.
3. Add fields to define how to aggregate/summarize the data by adding fields onto the columns and rows.

For example, to create a report that displays the average salary analyzed by job code and location, you would create a query that includes the job code, location, and salary data. When creating the pivot table, you would add the job code to the column heading, location to the row heading, and drag the salary to the data section. When prompted, select the "average" function.

When you are finished, save your pivot so that you can add it into a pivot table or chart component.

The Pivot Query Designer allows you to pivot data around certain fields in a query. There are three basic steps to take after you choose Tools→Pivot Query Designer:

When you are finished, save your pivot so that you can add it into a pivot table or chart component.

You can toggle between Show Design View, which shows what is in the columns and rows, and the TableView, which displays the data in a table.

If you need to edit the measures, columns, or rows, choose Show Design→Edit or Remove.

Depending on the desired outcome, it is possible to include the 'overall' value of a measure in the table. For example, when creating a pivot to display average salaries analyzed by Job Code and Location, you may wish to also display the average salary for a location, regardless of the job code. This 'overall' value needs to be enabled.

This option is accessed in Show Design View→Dimension→Edit. Select a qualifier that includes the selected element to include the value:

Depending on the desired outcome, it is possible to include or exclude null results when aggregating data in a Pivot Table or Chart.

This option is accessed in Show Design View→Measures→Edit. Checking the box will exclude cells that contain no data; leaving the box unchecked will include them.