Pivot type reports allow you to see your data with 3 different Dimensions all at once:
- Rows
- Columns
- Subtotals
A great example of this would be a report showing what Items each Customer bought by Month:
- Rows = Items sold
- Columns = Time (Months, weeks, days etc.)
- Subtotals = by Customer
Use an Existing Pivot Favourite
We have set up some Favourite reports as Pivot type reports already to get you started.
Pivot type reports have an icon showing two small pages side by side when you view a list of Favourite Reports.
Open the Favourite report Sales (Item) - Items by Customer (Pivot)
Note that:
- Rows = Analyze by
- Columns = "v" the next Field (this replaces the Then by used in Analyze reports for drill down )
- Subtotals = Group By
The Column Pivot can be changed to many other dimensions e.g. Invoice Salesperson or Sales by Week (Weekly Sales) or even Sales by Day (Daily Sales).
If you change to say Daily Sales as the Columns you should first change the Sales Date Range from This Year to This Month or This Week otherwise it will try to show Daily Sales for the whole year.
(WARNING: Choose the Column dimension carefully, there is a System limit of 100 columns Maximum. See below.)
Daily Sales
Change an Existing Analyse type report into a Pivot
You can also convert an Analyse type report into a Pivot type report.
Analyse type reports have an icon that looks like a spreadsheet page when you view a list of Favourite Reports.
Open the Favourite report Sales (Item) - Margin by Customer
Note that:
- Rows = Analyze by
- Columns = These are predefined depending the report mapping e.g. Item sales, Jobs, P&L etc.
- Drill down = Then by used in Analyze reports for drill down to next level of data )
- Subtotals = Group By
To change the Report type click on the Advanced icon.
Change the Report Type from Analyze to Pivot.
While you are here you can also set the Pivot (Columns) dimension in the first Then by field. I have chosen Supplier. (WARNING: Choose the Column dimension carefully, there is a System limit of 100 columns Maximum. See below.)
Also choose what data you wish to Pivot on (Total Sales, Profit, Margin % etc.). I have chosen Profit.
Now instead of just seeing Margin by Customer you can see the profit contribution by Customer AND by Supplier of the items they bought. This is great to understand if you want to identify customers who are not buying certain product groups, also important to understand how much profit each supplier adds to your business and where.
See Multiple Dimensions in the Pivot
To see another data dimension in the Pivot choose the Dislpay field.
Change from Profit to Qty to Total Sales etc.
To see All of these elements in the one report choose ALL:
- All (H) - a Horizontal Pivot
- All (V) - a Vertical Pivot
Horizontal Pivot
See all elements across the page. Useful when you want to compare data Down the page.
This view can get very busy with too many columns.
+/- Columns to hide Columns you do not need.
The result is much clearer to read just showing Qty and Total Sales.
Vertical Pivot
See all elements vertically on the page. Useful when you want to compare data Across the page. This is also useful when you have a large number of columns.
WARNING:
Choose the Column dimension carefully, there is a System limit of 100 columns Maximum. See below.
If your receive this message then your options are:
- Change the Pivot Dimension - e.g. Report by Week (52) rather than Day (365)
- Reduce the Trans Date Range - e.g. Report for a Quarter not the whole Year
- Set a Filter - e.g. Report for one group of Items, Customers etc not all
- Change View By - e.g. View by Top 10 rather than All