Pivoting data horizontally lets you display values like dates or categories as columns in a report. This guide explains how to set up a horizontal pivot step by step.
Video Walkthrough
For a visual walkthrough, watch the attached Loom video: How to Pivot Horizontally in the Designer in Wink V2. It demonstrates the process step by step for easy reference.
On a Report
For this 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.
Note: You can also refer to this guide for more detailed steps “How to create a chart or graph from a report in Wink V2”
From the report list screen, click Design New Report.
Name your report “Pivot Report” and select the ServiceM8 Jobs data source.
Adjust Layout: Remove unnecessary columns and add relevant ones like Status.
When pivoting, there are three kinds of values:
- Rows: Appear on the left with a row for each value.
- Pivot Columns: Display as individual columns for each value.
- Total Values: Numbers that appear for each row/column combination.
Use the Aggregation option to define the column roles when pivoting.
Report Layout: Pivot Table
Below the “Columns” option, toggle Pivot Mode to change the Report Layout to Pivot Table.
This enables additional options for adding columns as Pivot Columns or Total Values.
Row Values
Use the Status column as a Row value to display a row for each job status.
Add multiple Row values if needed, such as Category and Job Status, to show combinations of these fields.
Pivot Values
Use the Date Opened column as the Pivot Column to create a column for each date.
Change the appearance to “Month and Year” or customize it:
- Select Custom Date and Time.
- Enter “YYYY - MMM” in the format field.
Total Values
Use a count of Job # as the Total Value to calculate jobs per month/category.
Customize:
- Change the column title to “No. of Job” under Appearance.
- Set Data Format to “Number” with zero decimal places and enable the “Use Separator” option.
You can add multiple Total Values, such as both a count of Job # and a sum of Invoice Amount.
You can Toggle Total Row at End for an overall summary view.
After setting all the options, my Layout section looks like this:
Click Save to finalize your report.
And you get output like this:
Your final report will display rows for each status, columns for each month (based on the date grouping), and the count of jobs for each status/month combin
On a Dashboard
You should create a dashboard first. You can follow the step-by-step guide here in creating your first dashboard and widget “How to create a chart or graph from a report in Wink V2”
Use the same steps outlined above to configure the widget for a pivot view.
Add the report to the dashboard and adjust settings such as layout, setup, and appearance as needed.
Next Steps
Now that you know how to pivot, it's time to experiment and try it out on your own reports and dashboards!