asked on December 18, 2019
Hi,
Our reports break down data by year, month, or quarter. I'd like to be able to add the previous time unit to the report as a row.
For example, let's say a certain category of product sold $100,000 of goods in November. I'd like to be able to drill down into the row and find out that the total goods sold in October was $90,000. Same if I'm looking at data for 2019 -- i'd like to see the 2018 data. If I'm looking at quarter 3, i'd like to see quarter 2, etc.
I'm not sure how to accomplish this via the setup we have now. Perhaps with calculated values? Please advise.
Thanks.
⋅ December 19, 2019
Hello, David,
Thank you for reaching out to us.
In case the previous time interval is presented within the data set containing the current interval, it can be included in the report and displayed as a regular field.
If you are using data type "year/quarter/month/day" in order to break down data, the desired functionality can be achieved using filtering.
For example, in order to display quarter #2 with quarter #1 that would be represented in the role of a previous time interval, filtering has to be adjusted in the way similar to the following:
{
"uniqueName": "Date",
"levelName": "Date.Quarter",
"filter": {
"members": [
"date.[2019].[quarter 1]",
"date.[2019].[quarter 2]"
]
}
}
Check out an example demonstrating such an approach.
Please let us know if the described approaches are suitable for you. Otherwise, our team would like to kindly ask you to provide more information about your requirements. It would be very useful if you could provide some graphical presentation of your needs, e.g., screenshots.
Kind regards,
Illia
⋅ January 7, 2020
Hi Illia,
Circling back on this... Our data source for these reports is JSON. Our app builds a query, queries our database, returns the results as JSON and displays it in Flexmonster. Looking through your docs, I don't see a way to set the "type" of the date data object like you're doing in your fiddle.
For example, I tweaked the filters in your fiddle to look like this:
"columns": [{
"uniqueName": "[Measures]"
},
{
"uniqueName": "Date",
"levelName": "Date.Year",
"filter": {
"members": [
"date.[2019]"
]
}
}
],
And then I was able to get the "Quarter/Month/Day" dialog. But the date fields in my report don't have that option. How can I set the "type" of date?
⋅ January 8, 2020
Hello, David,
Thank you for your feedback.
Flexmonster provides the possibility to define the datatype explicitly through the meta-object of the JSON data set. The meta-object is presented by the first element of an array if your data is presented as an array of objects or the first array if your data set is an array of arrays.
The data type of the specific hierarchy can be defined using the following construction:
"Hierarchy_Name" : { type: "type_name" }
Detailed information about data types and their usage can be found by following the links: JSON data types.
Such an approach is used within the previously provided JSFiddle.
However, in case the data types have to be defined within the report, the mapping property of the dataSource object has to be used.
The mapping object has to have the following structure:
{
"ColumnA": { type: "type_name" },
"ColumnB": { type: "type_name" }
...
}
Please, find out a JSFiddle demonstrating usage of the mapping property.
We hope it helps.
Please contact us in case additional questions arise.
Best regards,
Illia
⋅ January 8, 2020
Hi Illia,
Thanks for the demonstration of the mapping object.
I am having trouble setting the mapping property using setReport(). (I would like to add the mapping on the front-end if possible.) I'll do something like this in the console:
var report = flexmonster.getReport()
report.dataSource.mapping = { 'bill_date_text': { "type": "year/quarter/month/day" } }
flexmonster.setReport(report)
But the mapping property remains unset. I can change other values of the dataSource object, like the `filename`, without incident.
⋅ January 9, 2020
Hello, David,
Thank you for contacting us.
We recommend applying the mapping in the way the mapping object would contain all existing fields from the data set. Otherwise, unincluded fields will be ignored by the component.
Please check out an example we have prepared for you, demonstrating the approach described above.
More information about the mapping object and its properties can be found following the link: Mapping object.
We hope it works for you.
Feel free to contact us in case of additional questions.
Best regards,
Illia