ERPNext have gone through a great journey to see the very great feature right now. To make it come to next step, I would like to request whether we can process on making pivot table to our Frappe framework. It can be helpful a lot on making reports and friendly interactive views.
1 Like
Why not use report builder and if pivot tables are required then export to excel and do it in Excel?
A forum search on ‘pivot table’ turned up this report submission that will be available in the next release, V11
https://github.com/frappe/erpnext/pull/12879
That’s exactly the problem! What @Tai_Tran1 is suggesting is that we should be able to create such tables inside ERPNext not outside in Excel!
It is a well known fact that the report builder in ERPNext is one of its weakest points. In a world that is getting more and more immersed in complex data analytics (think AI, Machine Learning etc), it’s not okay that an ERP as respectable as ERPNext should have a report engine that only handles one table (DocType) per report!
The only consolation for self-hosted users is ability to create query reports but if you’re on the ERPNext cloud, forget it! We need a better report builder like yesterday!!! 
Kind regards,
1 Like
Have been looking for this feature!!
Another related feature: When Totals row is enabled, allow specification of how each column is aggregated.
E.g. Sum, Count, Total, etc.
2 Likes
Yeah, that’s part of the standard features in Pivot Tables
@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
6 Likes
Perhaps a Metabase integration then? Would be willing to write a specification of what it is you want. If so, then this will move forward faster than it being just a discussion on this forum.
I guess I understand your frustrations from report builder. We use a self-hosted version and build our reports querying the database so don’t feel the need as strongly.
POC using js library PivotTable.js
Have modified query_report.js of frappe to include an area for pivot table (similar to the chart area)
Uses the same query and results as the query report, so minimum development overhead. Downside being the core file has to be modified.
6 Likes
@vijaywm
if you share how you achieved this it will be useful for fellow community peoples as well as we can try to integrate into core too
4 Likes
Hi @vijaywm
As suggested by @hereabdulla it would be great if you could contribute this so that it can be added in the core!
Great work
Cheers
Nice one Wale, truth has been said, the whole essence of smart work is to get smart reports that leads to smart analytics amd decision making by CEOs and senior management. It would be great to have the pivot table
1 Like
Hi, this is the library https://react-pivottable.js.org
If they point to something like this take my money! 
1 Like
Not ready for Production
The jquery UI datepicker breaks the frappe datepicker. So other forms will break. The only option currently is to use it without the UI and define columns and aggregates in js.
Moved it into a custom app. @federico_calvo As the repo states, it is a react port.
7 Likes
Hello @Tai_Tran1, i really love your post here on the BI tool you used. Can you please shed more light on Integrating SpagoBI as mentioned.
I am really interested in It
Wow, it’s smart way in using JS to integrate to the whole system 
@kolotayo: I don’t integrate the SpagoBI (current name Knowage) into ERPNext. So what I did:
- User interface
- Add a link to SpagoBI in the homepage for user to easy to navigate
- Both ERPNext and SpagoBI share same authentication by G-suite
- I tried to reduce their using of realtime report by setting up daily, weekly and monthly email report.
- Backend
There variety of solution to make replication from master to slave. You can pickup one, then use it as data source for SpagoBI. You can make direct connection to database in ERPNext but I don’t recommend this way, especially in peak time, it will be disaster.
1 Like
Hi @vijaywm , how to use this pivot in our custom report. I have downloaded this app and i don’t know how to use and check this functionality works or not.
Regards
Nivedha V
Hi @vijaywm, i am stuck in this can you please help me out to implement this pivot table.
Regards
Nivedha
Hi,
I am suggesting using Frappe Insight to achieve the same.
Please look at here : Insights: Open-source data analysis for everyone (frappeinsights.com)
Thanks,
Divyesh Mangroliya