Two-Dimensional Queries

Last updated
Save as PDF

Two-Dimensional Queries

You can use a grouping DataSource to return two-dimensional data set. First, define the DataSource with two grouping fields in AXVW:

<dataSource type="grouping" id="requestsByDepartmentAndBuildingDataSource">
<table name="wr"/>


<field name="dp_id" groupBy="true"/>


<field name="bl_and_fl" groupBy="true" dataType="text">
<title>Location</title>
<sql dialect="generic">bl_id + '-' + fl_id</sql>
</field>


<field name="total_requests" baseField="wr.wr_id" formula="count" dataType="number">
<title>Total Requests</title>
</field>
<field name="total_cost" baseField="wr.cost_est_total" formula="sum" dataType="number">
<title>Total Cost</title>
</field>


<sortField name="dp_id"/>
<sortField name="bl_and_fl"/>
</dataSource>

This DataSource can be used as is with any standard panel such as grid. But you can also load and use it from within a workflow rule code:

DataSourceGroupingImpl ds = (DataSourceGroupingImpl) DataSourceFactory.loadDataSourceFromFile("ab-ex-test-2d.axvw", "requestDs");

You can now use the grouping data source instance to get grouped records and their totals:

List<DataRecord> records = ds.getRecords(restriction);
List<DataRecord> totals = ds.getTotals(restriction);
List<DataRecord> rowSubtotals = ds.getSubtotals(restriction, "wr.dp_id");
List<DataRecord> columnSubtotals = ds.getSubtotals(restriction, "wr.bl_and_fl");

The records list contains grouped records, with one value per "measure" (calculated visible field).

The totals list contains one record for total values, with one value per "measure" (calculated visible field).
The row subtotals list contains records for all "row dimension" (first calculated grouping field) subtotals, with one value per "measure" (calculated visible field).
The column subtotals list contains records for all "column dimension" (second calculated grouping field) subtotals, with one value per "measure" (calculated visible field).

Web Central provides a specialized Data Set class that packages 2D data into format that preserves the grouping field information that can be used on the client:

// specify both grouping field names in the constructor
DataSet2D dataSet = new DataSet2D("wr.dp_id", "wr.bl_and_fl");
dataSet.addRecords(records);
dataSet.addTotals(totals);
dataSet.addRowSubtotals(rowSubtotals);
dataSet.addColumnSubtotals(columnSubtotals);
return dataSet;

On the client the WFR result will be represented by an Ab.data.DataSet2D object.