Question

Prep data for reporting

0

We have been building customer account review reports using Microsoft Excel pivot tables for quite some time. I would like to transition the reporting to Mendix. Ideally, I would take our customer data in it's raw exported format from other systems, and perform all of the data aggregation within Mendix (export > import > clean with logic), rather than exporting > cleaning > importing to mendix. I have started this proof of concept by building some dashboards using the Chart.Js widget which is working great. However, I imported the data in the perfect format for Chart.Js, and did not use Mendix to clean and aggregate. To test the business logic, I would like to take my data that is stored in an entity, and calculate the "count" of rows based on the "month" in a particular date field. I would then use this data to send to Chart.Js for display. As I am researching the best ways to do this, I am coming across OQL queries and datasets, but I can't tell if this is the best road to go down. Can anyone recommend resources on what I am trying to accomplish, using my "count by months" as an example? (Should I loop through my persistable entity to create non-persistable objects for reporting? )

asked 2017-09-13

Jeff Watts

2 answers

2

Hi Jeff,

Unfortunately there is no way that I know of to insert a group by statement in an XPath query.
Data sets support OQL including group by, but they can only be used in reporting widgets (personally never used them).
Here are three options that you have (I would love to hear other ideas from people on the forum):
* use multiple database retrieves followed by an aggregate list, in each retrieve add a filter on the date attribute to only get the dates within a certain month. (addMonths is your friend)
* retrieve everything in a single query and then do the grouping yourself in a microflow or a java action. I would personally prefer a java action because Mendix has no equivalent to a HashMap)
* execute a custom OQL query in a java action

The last option makes me a bit anxious, but it is the same approach that the Excel export module utilizes.
Hope this helps,
Andrej

answered 2017-09-13

Andrej Gajduk

Ronald Catersels · Accepted Answer · 2017-09-13

I think you need to take two things into account:

1) Is real time data important? Because it does make a difference if the graph should give the current state or if the state of yesterday is good enough.

2) Maintainability. Should everyone be able to grasp how the data is gathered or is this a setup once and never to change action?

Making the data all in a microflow would be my first route. It will make the logic visible by every modeler. But it could be that by other means are faster. If you need near real time data this does matter.

And I would create special entities that hold the aggregated data. You could either refresh that data in a scheduled event or update the data by triggers in your model when the data actually changes.

Regards,

Ronald

[EDIT]

To filter in a microflow by month or year I always create two dates. One at the first of the month and use addMonth to get the first day of the next month. Then you can filter the date by using somedate >= Date1 and < Date2. For years you can do the same and use the addYear function.