How to retrieve monthly data from database?

Hi Team, I have entity which contains my whole application data, but now I just want to retrieve data by monthly basis because it’s need to display graphical representation on monthly basis. So,  can I retrieve data for each month in one microflow? Thanks and Regards, Samarth Jadhav
3 answers

If my assumption is right, you want to have group by your entire dataset by month and pass that dataset to show in graphs.

Without understanding your domain model, it is bit difficult to answer this question.

- Try the suggestion proposed by Ronald

- Or read about OQL in Mendix. With OQL you can write SQL like queries. OQL can be used in report grids. But you can also choose to write a java action, which will execute the OQL and return the dataset so you can process it later the way you need with NPE

If both options does not answer your question, probably rephrase the question with more details


Look at the documentation here:

You have the [%BeginOfCurrentMonth%] or the UTC variant. And you can use things like '[%%BeginOfCurrentMonth%] - 3 * [%MonthLength%]' to retrieve other moths. You could also create a variable and use the addmonths function to change dates.






If you want to retrieve the dataset of a single month in a microflow I would do it like this

  1. Create DateTime variable to capture MonthStart.
  2. Create DateTime variable to capture MonthEnd.
    Both to avoid calculating directly in XPath, which cannot be debugged easily and using variables you can have more precise controle.
    In my example I’m using [%BeginOfCurrentMonth%], because I needed a date value. In your case it depends on which month you want to query.
    Using functions like trimToMonths, and addMonths can help you to capture a specific month.
  3. Add a retrieve from database activity
  4. Query the Entity you need where you use the both variables to compare the date.

    To retrieve multiple months in a single microflow in separated batches
  5. Add two change variable activity to shift the MonthStart & MonthEnd variables one or more months using previous mentioned functions.


BUT; I wonder why you want to have multiple lists of data in a microflow. Thus; what is your goal, why do you need them and what do you want to do with them.
Because my gut feeling says that you want to display them in a page as items per month or in a graph. in both cases you  don’t need this microflow. ;-)