asked on February 25, 2022
Hello,
We've got a question, it is as follow:
Given that we're using custom data source API; and implemented the 4 responses corresponding to the 4 requests (Handshake/fields/members/select), and we do want the "dragging" to be true.
knowing that the aggregations and filters are handled server-side, defined in "fields" response as follow:
* advanced: true (for multi-level hierarchies)
* "aggregations":{"any":["sum"]},"filters":{"string":{"members":true}
We're defining the slice as follow:
slice: {
reportFilters: [{"uniqueName":"Annee"}],
rows: [{"uniqueName":"Organisation"}],
columns: [{"uniqueName":"Periode"}, {"uniqueName":"Indicateur"}],
measures: [{
"uniqueName": "Data",
"aggregation": "sum"
}]
}
For first display, we receive "select" request as follow:
{
"type": "select",
"index": "FM_pivotTable",
"query": {
"aggs": {
"by": {
"rows": [{
"uniqueName": "Organisation-Gen1"
}
],
"cols": [{
"uniqueName": "Periode-Gen1"
}
]
},
"values": [{
"func": "sum",
"field": {
"uniqueName": "Data"
}
}
]
}
},
"page": 0
}
And when end-user filters in reportFilter (on "Annee" to select 2 values), we receive "select" request as follow:
{
"type": "select",
"index": "FM_pivotTable",
"query": {
"aggs": {
"by": {
"rows": [{
"uniqueName": "Organisation-Gen1"
}
],
"cols": [{
"uniqueName": "Periode-Gen1"
}
]
},
"values": [{
"func": "sum",
"field": {
"uniqueName": "Data"
}
}
]
},
"filter": {
"type": "and",
"value": [{
"field": {
"uniqueName": "Annee-Gen1"
},
"include": [{
"member": "TotalAnnee",
"filter": {
"field": {
"uniqueName": "Annee-Gen2"
},
"include": [{
"member": "2019"
}
]
}
}, {
"member": "TotalAnnee",
"filter": {
"field": {
"uniqueName": "Annee-Gen2"
},
"include": [{
"member": "2020"
}
]
}
}
]
}
]
}
},
"page": 0
}
And when end-user filters in Columns (on "Indicateur" to select 2 values), we receive "select" request as follow:
{
"type": "select",
"index": "FM_pivotTable",
"query": {
"aggs": {
"by": {
"rows": [{
"uniqueName": "Organisation-Gen1"
}
],
"cols": [{
"uniqueName": "Periode-Gen1"
}
]
},
"values": [{
"func": "sum",
"field": {
"uniqueName": "Data"
}
}
]
},
"filter": {
"type": "and",
"value": [{
"field": {
"uniqueName": "Indicateur-Gen1"
},
"include": [{
"member": "RO",
"filter": {
"field": {
"uniqueName": "Indicateur-Gen2"
},
"include": [{
"member": "Passif",
"filter": {
"field": {
"uniqueName": "Indicateur-Gen3"
},
"include": [{
"member": "Charges"
}
]
}
}
]
}
}, {
"member": "RO",
"filter": {
"field": {
"uniqueName": "Indicateur-Gen2"
},
"include": [{
"member": "Actif",
"filter": {
"field": {
"uniqueName": "Indicateur-Gen3"
},
"include": [{
"member": "ImmoIncorp"
}
]
}
}
]
}
}
]
}
]
}
},
"page": 0
}
Since when doing multi-select, if it is:
* On reportFilter field "Annee": we should respond with the agregated value so in this case, only an agregated value: sum of values of "2019"+"2020"
* On Column field "Indicateur": we should respond with the selected members, so in this case with 2 values: value for "Charges" AND value for "ImmoIncorp"
We need to have the information if the filter has been made on the reportFilters or on the table (on 2nd field of columns in this case),
Since it not available in the "select" request: "Indicateur" is not sent in "select" request as a column field, when filtering on it.
So we've tried to use the method "getReportFilters()" (we've noticed the following mention in the documentation: If data load is in progress an empty array will be returned.)
so it returns an empty array when loading (on all events triggered before "select" request: e.g. "beforegriddraw", "querycomplete", "runningquery", and only got the response filled on events triggered after the "select" (too late to respond with appropriate values) request: e.g. "querycomplete", "update", "aftergriddraw".
But we need this information when dealing the "select" request.
Would it possible to return this information, either:
* in the select request: add all the fields (2nd field or more) on columns and rows?
* in the response of the getReportFilters() on event triggered before "select" request?
Or do you have a workaround/another approach to fulfil this requirement?
Thanks in advance,
Cheers,
Hamza
⋅ March 1, 2022
Hello Hamza,
Thank you for posting your question.
Our team is already working on the case.
We will get back to you as soon as we have an update.
Regards,
Dmytro
⋅ March 1, 2022
Hello Hamza,
Thank you for giving us some time.
As we understand, you are looking for a way to distinguish whether:
report filters area was filteredcolumns/rows area was filtered
If so, could you please provide a bit more details on your use case? We would like to understand better why distinguishing filters in rows/columns vs report filters is important for you.
Also, could you please let us know which custom data source API version you are sending in the /handshake response?
For example:
// handshake request:
{
"type": "handshake"
"version": string
}
// handshake response:
{
"version": "2.9.19"
}
This will help us to understand the situation better to solve your case in the best way.
Thank you, and looking forward to your response.
Kind regards,
Vera
⋅ March 3, 2022
Hello Vera,
Thank you for quick response.
More details about the use case are:
- Version of custom data source used is: 2.9.19 with pivot-table.
- As for why we want to distinguish between a filter in:
Please let me know if you need more details.
Best regards,
Hamza
⋅ March 4, 2022
Hello, Hamza,
Thank you for your reply and for specifying more details.
When implementing custom data source API, there is no need to distinguish filters from report filters or columns/rows since the response from the server should be similar for both cases.
To illustrate this, we have prepared a sample with a slice structure similar to the one you described:
Country in report filtersCategory in rowsBusiness type and Color in columnsCase 1: no filters
https://jsfiddle.net/flexmonster/z24L50ge/
If no filters are applied, the response to the /select request contains four types of objects:
You can check the structure of the response to the /select request in the Network tab.
Case 2: report filters with multiple items selected
https://jsfiddle.net/flexmonster/redj4cnL/
If multiple items are selected in report filters, the structure of the response to the /select request must remain the same. There might be fewer objects in case there is no data for specific cells. However, all four types of objects described in Case 1 must be included.
Case 3: column filters with multiple items selected
https://jsfiddle.net/flexmonster/846L2brc/
If multiple items are selected in column filters, the structure of the response to the /select request must remain the same. There might be fewer objects in case there is no data for specific cells. However, all four types of objects described in Case 1 must be included.
Our team suggests comparing network requests and responses for these cases. Also, the following page from our documentation might be of help: https://www.flexmonster.com/api/select-request-for-pivot-table/.
We hope our explanation clears things up. Do not hesitate to contact us if further questions arise.
Kind regards,
Tetiana
⋅ March 11, 2022
Hello,
Thank you for the response.
Concerning the response structure cases, we've used your recommandations.
About the main issue, here is the flow on how to reproduce:
Given a FlexMonster reportFilter
Given a multi-select filter is done on that FlexMonster reportFilter, so in select response we send the aggragated value calculated by our cube (we send the aggregated value as a one value into a cell with the corresponding keys and it works OK.
When dragging a reportFilter dimension & dragging into pivot table
Then for the first display following dropping, the value displayed at top level remains the aggregated value, although when doing filter multi-select on column, it should send the real value on the top cell, and the values of each select item separetely
Note that this only occurs only following the dropping
as for the next times it is OK, this because be we are sending the value of getReportFilters() as follow that we send as a parameter to the handling of the select request:
update: function() {
reportFiltersDimensions = flexmonster.getReportFilters();
}
and it is at first null, then contains the information.
Please let me know if you need more details...
Thank you and best regards,
Hamza
⋅ March 14, 2022
Hello, Hamza,
Thank you for your reply.
If we understand correctly, you are sending fields that are in reportFilters along with Flexmonster's requests, and your server relies on these additional fields to provide the response. Could you please let us know what you are using the report filters for on your server?
If so, we would like to explain that the custom data source API requests are complete. This means you don't have to send fields that are in reportFilters along with the requests. Filtering should work well with the designed request/response structure without having to complement Flexmonster's requests with additional parameters.
Please see the following example using our sample custom data source API endpoint: https://jsfiddle.net/flexmonster/o9gfejn3/.
In the JSFiddle, when a report filter dimension (for example, "Country") is moved into the pivot table (for example, to rows or columns), the data is represented on the grid correctly.
The example also logs Flexmonster's requests for a better understanding. In addition, you are welcome to check out the request and response structure (by inspecting the page and navigating to the network tab).
Please let us know if this helps to get the filters working on your end.
Looking forward to your response.
Kind regards,
Vera
⋅ March 23, 2022
Hello, Vera,
Sorry for the delay.
Here the concrete use case
Initial state: "InitialState.png"
Filter multi-select on reportFilter: "FilterMultiSelectOnReportFilter.png"
Doing so results as displaying the aggregated value for selected items (calculated server-side) as follow: "ResultAfterMultiSelectOnReportFilter.png"
Until now it’s OK.
Next step is to drag “Annee” report filter & drop it as last column (with the multi-select already set): "ResultAfterDragDropReportFilterOnColumns.png"
Whereas after expanding on “Total Periode” to display “Indicateur” we got: "ResultAfterExpandingOnColumn.png"
Notice the difference in values for cell at Line 4 / Column 3:
Please let me know if it's not clear / you need more details…
Thank you and best regards,
Hamza
Thank you
⋅ March 23, 2022
attached files
⋅ March 24, 2022
Hello,
Thank you for reaching out to us.
Our team is currently working on your request. We will come back to you with the results.
Best Regards,
Maksym
⋅ March 25, 2022
Hello, Hamza!
Our apologies for the delayed reply.
To make sure we understand the situation correctly, we would like to clarify a few points.
As we see, currently, when a filter is applied, it affects the whole pivot table. This happens for both report filters and filters applied to a certain field (in columns/rows). For a better understanding, we prepared a JSFiddle example. For illustration purposes, we will focus on the value under "Bikes" - 112437 (see the JSFiddle):
"France" and "Germany" in "Country", the value under "Bikes" changes to 27896."Country" to columns, the value under "Bikes" stays the same (27896). This is the expected behavior since the filter on "Country" is still applied. If we expand all the way down, we will see only the selected countries ("France" and "Germany").
As we understood, instead you would expect the following behavior:
"France" and "Germany" in "Country", the value under "Bikes" changes to 27896."Country" to columns, the value under "Bikes" changes back to the original value (112437), as if the filter on "Country" is not applied. However, if we expand all the way down, we should still see only the selected countries ("France" and "Germany").Could you please confirm if this is the behavior you are trying to achieve?
Looking forward to hearing from you.
Best Regards,
Maksym
⋅ March 28, 2022
Hello back, Maksym,
Thank you for the example that illustrates perfectly the case.
I Confirm that the expected behavior is the one that you describe :
"Country" to columns, the value under "Bikes" changes back to the original value (112437), as if the filter on "Country" is not applied. However, if we expand all the way down, we should still see only the selected countries ("France" and "Germany").This is maily because when a filter is set on column, we want that the the value displayed is as of no-filter is made (value retrieved from server) and when expanding to see the selected items.
Thank you and best regards,
Hamza
⋅ March 29, 2022
Hello, Hamza!
Thank you for making it clear for us.
Please note that this behavior contradicts pivot table logic. The subtotals are the sum of all the values below them. Showing only the filtered values with subtotal containing sum of all values is considered to be an unexpected behavior. As a result, there is no direct approach for achieving the described functionality.
Feel free to contact us in case other questions arise.
Best Regards,
Maksym