How to implement irregular month statistics in Birt

In reports, grouping statistics based on time periods are often required, especially by month. Generally speaking, it is easy to divide into groups according to natural months, but sometimes it is necessary to divide into groups according to irregular months. For example, since January 16, a company has implemented some special price strategy or marketing activities, so it wants to count the sales of products on the 16th of each month. The so-called irregular month here refers to: if the starting time is 2013-01-16, then the period from 2013-01-16 to 2013-02-15 will be a group, and the period from 2013-02-16 to 2013-03-15 will be a group. More specifically, if the starting time is 2013-01-31, it may be required to group 2013-01-31 to 2013-02-27, and to group 2013-02-28 to 2013-03-30, that is, “from the last day of the month”.

  
For reporting tools such as Birt, expressions grouped by natural months are generally provided, but it is difficult to deal with such requirements grouped by irregular months. If we want to solve this problem, we often need to write a program to realize the 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 not universal.
  
Now, if esProc is introduced into the reporting tool, it will be easy to solve this problem. Let’s take Birt reporting tool as an example to illustrate the implementation process. For other reporting tools, they are basically the same.
  
In this case, the monthly sales from 2013-01-16 to 2013-08-15 will be counted. The original data in the table salesall are as follows:


We need to get the target representation as shown in the following figure:


esProc’s SPL codes to solve this problem:

	A	B
1	=connect("demo")
2	=A1.query("select OrderID,Amount,OrderDate from salesall where OrderDate>=? and OrderDate<? order by OrderDate",startDate,endDate)
3	=interval@m(startDate,endDate)	=startDate|A3.(elapse@m(startDate,~))
4	=A2.group(B3.pseg(OrderDate);round(~.sum(Amount),2),B3(#))
5	=A4.new(#:Number,#2:TotalAmount,#3:StartDate)
6	>A1.close()
7	return A5

A1 Connect the database.
A2 The order data between the start date and the end date are found from the database order table. StartDate and endDate are the date parameters.
A3 Calculate the monthly intervals based on the start and end date, such as the six-month intervals between 2013-01-31 and 2013-07-31.
B3 Calculate the start date of an irregular month based on the start date and date interval, and insert the start date in the first place. “|” means merge and “~” means the current members of the set, i.e. numbers 1 to 6. With different options, elapse function can correctly calculate various irregular months.
A4 Group A2 according to B3 interval, and count the sales in irregular months, keep two decimal places, and take B3 as the last column. The function pseg returns the parameter, which is the interval number of the order date. The ~ in ~.sum(Amount) represents the current group after grouping. # Represents the current group number of A2.
A5 Take the group number of A4 as Number, the second column as TotalAmount, and the third column as StartDate of the group to generate a new table.
A6 Close the database.
A7 Return A5 as a dataset for the reporting tool.
  
Save the esProc SPL scripts into file orders.dfx and introduce it into Birt report. For introducing SPL scripts into Birt report, please refer to the article 《How to call an SPL script in Birt》.
  
In BIRT, a simple list table is designed as follows:


Reporting tool calls esProc in exactly the same way as calling stored procedures. For example, in BIRT’s stored procedure dataset, call orders (?,?) can be used to call them. After entering the start date parameter 2013-01-16 and the end date parameter 2013-08-15, preview report to see the required target form.