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;

}