How to realize irregular month Statistics by Birt
Aggregator implements irregular month statistics in Birt and other report tools
In business reports, it is often necessary to make grouping statistics based on time periods, especially by month. In general, it is easy to group by natural month, but sometimes it is necessary to group by irregular month. For example, an enterprise begins to implement some special pricing strategy or marketing activity on January 16, so it wants to count the sales of its products on the 16th of each month. The so-called irregular month here refers to: if the starting time is 2013-01-16, then 2013-01-16 to 2013-02-15 as a group, 2013-02-16 to 2013-03-15 as a group. More specifically, if the start time is 2013-01-31, then 2013-01-31 to 2013-02-27 may be required as a group, and 2013-02-28 to 2013-03-30 as a group, that is, starting with the last day of the month.
How's it going? Aren't you guys a little silly? For reporting tools such as Birt, expressions grouped by natural month are generally provided, but they are also difficult to deal with such irregular month grouping expressions. If we want to solve this problem, we often need to write a program to implement a custom report dataset, and grouping according to this rule in the program, not only the programming workload is not small, but more importantly, it is basically impossible to be universal.
Now, if you introduce an aggregator into the reporting tool, it will be easy to solve this problem. Let's take the Birt reporting tool as an example to explain the process of implementation. For other reporting tools, it's pretty much the same.
In this example, the total monthly sales of the enterprise from 2013-01-16 to 2013-08-15 are counted. The raw data in data table salesall is as follows:
Finally, you need to get the target table shown in the following figure:
Let's take a look directly at the aggregator's SPL code to solve this problem:
AB1=connect ("demo")
2=A1.query ("select OrderID,Amount,OrderDate from salesall where OrderDate > =? and OrderDate"
