Shulou(Shulou.com)06/03 Report--

Source:

   https://forums.opentext.com/forums/discussion/173783/dynamic-grouping-of-crosstab-in-birt#latest

In the business report of   , it is sometimes necessary to carry out dynamic cross-grouping statistics based on the time period, and realize dynamic grouping by day, week, month and year according to the length of the time period. For example, according to the business needs, an enterprise needs to realize the order statistics within a period of time according to two parameters (start date and end date). The statistical rules are as follows:

   example: calculates the difference between dates based on the parameter values entered.

   var diff = end date-start date-number of days apart

   if (diff 15)

     groups date groups to display as weeks

   if (diff > 30)

     groups date groups to display as months

   if (diff > 365)

     groups date groups to display as years

Aren't    friends a little overwhelmed? The above problem is essentially a problem of data preparation, but the code of SQL or scripted data sources is difficult to write and the workload is heavy; the way of using report hidden columns for the second time is not common and very awkward. Then, a better solution is to introduce an aggregator into the reporting tool, which will be easy to solve all kinds of problems. Let's take the Birt reporting tool as an example to introduce the implementation process. For other reporting tools, it's pretty much the same.

   in this example, according to the parameter input, the total number of orders, freight charges and order amounts of the enterprise during the period from 2012-07-04 to 2014-05-06 are counted. The raw data in the data table "ORDERS" is as follows:

  , let's look directly at the aggregator's SPL code to solve this problem:

ABC1=connect ("demo") / / Connect to database 2=A1.query ("select ORDERID,ORDERDATE,FREIGHT,ORDERAMOUNT from ORDERS where ORDERDATE > =? and ORDERDATE")

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.