getReportFilters() with custom data source API or in "select" request

Answered
NOURI 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

12 answers

Public
Dmytro Zvazhii Dmytro Zvazhii Flexmonster 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

Public
Vera Didenko Vera Didenko Flexmonster March 1, 2022

Hello Hamza,
 
Thank you for giving us some time.
 
As we understand, you are looking for a way to distinguish whether:

  • a field in the report filters area was filtered
    or
  • a field in the columns/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

Public
NOURI 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:
 

  • on reportFilters: When an end-user does filter by selecting multiple items, we send as response for the "select" request, the aggregated value (in our case, the sum) for these multi-selected values, by sending only one pair of Key-Value, because it will be displayed in one cell within the pivot-table.
  • on row/columns: When an end-user does filter by selecting multiple items,we send as response for the "select" request, all the values for the filtered items, so as many pairs of Key-Value as the number of selected items, because they will be displayed in different cells within the pivot-table.

Please let me know if you need more details.
 
Best regards,
Hamza
 
 

Public
Tanya Gryshko Tanya Gryshko Flexmonster 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 filters
  • Category in rows
  • Business type and Color in columns

Case 1: no filters
https://jsfiddle.net/flexmonster/z24L50ge/
If no filters are applied, the response to the /select request contains four types of objects:

  1. values only for values to be treated as grand totals
  2. key "Business Type" for values to be treated as totals for "Business Type"
  3. key "Category" for values to be treated as totals for "Category"
  4. keys "Business Type" and "Category" for values to be treated as a regular cell

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

Public
NOURI 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

Public
Vera Didenko Vera Didenko Flexmonster 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

Public
NOURI 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:

  • After dragging/Dropping “Annee” into columns, the value remains 80077
  • After expanding on “Total Periode”, the value becomes 160611, which is the correct state that we want. This is mainly because:
    • When doing a multi-select on reportFilter, we want only the aggregated value for the selected items (calculated server-side within the select request handling)
    • When doing a multi-select on a column indicator, we want the real value stored server side for parent member (in this case "TotalAnnée") and not only the aggregation of selected items value. So that when expanding, we get the value of parent member: 160611 and each of the values for selected items “2019” and “2020”

 
Please let me know if it's not clear / you need more details…
Thank you and best regards,
Hamza
 
 
 
Thank you

Public
NOURI March 23, 2022
Public
Maksym Diachenko Maksym Diachenko Flexmonster 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

Public
Maksym Diachenko Maksym Diachenko Flexmonster 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):

  • After we apply a report filter by selecting "France" and "Germany" in "Country", the value under "Bikes" changes to 27896.
  • When we drag&drop "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:

  • After we apply a report filter by selecting "France" and "Germany" in "Country", the value under "Bikes" changes to 27896.
  • When we drag&drop "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  

Public
NOURI 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 :

  • When we drag&drop "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

Public
Maksym Diachenko Maksym Diachenko Flexmonster 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

Please login or Register to Submit Answer