The designer supports pivoting data horizontally into columns.
For example, you might want to group figures by month and display a column for each month. This is called a horizontal pivot, and it is most commonly used for dates or categories with a small number of values (e.g., staff member, product group, state, etc.).
This how-to guide will walk you through using the designer to create a horizontal pivot. It assumes you have a basic working knowledge of the report designer.
Create the Report/Dashlet
For the purpose of this how-to guide, we'll create a report/dashlet from scratch using the ServiceM8 Jobs data source. The same steps apply to any connector and data source.
From the report list screen, click the New Report button, then select Blank Report to start a new report. Choose the ServiceM8 Jobs data source, and name the report something like Pivot Report.
On a Dashboard, Click on “Edit” on the top right corner of your dashboard. Then click on “Add Widget”
In the Layout section, Remove some of the auto-added columns and add Category so that the columns will appear as desired.
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 added to become Pivot Columns or Total Values. I will add Date Opened to be a Pivot Column, and Job # to be a Total Value.
Row Values
In my report, If 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, If 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 Aggregation function to let me group the dates into week / month / etc.
The Value Source can be edited like this:
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.)
You can use multiple total values. For example, I could have a count of Job # and also sum of Invoice Amount.
Also you can check mark different options like “Total Row at End”, “Auto-expand grouping”, “Freeze first”, “Sort Pivot Headers”, “Include Pivot Totals” and “Fill Blank Cells with 0”.
In our example I will check mark “Total Row at End” to show the overall totals below the report.
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 week (because I chose weekly aggregation), and the count of jobs for each category/week combination. Fantastic!
Next Steps
Now that you know how to pivot, it's time to experiment and try it out on your own reports!