# Multi-Dimensional Totaling Table Examples

1) The following coding example sets "All Totals" (both sub-total and grand total) on rows.

/**

*

* A simple method creates a totaled result set.

*

* @param list of data items in an information map.

* @param input data selection

* @return modified data selection

*

* @throws Exception

*/

private com.sas.iquery.execution2.ResultSetInterface TotalOLAPTableDisplay(java.util.List dsDataItems, com.sas.iquery.metadata.business.DataSelection ds) throws Exception

// Create connection and assign roles to data items.

java.util.Map results =null;

> com.sas.iquery.dataretrieval.QueryConnector m_queryConnector = new com.sas.iquery.dataretrieval.QueryConnector();

> 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();

> > // Assign Column to Sales_sum measure, all other dataitem will be assigned a role of ROW

> > if ( label.equalsIgnoreCase("Sum of Sales") )

> > > { ds.addResultItem(tdi, com.sas.iquery.metadata.business.Role.COLUMN);

> > > }

> > else

> > > { ds.addResultItem(tdi, com.sas.iquery.metadata.business.Role.ROW);

> > > }

> }

> // Perform query and get results.

> java.util.List selections = new java.util.ArrayList();

> selections.add( ds );

> com.sas.iquery.metadata.business.BusinessQuery bs = (com.sas.iquery.metadata.business.BusinessQuery) ds;

> bs.setTotalType(com.sas.iquery.metadata.business.BusinessQuery.ALLTOTAL, com.sas.iquery.metadata.business.Role.ROW);

> ds.setID("AllTotalOLAPTable");

> results = m_queryConnector.retrieveQueryResultMap( selections );

> // Get Results interface to display table if desired.

> com.sas.iquery.execution2.ResultSetInterface resultSet = (com.sas.iquery.execution2.ResultSetInterface)results.get( ds.getID() );

> return resultSet;

}

2) The following coding example sets "Grand Totals" or "Axis Totals" on rows.

/**

*

* A simple method creates a totaled result set.

*

* @param list of data items in an information map.

* @param input data selection

* @return modified data selection

*

* @throws Exception

*/

private com.sas.iquery.execution2.ResultSetInterface TotalOLAPTableDisplay(java.util.List dsDataItems, com.sas.iquery.metadata.business.DataSelection ds) throws Exception

// Create connection and assign roles to data items.

java.util.Map results =null;

> com.sas.iquery.dataretrieval.QueryConnector m_queryConnector = new com.sas.iquery.dataretrieval.QueryConnector();

> 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();

> > // Assign Column to Sales_sum measure, all other dataitem will be assigned a role of ROW

> > if ( label.equalsIgnoreCase("Sum of Sales") )

> > > { ds.addResultItem(tdi, com.sas.iquery.metadata.business.Role.COLUMN);

> > > }

> > else

> > > { ds.addResultItem(tdi, com.sas.iquery.metadata.business.Role.ROW);

> > > }

> }

> // Perform query and get results.

> java.util.List selections = new java.util.ArrayList();

> selections.add( ds );

> com.sas.iquery.metadata.business.BusinessQuery bs = (com.sas.iquery.metadata.business.BusinessQuery) ds;

> bs.setTotalType(com.sas.iquery.metadata.business.BusinessQuery.GRANDTOTAL, com.sas.iquery.metadata.business.Role.ROW);

> ds.setID("GrandTotalOLAPTable");

> results = m_queryConnector.retrieveQueryResultMap( selections );

> // Get Results interface to display table if desired.

> com.sas.iquery.execution2.ResultSetInterface resultSet = (com.sas.iquery.execution2.ResultSetInterface)results.get( ds.getID() );

> return resultSet;

}

3) The following coding example sets "Sub Total" on rows.

/**

*

* A simple method creates a totaled result set.

*

* @param list of data items in an information map.

* @param input data selection

* @return modified data selection

*

* @throws Exception

*/

private com.sas.iquery.execution2.ResultSetInterface TotalOLAPTableDisplay(java.util.List dsDataItems, com.sas.iquery.metadata.business.DataSelection ds) throws Exception

