Multi-Dimensional Sorting Table Examples

1)    The following coding example sorts ascending by CAR.

/**

*

* A simple method creates a sorted 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 SortOLAPTableDisplay(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);

if (label.equalsIgnoreCase("CARS"))

{   tdi.setSortDirection(com.sas.iquery.metadata.business.DataItemActionType.SORT_HIERARCHICAL_ASCENDING);

}

}

}

 

// Perform query and get results.

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

selections.add( ds );

ds.setID("SortOLAPTable");

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 sorts ascending by the value of SALES_SUM.

/**

*

* A simple method creates a sorted 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 SortOLAPTableDisplay(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();

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

 

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

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

               tdiList.add(tdi);

               sortCriteria.add(tdiList);

               ds.setSortCriteria(sortCriteria, Role.ROW);

 

}

else

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

}

}

 

// Perform query, apply sort to query, and get results.

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

selections.add( ds );

ds.setSortCriteria(sortCriteria, Role.ROW);

ds.setSortDirection(BusinessQueryActionType.SORT_HIERARCHICAL_ASCENDING, Role.ROW);

ds.setID("SortOLAPTable");

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;

}