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 and select the ServiceM8 Jobs data source.
In the Layout section I'll remove some of the auto-added columns, and add Category so that my columns look like this:
Set Pivot Options
When pivoting, there are three kinds of values:
- Rows - these appear on the left, and there will be a row for each value
- Pivot Columns - there will be a column for each of these values
- Total 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.
Report Layout: Pivot Table
In the Layout section, change the Report Layout option to Pivot Table:
This gives us extra options so that columns can be dragged to become Pivot Columns or Total Values. I will drag Date Opened to be a Pivot Column, and Job # to be a Total Value.
Row Values
In my report, I'm going to use the Category column as a Row value. This will mean I get a row in the report for each job category.
You can have multiple Row values - they'll all appear on the left side of the report. For example, if I choose both Category and Job Status, I will get a row for each combination of Category / Job Status.
Pivot Values
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.
The Value Source can be edited by clicking the cog icon:
Total Values
In my report, I want to get a count of jobs per month / category, so I'm going to use a count of Job #.
The designer has already set a default total type of Count so I don't need to change anything. For total values, we can change the aggregation type (Sum, Count, Average, etc.) using the cog icon:
You can use multiple total values. For example, I could have a count of Job # and also sum of Invoice Amount.
After setting all the options, my Layout section looks like this:
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!
Next Steps
Now that you know how to pivot, it's time to experiment and try it out on your own reports!