@jai_kejriwal: I understand from your view of point. From our tech perspective, it’s quite easy, just to export and then do the pivot in the excel.
However, in my experience of implementing ERP, many users still demand for that feature which enabling them to create a quick & live report. For example, some of users need reports which is result of simple pivot, then they can save that report view and later view it. They don’t need to repeat downloading all the data and doing the pivot again. It’s also really useful when they are on the field, where are not in the office with computer or laptop.
Regards to the usage of system, exporting large of dataset to make the system will crazy. Currently, I run the ERPNext for a retail company with 160.000 customer based and and around 900.000 orders annually which include 1.8m line of sale order item. At this scale, Sales Analytics is freeze due to all the calculation is executed at front-end of a small laptop and loading data is another issue. Therefore, the only way for them to make report is exporting sales order data and they select for nearly all the columns to export. Sometime the system can return the excel file, sometimes not. In case of not, they need to split the columns and combine later in Excel. We cannot abandon them from doing these things because system is created to serve the end-user. As the result, the system is freezing of huge reading because of some user doing this for their valid purpose. Pivot directly on database can reduce a huge system resource for these kind of usage with in-memory calculation. I’m not sure how we implementing these case in system (through ORM or directly on database, or we can use CQRS-ES) but this topic may be our starting point.
I take the steps on making some custom report in some BI tools like SpagoBI with slave database. However, I only cover for very common report. The needs of using a quick pivot for huge range of department report is still there. Some people can suggest metabase, but installing another system, configure it, setting permission and security then train the user to learn metabase is a huge effort for a medium size company