Ad Hoc report Design : Aggregation on a period of time like month

Hello,

I would like to get your opinion on a relative design point on Ad Hoc reports:

Do you think it is possible to design a report with an aggregation by period (For example, the number of orders per month) while M3 only stores a date which can only be used as in the sort order to achieve this?

Is this a limitation of using Ad Hoc? At best it can select a date range
Do you see any workaround to approach this need?

Thank you for your feedback

Find more posts tagged with

Comments

Denis MARETTE

No idea ?

Legacy Contributor

The M3 system aparently uses the LUCENE query syntax (which you can also directly enter into the search field to query your view (only for connected fields of related tables ofc.) you should be able to use it in Reports, too.

Lucene does not have a std "aggregate" function like SQLs MAX() or COUNT(),

But you might get results with this combination:
Lucene Query Syntax - Lucene Tutorial.com
scroll down to "range searches" and combine it with

Faceting Hibernate Search 3.4: Faceting, perf and more - In Relation To
but beware, i never done this myself since honestly, it would be much faster and more convenient to use a std SQL tool towards your Database or Datalake(DWH)

but knowing that M3 uses Lucene behind the scenes was sometimes a life changer for me.

tdouglass

Roland Z, quick question. When you say SQL tool toward database, are you on the cloud? I didn't think there was a SQL tool that could connect to the M3 database? What have you used?

magnus-tallqvist

The usefulness of function search in aggregation requirements all depends on the requirement as such. M3 has an API FACETMI where you define the table and the query to be used for the filtering. Returned information are facets and their count. Facets are traditionally used in web shops to simplify filtering and give an idea of the number of possible hits for that particular filter (see Amazon left hand once you selected a category as an example. However facets can also be used as a fast statistical method to give insights in aggregated data. That is for example used in many of the M3 standard homepages (the widgets displaying a number). If the requirement is to understand the number of records with a particular filter then facets are the tool to be used. If the number is to aggregate information like quantities etc then it is the wrong path to take.

Legacy Contributor

we are on Multitennant Cloud.. yes and you can perfectly connect to the datalake via JSON with basically any query builder that supports this.

so "technically" you are not connecting to your hot Database in the cloud (so you cant update or create datasets ) but you connect to the Datalake where the information is refined and you can get it from there.

Denis MARETTE

Thank you for sharing this knowledge

Denis MARETTE

Hi @magnus-tallqvist

When you say "If the number is to aggregate information like quantities etc then it is the wrong path to take" , what do you suggest as an alternative way ?

Copyright © 2025 Infor. All rights reserved.