A pivot report is among the underrated and underused powerful feature of Excel. So I want to give some lights onto its importance because this is really helpful, not only to the end users, but also to the developers.
What is a Pivot Table?
In data processing, a pivot table is a data summarization tool found in data visualization programs such as spreadsheets or business intelligence software. Among other functions, pivot-table can automatically sort, count, total or give the average of the data stored in one table or spreadsheet. It displays the results in a second table (called a "pivot table") showing the summarized data. Pivot tables are also useful for quickly creating unweighted cross tabulations. The user sets up and changes the summary's structure by dragging and dropping fields graphically. This "rotation" or pivoting of the summary table gives the concept its name.
- wikipedia
The above explains in summary what pivot tables are. But sometimes some readers will still find it hard to picture what those really are. So okay, what is a pivot table again? This time in layman's term please?
A Pivot Table is a summary report of underlying normal data in a tabular output. In excel, a pivot report is derived from a source data which is a "perfect table". A perfect table in the sense that all the needed information are there. If you have an abnormal table, then all data might be already there. But if you are using normalized tables, then you have to perform an SQL SELECT with JOINs .. INTO CURSOR to complete the information and then export that cursor into an excel sheet.
For non-developers where data are manually encoded, it should have no cell merging, no gaps on rows or columns, no anything fancy. Just plain straight records with column headings and row 2 onwards representing records. Very similar to our exported data.
The image below shows an example of an excel pivot report.
Hey, it looks like just a crosstab report? So why the title? What is the difference between a crosstab and pivot reports?
When we do a crosstab report inside VFP using either _GenXTab, FastXTab or any other cross tabulation utility, then we are placing the cross-tabbed result into a new cursor/table. The source table(s) remains intact. Said result also cannot be easily changed from within itself because just imagining the processes involved in doing so may give you a headache, LOL!
So if you wanted the crosstab report to be of different appearance? Then you have to create a new query for that and run those crosstab utilities again.
Pivot repots,on the other hand, goes beyond the crosstab report, in the sense that you still can:
- Perform report filtering
- Drill-down to details
- Change computations
- Add/remove fields onto the existing report as long as it is available in the source data
- Change formats
- Change appearances of the report
- Create a pivot chart
- Adjust the print size
- and any other more things we can do. It is a report where other more reports of varying presentations and needs can be done.
Excel pivot reports, with its ability to easily and readily change output, is very vital to top management for analyzing transactions to be used as basis for decision making. Most of them do not need to see the details because they do not have time for that. All the top management wants to see is to get a complete birds eye-view of everything in a glance.
When they have more questions, then that is the time to either employ the drill-down feature of an excel pivot report; or simply call someone below their rank to investigate matters.
And because of this, while VFP has its own internal reporting engine, I am lately moving these top managerial summary report from several frx files into a single pivot report with the aid of ssExcelPivot class. With that class, not only that I can give my users a lot of formats to choose from but also a lot of possible pivot report (summary output) just from a single source data; with just a few clicks. And this is because of the various switches I infused inside said class; which results to a real one-to-many reports if I may say so! Plus the ability to instantly change the pivot report also from within excel. Imagine having all the additional features of Excel for our reporting needs!
Get ssClasses library in VFPx to have the power of easy excel pivot generation in your hand in just a few switches. Cheers!
filtering Pivot report via PivotGrid control
ReplyDeleteThanks for this explanation. It was very helpful.
ReplyDeleteExcellent article. I have a question. Conceptually, why would you choose reporting solution like Telerik than to pivot table? What's the difference between them and what use cases do they each solve?
ReplyDelete