Pivot - To see Multiple Dimensions at once

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

 

 

www.BI4Cloud.com 

 

  

 

 

 

Was this article helpful?
0 out of 0 found this helpful
Have more questions? Submit a request

Comments

Please sign in to leave a comment.

Powered by Zendesk