Building Pivot Data Queries

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, 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 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.

Sometimes a list report will have multiple rows per employee. In the example above, each user has several different competencies, and their corresponding ratings are listed under Competency Name. Each competency outputs as an individual row.

When you have created a query, you may want to present the data as a pivot table. In this case there will be one row per employee, and the competency names will be transposed from rows to column headers. Ratings will be shown in the cells under each column.

You follow the same procedure as working with numeric data, except when you add the measure, the list of functions available will be restricted to those that can be applied to textual data.

For example, you would add the Competency name to the columns, Employee Name to the Row, and the Competency raging to the data area. Depending on the situation, you will need to select the correct aggregate function.