ArticlesBite Size Analytics: Getting Started with Pivot Tables

• All versions

For any feedback or requests for future Bite Size Analytics post in the Reporting Chatter group.

1. From Analytics Studio, navigate to a dataset that will be used as the source for the pivot lens
2. For Accounting, users use Financial Transactions for any transaction-based reporting and Financial Balances to report on both Transactions and budget details [users can filter by the Balance Type field to report on budgets]
3. Then open the dataset to start the creation of a lens
   • Note: although a Financial Transactions dataset is chosen, any dataset across either PSA, FM, RM, BC, SCM or Custom can be used to create a lens
     
4. Use the lens options to switch to either a compare table or pivot table. This is in the top right
   
5. Once the Lens is created and the option is chosen for the pivot table, the view looks similar to below:
   
6. Choose a column value. By default, this will be Count of Rows, telling how many records there are
7. Click on the text to choose from measures in the user's dataset. In the case of the Financial Transactions dataset, type "Dual" and options for dual value are shown
   
   
   • Note: in many of the Financial Datasets an(s) on the end of a measure means it is reversed. This allows users to format the sign of sections.

Now add Groups to the pivot. These are text (aka Dimension) and date fields. Users can add up to four groupings through the user interface, but more can be added by reading the Increasing the Number of Lens Groupings Supported Bite Size Analytics. The last Group added will be used for the columns with the rest for rows. 

Remember users can filter pivot tables through filter options on the left and format through the paintbrush on the far right. Also note that it is not possible to have two groupings across the top like a matrix style Salesforce report.

When it comes to Analytics, there is a great deal of value to be had from building lenses. A lens is the Salesforce name given to either a chart or a table of data in analytics. One such lens is a pivot table and this byte will give an introduction to that functionality.