// Create connection and assign roles to data items.

java.util.Map results =null;

> com.sas.iquery.dataretrieval.QueryConnector m_queryConnector = new com.sas.iquery.dataretrieval.QueryConnector();

> 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();

> > // Assign Column to Sales_sum measure, all other dataitem will be assigned a role of ROW

> > if ( label.equalsIgnoreCase("SALES_SUM") )

> > > { ds.addResultItem(tdi, com.sas.iquery.metadata.business.Role.COLUMN);

> > > }

> > else

> > > { ds.addResultItem(tdi, com.sas.iquery.metadata.business.Role.ROW);

> > > }

> }

> // Perform query and get results.

> java.util.List selections = new java.util.ArrayList();

> selections.add( ds );

> com.sas.iquery.metadata.business.BusinessQuery bs = (com.sas.iquery.metadata.business.BusinessQuery) ds;

> bs.setTotalType(com.sas.iquery.metadata.business.BusinessQuery.SUBTOTAL, com.sas.iquery.metadata.business.Role.ROW);

> ds.setID("SubTotalOLAPTable");

> results = m_queryConnector.retrieveQueryResultMap( selections );

> // Get Results interface to display table if desired.

> com.sas.iquery.execution2.ResultSetInterface resultSet = (com.sas.iquery.execution2.ResultSetInterface)results.get( ds.getID() );

> return resultSet;

}

4) The following coding example sets "Sub Totals" on rows and applies total percentage to the "Cars" subtotaling.

In this case we’ve chosen Cars as the denominator.

private com.sas.iquery.execution2.ResultSetInterface TotalPercentOLAPTableDisplay(java.util.List dsDataItems, com.sas.iquery.metadata.business.DataSelection ds) throws Exception

// Create connection and assign roles to data items.

java.util.Map results =null;

> com.sas.iquery.dataretrieval.QueryConnector m_queryConnector = new com.sas.iquery.dataretrieval.QueryConnector();

> DataItem numeratorMeasure;

> DataItem denominatorCategory;

> 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();

> > // Assign Column to Sales_sum measure, all other dataitem will be assigned a role of ROW

> > if ( label.equalsIgnoreCase("Sum of Sales") )

> > { ds.addResultItem(tdi, com.sas.iquery.metadata.business.Role.COLUMN);

> > numeratorMeasure = tdi;

> > }

> > else

> > if ( label.equalsIgnoreCase("Cars") )

> > { ds.addResultItem(tdi, com.sas.iquery.metadata.business.Role.ROW);

> > denominatorCategory = tdi;

> > }

> > else

> > { ds.addResultItem(tdi, com.sas.iquery.metadata.business.Role.ROW);

> > }

> }

> //Set up totaling for query

> com.sas.iquery.metadata.business.BusinessQuery bs = (com.sas.iquery.metadata.business.BusinessQuery) ds;

> bs.setTotalType(com.sas.iquery.metadata.business.BusinessQuery.ALLTOTAL, com.sas.iquery.metadata.business.Role.ROW);

> //Set up calculated measure

> com.sas.iquery.metadata.business.DataItem di = new com.sas.iquery.metadata.business.DataItem();

> FractionOfTotalExpression fractionExpression = new FractionOfTotalExpression();

> fractionExpression.setNumerator(numeratorMeasure);

> fractionExpression.setDenominator(denominatorCategory);

> di.setExpression(fractionExpression);

> di.setFormat("PERCENT10.4");

> di.setUsage(DataItemActionType.USAGE_AGGREGATE);

> //BusinessQueryOLAPUtil will check Product and find the next inner most dimension which should be country

> ds.addBusinessItem(di, com.sas.iquery.metadata.business.Role.COLUMN);

> // Perform query and get results.

> java.util.List selections = new java.util.ArrayList();

> selections.add( ds );

> results = m_queryConnector.retrieveQueryResultMap( selections );

> // Get Results interface to display table if desired.

> com.sas.iquery.execution2.ResultSetInterface resultSet = (com.sas.iquery.execution2.ResultSetInterface)results.get( ds.getID() );

> return resultSet;

}