I would like to Integrate PivotTable.js with iDempiere

[Syed]

Dear Community,

I am planning to contribute the iDempiere Plugin for the PivotTable.js to visualize, slice, and dice the data.  I believe PivotTable.js Integration will add more value to the End Users to see the data the way they want.

Whoever is interested, kindly share your design ideas to integrate with the iDempiere so that we can fix the roadmap for the development and proceed accordingly.

Thank you,

Kind regards,

Syed.

[Michal Zilincar]

Hi Syed,

a while ago I started to integrate it in the reporting. 

I am halfway through , user can run it over data from reports. Next steps should be , save settings of pivot table so user doesn't have to configure it each time and potentially run it preconfigured as report in dashboard gadget. 

https://recordit.co/Iap4tJFWLn

I did changes in ZkReportViewer and ReportEngine classes , didnt do it as a plugin.  If interested I can share my changes. Ideally to create a ticket and do it there ...

BR

Michal

[Sidik Bilardi]

Hi Michal ,

i seen the vid , that's really something

hope idempiere future version can handle that pivot.

Best Regards

Sidik

[Nicolas Micoud]

Hi Michal,

That's sound great! Does it work with ZK Community Edition or does it require Enterprise Edition?

And I notice HTML+ on the listbox of outputs, if possible can you elaborate on what is it?

Thanks

Regards,

Nicolas

[Norbert Bede]

Hi, I did that too. but it is unusable in a multi-tenant cloud environment. kills the server if the user has a big imagination. n

[Syed]

Hi Michal,

It's a great work. I am interested in completing what you have started.

Kindly share the changes and we will discuss how we can preserve the user pivot table settings.

Hi Norbert,

Can you explain how it affects server performance since this pivottable.js is a purely javascript library 

so once the data is downloaded to browser

then there is no need to hit the server for every drag and drop of rows and columns?

Thank you,

Best Regards,

Syed

[Norbert Bede]

Hi, 

first for serious pivot you need huge dataset. Then it downloaded first to default format. If it is HTML, then you receive eg. 100k rows, then your browser fill huge DOM objects and iDempiere WebUI being unstable. (You can measure it in Chrome Inspect => memory and performance.)

Probably it can work, but ATM you cant prevent  user to lost in print outputs and switch between formats with 100k rows...etc.

n

[Michal Zilincar]

Hi Nicolas ,

HTML+ is implementation of datatables.net

It was originally done by Norbert, all kudos to him. Its quite nice fancy feature, pagination, very quick filtering.

But in reality there is zero response from my end users. They somehow didnt get used to it. 

I have implemented it as HTML+  as I was not 100% sure with all implications of using it. I wanted to gave a user opportunity to try it, and if any problem still use original HTML. Maybe better approach would be just to replace original datatable. 

https://recordit.co/f68tUk6bKD

[Michal Zilincar]

Norbert is right in a way , that typical user gets an idea to do some sales analysis over " Invoice (Line) Detail and Margin Report ". He wants to compare 3 years, all organizations. There can easily be millions of lines. But this can be dealt with communication with end users.

In my use case I have prepared aggregated view so user still can do needed analyses over thousand of lines not millions.

I will create jira ticket and migrate classes to latest version of iDempiere , so anyone can test it and give feedback. 

[Michal Zilincar]

there is no need any special license. As pivottable.js is  pure javascript library , opensource.

[Nicolas Micoud]

Hi Michal,

HTML+ seems great !

+1 for me (from what I see) to integrate in trunk (maybe by adding a list on AD_Process : ShowFilters : Yes, No, OnDemand) ; if OnDemand, add a new button on the toolbar)

About pivot tables, a limit can be set to not execute it if more than x lines are generated ?

wdyt?

[Norbert Bede]

Hi,

I found the ticket we created  for the advanced HTML report: https://idempiere.atlassian.net/browse/IDEMPIERE-5236

I had a discussion with Hengsin in Mattermost (I cant find it now), where we discussed about data loading. ATM we are load data first, then fill DOM objects, not nice wth huge data. This must be fixed or implemented async load - no idea how. 

From a functional perspective, the database is really nice and usable, would be happy to get into the core.

We simply switched some tenants to use it without question to like or didn't like it.

Norbert.

[Syed]

Hi Michal,

Could you share the Jira ticket? I am eager to test your changes.

[Michal Zilincar]

Hi Syed,

I ask you for little patience , quite busy for next few days, beginning next week you can count on it. 

I will put in ticket both HTML+ ( aka datatables.net ) and Pivot ( pivototable.js )

BR

Michal

[Flo Boj]

Hello Syed,

I had the same idea, but talking to my girlfriend ( working at Continental (AP Strategics) )

convinced me just to export to Excel, since Excel ( I dont like that ) serves as "de-facto standard" for inter-company data exchange.

That being said, I just improved ReportEngine -> Sort-by-Columns, Export-to-whatever and that stuff in my local branch.

Now here we are fine !

Best Regards

Florian

[Michal Zilincar]

Hi Syed,

I have created patch

https://idempiere.atlassian.net/browse/IDEMPIERE-5949

BR

Michal

[Syed]

Thanks Michal,

I will check and get back to you.

BR

Syed.