【Question】

In a line chart with an X axis divided by scales, we can display columns by different intervals according to the change of scales in a certain frame. For example, we can display a column every 5 mins for the past 1 hour, or every 2 hours for the past 24 hours, or every 2 days for the past week. Now I want to make the aggregate values change according to the change of time frame. Specifically, I need to get dynamic units and intervals in a grouping operation when the time frame changes from 1 hour to 1day then to 1 week.

I found a solution on the web saying that I can change the unit of scales:

importPackage(Packages.org.eclipse.birt.chart.model.data.impl);
importPackage(Packages.java.text);
importPackage(Packages.java.util);
importPackage(Packages.java.lang);

function beforeGeneration(chart, icsc)
{ //01 hour 02 day 03 week
  var type = icsc.getExternalContext().getScriptable().getParameterValue(“type”);
  var stime = icsc.getExternalContext().getScriptable().getParameterValue(“start”);
  var etime = icsc.getExternalContext().getScriptable().getParameterValue(“end”);
  var sh = icsc.getExternalContext().getScriptable().getParameterValue(“sh”);
  var eh = icsc.getExternalContext().getScriptable().getParameterValue(“eh”);

 var format = new SimpleDateFormat(“yyyy-MM-dd”);
 var timeFormat = new SimpleDateFormat(“yyyy-MM-dd HH:mm”);
 var scal = Calendar.getInstance();
  var ecal = Calendar.getInstance();
  var scalH = Calendar.getInstance();
  var ecalH = Calendar.getInstance();

 var sd = format.parse(stime);
  var ed = format.parse(etime);
  var sdh = timeFormat.parse(sh);
  var edh = timeFormat.parse(eh);
  scal.setTime(sd);
  ecal.setTime(ed);
  scalH.setTime(sdh);
  ecalH.setTime(edh);

  var xAxisArray = chart.getAxes();
  xAxisArray[0].setCategoryAxis(false);
   //xAxisArray[0].getGroup().setInterval(2);
//
// chart.setUnits(“Days”);

 if(type==‘01’){
 xAxisArray[0].getScale().setMin(DateTimeDataElementImpl.create(scalH.getTimeInMillis()));    xAxisArray[0].getScale().setMax(DateTimeDataElementImpl.create(ecalH.getTimeInMillis()));
  xAxisArray[0].getScale().setUnit(xAxisArray[0].getScale().getUnit().MINUTES_LITERAL);
   xAxisArray[0].getScale().setStep(5);

 }else if(type==‘02’){
 xAxisArray[0].getScale().setMin(DateTimeDataElementImpl.create(scal.getTimeInMillis()));
  xAxisArray[0].getScale().setMax(DateTimeDataElementImpl.create(ecal.getTimeInMillis()));
   xAxisArray[0].getScale().setUnit(xAxisArray[0].getScale().getUnit().HOURS_LITERAL);
   xAxisArray[0].getScale().setStep(1);

} else {
 xAxisArray[0].getScale().setMin(DateTimeDataElementImpl.create(scal.getTimeInMillis()));
   xAxisArray[0].getScale().setMax(DateTimeDataElementImpl.create(ecal.getTimeInMillis()));
   xAxisArray[0].getScale().setUnit(xAxisArray[0].getScale().getUnit().DAYS_LITERAL);
   xAxisArray[0].getScale().setStep(1);

}
}

【Answer】

A reporting tool is good at handling computation with a consistent condition. But they are not that great when handling dynamic conditions. Instead of letting BIRT handle both data processing and report building, a better alternative is first preparing data source outside of the reporting tool and then pass the result set to it to build the report. Here you can use SPL (Structured Process Language) to prepare data. It’s convenient because the language provides a rich library of set operation functions. Below is the SPL script:

A1: Create a sequence of time intervals.

A2-B2: For the past 1 hour, generate a time interval every 5 mins and there are altogether 12 intervals.

A3-B3: For the past 1 day, generate a time interval every 1 hour and there are altogether 24 intervals.

A4-B4: For the past 1 week, generate a time interval every 1 day and there are altogether 7 intervals.

A5: Perform aggregation by different intervals.

A6: Return result set to the reporting tool.

You can connect to esProc from BIRT via JDBC interface and call the SPL script as calling a stored procedure. For more information, see How to Call an SPL Script in BIRT.