🍉 Meet Flexmonster Pivot Table & Charts 2.9.Check out all hot features!
Get Free Trial

How to work with Year/Quarter/Month/day dates in Custom API?

Cedric Kalume asked on August 25, 2020

I can get the members request to work but I don’t know how to format the select request for the pivot table
could you please give me the /select response format

6 answers

Vera Didenko Vera Didenko Flexmonster August 27, 2020

Thank you for reaching out to us. 
Our team would like to explain that the built-in year/quarter/month/day date data type is not implemented for the custom data source API. 
However, it is possible to create such a date data structure by combining separate fields into a multilevel hierarchy.
For example, it is possible to create separate fields representing each level: "year", "quarter", "month", and "day". Then these fields can be united to create a multilevel hierarchy that will represent the year/quarter/month/day date. The multilevel hierarchy can be configured via the mapping property.
Also, for multilevel hierarchies to work for the custom data source API, advanced filters need to be implemented on your server. 
Please see the following Support Forum thread for more details: https://www.flexmonster.com/question/is-it-possible-to-describe-hierarchies-with-the-custom-data-source-api/
For a better understanding of the required response structure for the /select request, please see the requests and responses when a hierarchy level is selected in the following JSFiddle example: https://jsfiddle.net/flexmonster/o4jywt38/ (by inspecting the page and navigating to the network tab). 
Please let us know if this helps. If any further questions arise, please feel free to reach out. 
Kind regards, 

Cedric Kalume August 28, 2020

thank you this should solve my issue

Cedric Kalume November 17, 2020

I have a new use case where my data set contains 5+ date columns and using the hierarchy trick is not enough.
Is there any eta toward the implementation of “year/quarter/month/day” or “year/month/week” in the custom API

Illia Yatsyshyn Illia Yatsyshyn Flexmonster November 18, 2020

Hello, Cedric,
Thank you for reaching out to us.
In case your data set contains two or more date fields that need to be represented as multilevel hierarchies, each separate field needs to be split into corresponding parts representing each level. For example, two fields named “StartDate” and “EndDate” should be split into six different hierarchies: “StartDateYear”, “StartDateMonth”, “StartDateDay”, “EndDateYear”, “EndDateMonth”, “EndDateDay”.
The same approach should be applied when operating with five or more fields.
As for now, our team is not ready to provide you with ETA on native support of “year/quarter/month/day” and “year/month/day” data types for custom data source API.
Therefore, we suggest using the workaround described above.
We hope it works for you.
You are welcome to contact us in case other questions arise.
Kind regards,

Cedric Kalume November 24, 2020

Thanks for your answer, I decided to implement custom hierarchy it’s a lot of work but seems to work.
I have one last question concerning dates is there  a way to add a custom date filter to the custom API or is there a way to have a filter that gives only weekday
for example: on the filter “last day/week/month” there would be a additional value called weekday which would be Monday-Friday only

Illia Yatsyshyn Illia Yatsyshyn Flexmonster November 25, 2020

Hello, Cedric,
Thank you for your feedback. We are happy to hear the suggested approach works for you.
Concerning advanced filtering, we suggest considering the following approach.
Add an additional field with a weekday type and associate it with your multilevel date hierarchy. When the user applies the “weekdays” filter, generate an appropriate Filter Object with “Saturday” and “Sunday” members excluded. Finally, place this field to report filters and apply the configured filter object using the setFilter method.
Please note that a separate weekday field needs to be created for each multilevel date in your data set.
Also, this approach implies creating custom UI control that would allow “weekdays” filtering.
Another way is to deselect weekends manually after opening the corresponding report filter.
We hope it helps.
Please contact us in case other questions arise.
Kind regards,

Please login or Register to Submit Answer