PLEASE NOTE: Since we update Flexmonster Pivot with new features biweekly, the information might become outdated. Please check our latest news.
No doubts pivot reports provide better insight on multidimensional data with their ability to provide basic operations like slicing, dicing and pivoting OLAP data interactively. Rather than having such data separated into flat reports for dashboard tables, gauges and charts, pivot table report is still a tool where analysts can interactively gain more visibility.
While traditional pivot table report tools like Excel or PowerPivot provide a number of instruments to accomplish the above, the web report should be more robust to meet expectations and usability of common users. From manager to CIO, web pivot table reports should be a straight and easy self-service reporting tool to answer questions with little (or better without) any lengthy trainings or education.
But how extensive such report should be? Whether to include highly prepared and ETLed data or leave end-user with as many as possible dimensions and values to select from and dice? We recommend the following 3 practices for a web pivot report that will bring more value than traditional tables or dashboards, published on the web.
Prepare correct data source
A somewhat common, yet crucial thing. You might have a number of data sources to take initial data from, but selecting the one to report on is important for end user to see a direct value. For example, a SCRUM development team effectiveness can be measured with number of “burned down” tasks. Once loaded into web pivot report, these can be analysed versus time period, actual billable hours or particular development teams or even diced for individual developers performance.
With the latest arising trend of big data and textual analytics, the ETL phase becomes more important than ever. Strive to eliminate any duplicates whether they are in values or dimensions. We will not cover all ETL topics in a small post, however, this is an important practice to take care of. The more prepared data source will be, the higher benefit it would give to end users.
Fight for compact and visible report
Once the data source is prepared, consider the initial report that will be presented to users. It’s definitely known that they would modify it and save different configurations for various needs, however, they would still need a starting point. A concise and compact report should contain the most important value visible on the grid as well as dimensions that firmly establishes the context for user.
Put conditions to locate trends, opportunities or threats
Despite a raw list of figures would be more valuable than no information at all, it becomes more visible if any trends, opportunities or threats (e.g. critical number of fraud transactions can be important to start locating the reason) are marked in the report. Use pivot table conditional formatting to put specific value indicators. You can put couple of them as example for user to adjust or place his own ones.
A well-prepared and comprehensive report would be a good addition to a dashboard or analytics solution and can be far more valuable than traditional crosstab reports. However, everyone has their own experience, you are welcome to share your thoughts and best practices as well!