Multi-Dimensional Calculated Measure Table Example

The following coding example displays a simple multi-dimensional table with a calculated measure.

/**

*

* A simple method creates a result set with a calculated measure.

*

* @return modified  data selection

* @throws Exception

*/

private  com.sas.iquery.execution2.ResultSetInterface SimpleOLAPTableDisplay(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);

}

 

}

 

//Set up calculated measure

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

com.sas.iquery.metadata.expr.ResourceScope scope = com.sas.iquery.metadata.expr.ResourceScope.BUSINESS_AND_PHYSICAL_SCOPE;

int type = ExpressionTypes.EXP_TYPE_NUMERIC;

com.sas.iquery.metadata.expr.ExpressionInterface expr1 = null;

try

{     expr1 = com.sas.iquery.metadata.expr.StringExpressionUtil.getInstance().newExpression(Map,"[Measures].[SALES_SUM]*.10", type, scope);

}

di.setExpression(expr1);

di.setLabel("10% of SALES_SUM");

ds.addBusinessItem(di);

 

// Perform query and get results.

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

selections.add( ds );

ds.setID("CalculatedMeasureOLAPTable");

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;

}