The report designer supports pivoting data horizontally into columns.
For example, you might want to group figures by month and have a column per month. This is called a horizontal pivot and is most commonly used for dates, as well as categories which have a small number of values (e.g. staff member, product group, state, etc.)
This how-to guide will step you through using the report designer to create a horizontal pivot. It assumed a basic working knowledge of the report designer.
Create the Report
For the purpose of this how-to guide I'll be creating a report from scratch using the ServiceM8 Jobs data source. The same steps should work with any connector and data source.
From the report list screen, use the Design New Report button to create a new report. I'll call mine Pivot Report:
I'll select the ServiceM8 Jobs data source, and enable the Date Range filter:
Next add some columns to the report:
Set Pivot Options
When pivoting, there are three kinds of values:
- Index values - these appear on the left, and there will be a row for each value
- Pivot values - there will be a column for each of these values
- Aggregate values - these are the numbers that appear for each row/column
The aggregation option is used to tell the designer which column is which when pivoting.
In my report, I'm going to use the Category column as the index. This will mean I get a row in the report for each job category.
For index values, we use the No Aggregation setting:
You can have multiple index values - they'll all appear on the left side of the report.
In my report, I'm going to use the Date Opened column for the pivot values. This means I will get a column for each date. I'll also use the DATETRIM function to let me group the dates into week / month / etc.
For pivot values, we use the Pivot Horizontally setting:
You can only pivot on a single column.
In my report, I want to get a count of jobs per month / category, so I'm going to use a count of Job #.
For aggregate values, we select an aggregation (Sum, Count, Average, etc.):
You can use multiple aggregate columns. For example, I could have a count of Job # and also sum of Invoice Amount.
Run the Report
After saving, I used the following filter settings to run my report:
And I get output like this:
You can see there is a row for each category, a column for each month (because I chose Monthly as the date grouping), and the count of jobs for each category/month combination. Fantastic!
Now that you know how to pivot, it's time to experiment and try it out on your own reports!