Custom Dimensions

The examples above use database field values ( wr.status, wr.prob_type, wr.dp_id ) as dimension values. They produce a data record with aggregated values for each dimension field value (1D), or for each combination of two dimension field values (2D).

Sometimes the dimension should be derived from underlying database field values. For example, the view may need to group work requests according to their priority. If the wr.priority field contains numerical values from 0 to 100, you might want to establish a set of ranges, for example "Emergency" = (75 < wr.priority <= 100) . These ranges can be defined in SQL as shown below:

String rangeExpr = "CASE " +
    "WHEN priority > 75 AND priority <= 100 THEN 'Emergency' " + 
    "WHEN priority > 50 AND priority <= 75 THEN 'One Week' " + 
    "WHEN priority > 25 AND priority <= 50 THEN 'One Month' " +
    "ELSE 'Eventually' " +
    "END";

String sql = "SELECT " + rangeExpr + " AS urgency, COUNT(wr_id) AS total_requests " + 
    "FROM wr GROUP BY " + rangeExpr;

List records = DataSourceFactory.createDataSource()
    .addTable('wr')
    .addField('wr_id')
    .addField('priority')
    .addVirtualField("urgency")
    .addVirtualField("total_requests", DataSource.DATA_TYPE_NUMBER)
    .addQuery(sql)
    .getRecords();

DataSetOneDimension dataSet = new DataSetOneDimension();
for (int i = 0; i < records.size(); i++) {
    DataRecord record = (DataRecord) records.get(i);
    dataSet.addRecord(record, record.findField("wr.urgency"));
}
context.setResponse(dataSet);