[Pivot Table] OnRefresh

[Pivot Table] OnRefresh

  
Forge Component
(6)
Published on 5 Oct by Gonçalo Borrêga
6 votes
Published on 5 Oct by Gonçalo Borrêga
How can we integrate the onrefresh parameter as shown in: http://nicolas.kruchten.com/pivottable/examples/onrefresh.html

Functionally I think it's important to have the abiitty to save the settings as a variable so if we want to recall the settings (say for canned reports) we can.
New version published... 
It notifies the page being used as settings change. I've also added a few more capabilities:
- Sorting in the table (column headers, row headers, or totals)
- Save current state as a report; reload them later (check the sample app)

The readonly mode won't apply filters unfortunately (seems to be a limitation of the base javascript component)
Enjoy.

Hi Gonçalo and Doug,

First all, I would like give you the congratulations by the excelent component. I was finding some simple "bigdata" solution to use in an application that I'm developping for a beer industry, and the Pivot Table was great. 

Now, I'm having too problem in the IsReadOnly refresh. I followed exactly the same example of the PivotSimple, writing the reports and configuration in a table. However, when I pass the configuration SavedSettings with IsReadOnly true, the report showed is different. Seems that the sum is changed by count. And the same problem happens in the PivotSample. Is there possible that this issue is by the version 9 ? My version is 10.

Thank you very much,

Roberto

Hi Roberto,

Thanks for the feedback and the reported malfunction... 

In fact, this is a problem with the component. I looked into it, and the "RawSettings" format for an editable pivot is not the same as the one for a read-only one... so some, such as the aggregator and value - which define what Value should be used and how it is aggregated (in this sample the Sum / Value) is ignored by the read only definition.

I'll give it a look and check if I can transform it automatically between the 2 modes. The Aggregator parameter seems to suffer from the same problem: ignored in the readonly mode...

Perfect Gonçalo,

I'll appreciate very much if you can to do this, because your component is really very good. In my case I defined some "cubes" with the main informations groups of the application, and the proper user defines and retrieves the wished data, in a confortable way. But the swicth to a read-only mode is important, because the screen stays more cleaned (and I have several fields in a cube). Congratulations.

Best Regards,

Roberto

 

Hi Roberto,

A new version is available (2.0.1) that already properly supports the settings you need.

  • Fixed problem where ReadOnly view was not properly considering the Aggregator/Values nor the renderer.
    • This now also allows saving your settings in Edit mode, and then switching to ReadOnly mode while keeping the exact same configuration - useful for a user to change a report, and then save it for clean visualization by someone else.

Give it a try and let us know if it worked.

Cheers

GB

Hi Gonçalo,

I would like to thank you very very much. Your kindness was great. But when I tested, I saw an last error. Really, the switching readonly fix was solved, but the component does not storing the filters in this swicthing. You can see this in the PivotSample. Enter in the "Pivot Sales Via Table" and select some countries in the dimension country. It will woks, showing only the countries selected. But when pressing the readonly swicth, all the countries are showed again. Is this fix hard to resolve ?

Thank you,

Roberto 

Hi 

Was taking a look into the pivottable.js documentation and ‘it seems doable’, but there will need to be some custom programming because it doesn’t provide that possibility out of the box. So some effort... I’ll try to give it a shot this weekend.

However, the way to make it work would imply that the filtering (for the read-only table) would happen on the client/browser, done via JavaScript. This means that even if who’s creating the report filters out some data, it will still be available on the page, and whoever sees the readonly table would still be able to see it (by viewing the page source). Is that acceptable in your case? Another option would be for the component to provide back (via its event) the list of values that were excluded, and you could use them to filter the source aggregate on the server side, therefore not even sending that data to the browser...

Hi Gonçalo,

I appreciate your effort to try solve the fix. But I think that I understood your suggestion in transfer the filters to the server side when using the read only mode. This enforce my worry about component's performance when the result set of the query is great. I believe that all result set is brought to client side and then filtered and transformed by Pivot Component. Is it? In this case, your suggestion in transfer these filters to a sql query can be a good and more suitable solution.

But to implement this estrategy reading the excluded/included filters of the Pivot's SavedSettings, I need a dinamic filter in my queries. I am using the queries builded by Outsystems (drag the table to espace and adding the source entities and so on). Is There a way to add a dinamic filter in these queries ?

Thank you very much,

Roberto


ROBERTO CAMPOS wrote:

Hi Gonçalo,

I appreciate your effort to try solve the fix. But I think that I understood your suggestion in transfer the filters to the server side when using the read only mode. This enforce my worry about component's performance when the result set of the query is great. I believe that all result set is brought to client side and then filtered and transformed by Pivot Component. Is it? In this case, your suggestion in transfer these filters to a sql query can be a good and more suitable solution.

But to implement this estrategy reading the excluded/included filters of the Pivot's SavedSettings, I need a dinamic filter in my queries. I am using the queries builded by Outsystems (drag the table to espace and adding the source entities and so on). Is There a way to add a dinamic filter in these queries ?

Thank you very much,

Roberto


Hi Roberto!  

You are correct the component handles the data client side and probably isn't suited for massive data sets.  Might I suggest looking at https://www.outsystems.com/forge/component/365/advanced-filter-builder/ as an alternative to reducing the resulting set - then using this component to pivot those results.  On the actual issue at hand it's really an issue with how the data is loaded initially (or in read only) versus how it's modified by the user - two different functions - so we have to translate.  This will be available shortly.      


Hi Doug,

I liked very much of Advanced Filter Builder component, but I get worried in raise the complexity to the user final, that could do a filter in both components: one local (Pivot Table it self) and other in server (Advanced Filter). 

Then I decided to construct a server filter myself within the application. First I get change the automatic scafolding queries by manuals widgets SQL Query. I copy the SQL generated by scafolding and modify manually to widget. After I builded an action that read the user defined filters in PivotTable string SavedSettings (clausule Inclusions) and generate the SQL part Filter using 'In clausule' (For example, "and {batch}.site in ('Rio de Janeiro', 'São Paulo', 'Recife)'". The second thing was adopt the readonly concept of the PivotSample in a edit concept. In my report screen there is a dual button Edit and Save. When the user see the Edit, the query is in the readonly mode (or running mode) and the query is sended to server with the filters builded. When the users press edit button, the report enters in a design mode with a not readonly. In this mode the server filters are cleaned and the report can be changed. The help guided the users to enter in a Edit Mode with a short date period to reduces the query result set length, because is this mode it does not have sql filters.

I had worked a little to do this, but I was very satisfied with the result, because after the report is builded and saved, there is a filter under query that decrease the length of the result set.

Again, I would like to thank you and Gonçalo very much by the component and by your attention. I'm developing a applicative for beer industry, and this estrategy allowed that the user retrieves plant floor process and production data with great flexibility and in a very confortable way.

Best Regards,

Roberto

Great to hear that Roberto!

Would be great if you could post some screenshots or a small demo of your system working for others to see how this could be leveraged in a real world scenario!