The following coding example returns a grand total row as the last row of the result set. Note how the totals are only computed for the height measure with an additive statistic SUM, and not weight which uses AVG.
* A simple method creates a result set that contains totals.
* @param list of data items in an information map.
* @param input data selection
* @return modified data selection
*
* @throws Exception
*/private com.sas.iquery.metadata.business.DataSelection TotalRelationalTableDisplay(java.util.List dsDataItems, com.sas.iquery.metadata.business.DataSelection query) throws Exception
{
//get the SUM and MAX functions as known by the server.
com.sas.iquery.metadata.serverprop.ServerProperties serverProperties = query.getServerProperties();
com.sas.iquery.metadata.serverprop.Function sumFunction = serverProperties.getFunctionByNameID(com.sas.iquery.metadata.serverprop.FunctionNameID.SUM);
com.sas.iquery.metadata.serverprop.Function maxFunction = serverProperties.getFunctionByNameID(com.sas.iquery.metadata.serverprop.FunctionNameID.MAX);
for(int i = 0; i < dsDataItems.size(); i++)
{ com.sas.iquery.metadata.business.DataItem tdi = (com.sas.iquery.metadata.business.DataItem)dsDataItems.get(i);
String label = tdi.getLabel();
// Get columns to be totaled and apply aggregation
if ( label.equalsIgnoreCase("Height") )
{ tdi.setAggregationType(sumFunction);
}
else
if ( label.equalsIgnoreCase("Weight") )
{ tdi.setAggregationType(maxFunction);
}
// Assign Column to all dataitems and add to dataselection.
query.addResultItem(tdi, com.sas.iquery.metadata.business.Role.COLUMN);
}
//Make the query return a final, grand total row last, containing the total
//over all values of a column:
query.setTotalType(com.sas.iquery.metadata.business.BusinessQuery.GRANDTOTAL, com.sas.iquery.metadata.business.Role.COLUMN);
// Get data selection
return query;
}