Marvels of Pivot Table
Amazing user friendly slice and dice tool in MS Excel
Pivot table is an amazing slice and dice tool within MS Excel with extremely feature rich drag and drop user-friendly multi-dimensional capabilities .
Users can do any kind of what if scenario with any kind of calculated fields within it. In Office 2007, Pivot table capacities have been expanded manifold.
With excellent drill downs , group by , sorting (including Top n reports) , charting , emailing , printing , exporting and conditional formatting capabilities there is no match MS Excel Pivot Table .
ERPJewels adds much needed interactivity with any baan data in any Baan table/ report and brings life to dead (read static black and white ) data in any Baan Table / report by letting user get the data in directly in Pivot Table .
Thus ERPJewels brings lot of vivacity and agility to reporting by providing a wonderful bridge between this 2 great software Baan and MS Excel.
OLTP Vs OLAP
ERP implementations or for that matter any OLTP (On-Line Transaction Processing) are necessarily relational technology and Business Intelligence or Data Warehousing or OLAP (On-Line Analytical Processing) necessarily are multi-dimensional technology.
For more information on distinction between Relational modeling and Multi-dimensional modeling please see this document.
OLAP Typical Advantages
A good Data Warehousing implementation has it’s own typical advantages such as.
1. Flexible reporting
2. Fast online reporting
3. Easier maintenance
4. Management by Exception
5. Push intelligence
6. Data redundancy
7. Optimum resource utilization
8. Multi-dimensional reporting
One can clearly see the relevance of ERPJewels in this context.
However in this document we’ll discuss only the last benefit (i.e. multi-dimensional reporting) capabilities of Excel Pivot Table which is the multi-dimensional load client for ERPJewels. The pivot table of MS office 2000 (office 9) onwards is very powerful and agile in terms of presentation. For the purpose of this document MS office 2002 (office 10 ie XP) is used.
This document highlights the elegant features of Microsoft Pivot table as a multi-dimensional reporting tool
Pivot table Basics
For a person who may not be familiar with pivot table, a PivotTable report is an interactive table that quickly combines and compares large amounts of data. You can rotate its rows and columns to see different summaries of the source data, and you can display the details for areas of interest.
The source data used in pivot table can be from worksheet list or external database. When external data source is used, then Micorsoft Query (which is an optional component of MS office 2000 (i.e. 9) onwards) must be already installed in the workstation. In a typical office installation this component (Microsoft Query) is not installed by default. Microsoft Query let’s user create offline cubes also.
The user can add different fields to Row, Column, Data or even a page area to get different view and summary of the data. Page field acts as filter on source data and the presents pivot table results for the current value of the page field.
When the pivot field is dragged to data area or is added to data area, it can have summary functions such as sum, minimum, maximum, average, count etc.
Sample Output
The beauty of Pivot table is that it just enables excellent drag and drop facilities, select/ed-select check boxes, appropriate command option list for each cell in the pivot area as per the requirement of the pivot cell. With all these changes, pivot table recalculates the figures and presents accurate figures to the user. And this is all user wants.
Columns do auto fit by default in the output. However the columns resized to fit the output in 1 screen.
Analysis has time dimension in column area and ledger hierarchy in row area. Whenever user clicks on the pivot area, all pivot fields are displayed by default.
Hide / Unhide items / Subtotals / Grand total / pivot field
In pivot table area user can right click at the pivot fields (with command button looks) or subtotal cells and click ‘hide’ to eliminate subtotals and the subtotals/ grand totals get eliminated. Hiding pivot field will remove the pivot field from the pivot area completely.
For example, hiding ‘Month’ will give following result.
Group / Ungroup by Pivot Field
User can group any numeric / date type pivot field and get results as per his/her grouping. For example ledger account dimension which is numeric in the example, user can right click any cell with ledger code è Group & Show detail è Group by gives following window.
It will give following output.
User can ungroup the same by selecting ungroup option for the field grouped by.
For Date type of dimension, following grouping options are available wherein user can select multiple options. Noteworthy is that Weeks, Fortnights, Half years are not available here (which are available with ERPJewels) while hours, seconds and minutes are available (which is not present in ERPJewels)
Roll up / Roll down
This is excellent feature of MS Excel wherein user can see the aggregates at the higher level and if required roll down or explode the detail. This helps the user in playing with hierarchical information with extreme ease.
E.g. if the user hides the data while right clicking the dimension ‘Fiscal Year’ in Pivot table above , it will recalculate the figures and present only yearly figures.
Interactive HTM output
Though users can not modify the values in Pivot cells and Excel Pivot table is very great output, he/she may wish to view the Pivot table in an intranet location and hence may wish the output in htm format. It’s very easy to convert excel pivot table to htm pivot table with all interactive user friendly features of Excel pivot table.
The Interactive Webpage will look like as shown below
Re-ordering field & changing field orientation
The user can change the field setting and its orientation as he required. i.e. he can move the fields which are placed at beginning of the table to the end of the table and vice versa. Also he can interchange the fields orientation from column to row and vice versa. This can enable him/her to see non-hierarchical information with extreme ease.
Calculated Fields
When the user want to see the results of certain formula worked on with or without use of the fields from the field list, he / she can do so by inserting calculated fields. For example there is total sales and total units sold as measure and user wish to see the average price for each dimension, he/she can insert calculated fields in the following manner.
The user has to give the name of the calculated field and then insert the fields given below if he requires them to be used in the formula written by him.
Enable Selection
User can select multiple cells easily (e.g. to format cells).
Pivot Table Report Format
There are about 21 Pivot table report layout from which user can choose for Pivot table viewing and printing.
An example of a Pivot Table report format is shown here.
Pivot Table Chart
The user has also the benefit of viewing the graphical representation of the PivotTable.
With various types of graphs around 100 and including different view such as 2D, 3D the user can get almost clear idea about the data from it. The graphs too are very user friendly with all flexibilities of a pivot table report.
The sample graph is shown below.
Pivot Table in Summary
Pivot Table report and Pivot Table Charts in Microsoft Excel 2000 / 2002/2003 /2007 are the excellent contributions of MS Excel in the field of summary reporting. Pivot Table Report can report relational data in multi-dimensional form very easily. User can use the excellent drag and drop facilities of Pivot Table report to view the analyses results from different angles.
ERPJewels uses Microsoft Pivot Table Report as an output directly coming Baan automatically without having to trigger the same manually by user. This has tremendous advantages to users which are enumerated here.
1. One challenge of Intelligence applications is to present the analyses results in very user-friendly interactive manner (like drag & drop features) without compromising on quality and yet it’s very simple. Pivot Table Report meets these challenges very easily.
2. Optionally user can present the data graphically in interactive manner in Pivot Chart Report. This is boon to key users with little knowledge multi-dimensional technology and terminologies.
3. User can filter the analyses results also using page fields. This is possible only for analysis results filtering (ie Transform level querying). User can not however change the original query in Baan data source (ie Extract level query). This is because the ERPJewels uses MOLAP approach which stores the analyses results only in multi-dimensional format and not the original large bulky data from data source. In fact this is not even required and desired in most cases.
4. User can get Top 10 / Bottom 10 easily.
5. User can use calculated fields to get ratios & % among various columns.
6. ERPJewels also specifies the selection criteria on the original data source. This helps users to understand / know what they are viewing (even at a later point of time).
7. Format Report is an excellent function of Pivot Table whereby users can present the analysis results in the way he/she desires.
8. Pivot Table Report can accommodate 8000 (32000in MS Excel 2003 and 1 Million in Excel 2007 ) distinct items and 255 data items (16000 columns in Excel 2007). This is sufficient for most analyses because by very nature summary information compact, concise and precise.
9. User need not learn multi-dimensional technology to query pivot table report.
There are some limits to capabilities of Pivot Table Report and MS Excel. To know more about limits, please click this .
Microsoft has provided excellent samples of Pivot Table report. Please download
Pivot Table Samples & Pivot Table Reports 101 to view these excellent How-To samples.
FastTrack to ERPJEWELS :
Latest Trend | Hot Links | Jewels of ERPJewels | Jewels of Baan | Jewels of Excel