asked on February 17, 2023
Hello there,
We are tying to develop a pivot table solution that minimizes the data transfer with the JSON data server. So, we eliminated the names of fields and used very short identifiers instead in our json data.
In order to show the correct values in the grid we use customizeCell function in which we lookup the text value of the identifier in json.
Everything is working very fine except the filters. When the user opens the filter popup, he sees the identifiers instead of real text values. Is there a way to show custom text values or labels in filters? For example, if there were an event such as FilterItemRender, we could catch this event and put our custom label by looking up the identifier from a dictionary that is containing the mappings.
An example filter panel view is attached.
Thank you in advance!
Best wishes.
⋅ February 20, 2023
Hello, Uygar!
Thank you for contacting us.
Kindly note that filters in Flexmonster take the members' names directly from the data source, so there is no way to override them somewhere in the middle.
However, we can recommend the custom data source API approach to achieve the desired data transferring behavior. The custom data source API is our custom communication protocol between Flexmonster and your own server implementation. This way, you can define id for each member. Flexmonster will identify the member via id and use an appropriate member caption on the client.
You can read more about custom data source API in our docs: https://www.flexmonster.com/doc/introduction-to-custom-data-source-api/.
Feel free to contact us in case of any other questions.
Kind regards,
Solomiia
⋅ February 28, 2023
Hello, Uygar!
Hope you are doing well.
Our team is wondering if you had some time to check out our custom data source API approach. Could you please let us know if it works for you?
Looking forward to hearing from you.
Regards,
Solomiia
⋅ February 28, 2023
Hello Ms. Andrusiv,
Thank you very much for your follow-up.
We investigated the Custom Data Source API, and it is close to what we want to do. However, 3 things we want to implement differently:
#1) We want to distribute the server load to many clients (browsers), so eliminating any sessions and calculations from server side. Our server only gets the report configuration, creates relevant SQL, executes the query and returns the data as JSON, no more. Additionally, we also return only the required fields on-demand, so creating SQL dynamically and selecting only the fields specified by the user in rows, columns, filters or measures.
#2) We want the user to be able to see all available fields in the fields list panel, even if some of them are not in the JSON data. So, to achieve this we use a separate getMetaData function and add datasource mappings to pivot after the pivot is ready before actual data is loaded. After that the data set consists of the fields only specified in rows, columns, filters or measures sections in fields list panel.
#3) We don't want to retrieve plain data to client, but only short attributes defined for both keys and values in json to minimize transfer time. To eliminate the load from the client and the server, these identifiers are replaced by SQL server and also SQL server returns directly the result Json. We don't get dataset as ResultSet in the server app, but we get directly the Json from SQL Server (key-value pairs consisting of only identifiers for both key and value)
So, the question is how we show the correct data to the user if all the keys and values are short identifiers.
As I mentioned the getMetaData function, we also get all of these mappings in this function and during rendering in customizeCell function, we lookup the identifier from the mapping table, and put the real label instead of the identifier.
*** This approach works like a charm! Excellent performance for both the server and the client.
However;
Our problem is even if the user can see the labels inside the grid, he can only see the short identifiers when he clicks a field and opens the filter panel for that field. Since the filter panel uses the label attribute of the data, it uses the short identifiers that we had defined, not the labels we replace in customizeCell function.
I hope these explanations give you more information about the situation.
Thank you very much.
⋅ March 1, 2023
Hello, Uygar!
Thank you for the detailed description of your use case.
Our team kindly suggests implementing custom filters for the described case.
Here is a brief description of the suggested approach:
1) Create your own Filter pop-up window. Add the "Apply" and "Cancel" buttons to it.
2)(approach 1) Add a button in the desired place for opening the Filter pop-up for the specific field. To disable the default Flexmonster filters, use the showFilter: false option as follows:
report: {
options: {
grid: {
showFilter: false
},
//other options properties
},
//other report properties
}
2)(approach 2) As another possible approach, you can prevent Flexmonster default filters from opening and show your own pop-up instead.
It can be done using our filteropen event:
pivot.on("filteropen", (event) => {
let element = document.querySelector(".fm-filter-view");
if (element) {
element.remove();
}
showCustomFilter(event.hierarchy); // your custom logic here
}
Here is the JSFiddle example for reference: https://jsfiddle.net/flexmonster/b6nfa4em/.
Please note that all hierarchy members can be retrieved using the getMembers() API call.
3) Add the functionality to the Apply button. The filters can be applied to Flexmonster using the setFilter() API call.
Kindly note that the suggested API call requires the identifiers, not labels, for your case.
4) Filters are also present in our context menu, which opens on the right-click on the cell.
The context menu can be disabled in the following way:
flexmonster.customizeContextMenu(function(items, data, viewType) {
return [];
});
As another option, it is possible to add the custom filtering logic to the context menu using the following guide: https://www.flexmonster.com/doc/customizing-context-menu/.
Hope you will find our answer helpful.
Please try the suggested approach and let us know if it works for your case.
Looking forward to your response.
Regards,
Solomiia
⋅ March 14, 2023
Hello, Uygar!
Hope you are having a great week.
Our team is wondering if you had a chance to try the suggested approach and create the custom filter pop-up. Could you please let us know if it was helpful?
Looking forward to hearing your feedback.
Regards,
Solomiia