There are three levels of filter for multi-dimensional tables. In the below examples, we will discuss the most inner filters. These are filters that are applied as a step to either the business query of the dataItem.
1) The following coding example sets a member filter. The rank filter allows only a list of members [CARS].[All Cars].[Chevy], [CARS].[All Cars].[Chrysler] from the CARS dimension.
* @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 FilterOLAPTableDisplay(java.util.List dsDataItems, com.sas.iquery.metadata.business.DataSelection ds) throws Exception
{
// Define the content of the member to be filter on.
String[] memberNames = {"[CARS].[All Cars].[Chevy]","[CARS].[All Cars].[Chrysler]"};
com.sas.iquery.metadata.business.step.olap.DataItemMemberFilter step = new com.sas.iquery.metadata.business.step.olap.DataItemMemberFilter(memberNames, com.sas.iquery.metadata.business.step.olap.DataItemMemberFilter.LIST_MEMBER_FILTER_TYPE);
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
// Assign member filter step to CARS
if ( label.equalsIgnoreCase("CARS") )
{ tdi.addStep(step);
ds.addResultItem(tdi, com.sas.iquery.metadata.business.Role.ROW);
}
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 );
ds.setID("MemberFitlerListDataItemOLAPTable");
java.util.Map 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 a range for a member filter on a specific dataItem. This rank uses a range of members ROW - [CARS].[All Cars].[Chevy] : [CARS].[All Cars].[Toyota]
/** *
* A simple method creates a filtered 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 FilterOLAPTableDisplay(java.util.List dsDataItems, com.sas.iquery.metadata.business.DataSelection ds) throws Exception
{
// Define the content of the member to be filter on.
String[] memberNames = {"[CARS].[All Cars].[Chevy]","[CARS].[All Cars].[Toyota]"};
com.sas.iquery.metadata.business.step.olap.DataItemMemberFilter step = new com.sas.iquery.metadata.business.step.olap.DataItemMemberFilter(memberNames, com.sas.iquery.metadata.business.step.olap.DataItemMemberFilter.RANGE_MEMBER_FILTER_TYPE);
// Create connection and assign roles to data items.
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
// Assign member filter step to CARS
if ( label.equalsIgnoreCase("CARS") )
{ tdi.addStep(step);
ds.addResultItem(tdi, com.sas.iquery.metadata.business.Role.ROW);
}
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 );
ds.setID("MemberFitlerRangeDataItemOLAPTable");
java.util.Map 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 a filter for a particular navigation point. In this filter example, the navigation is set to Drill to Level "[CARS].[COLOR]".
* A simple method creates a filter 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 FilterOLAPTableDisplay(java.util.List dsDataItems, com.sas.iquery.metadata.business.DataSelection ds) throws Exception
{
// Create connection and assign roles to data items.
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
// Assign member filter step to CARS
if ( label.equalsIgnoreCase("CARS") )
{ com.sas.iquery.metadata.business.step.olap.DataItemNavigationFilter step = new com.sas.iquery.metadata.business.step.olap.DataItemNavigationFilter(com.sas.iquery.metadata.business.step.olap.DataItemNavigationFilter.LEVEL_NAVIGATION_FILTER_TYPE, "[CARS]", "[COLOR]");
step.setActionType(com.sas.iquery.metadata.business.step.StepFilterInterface.INITIAL_ACTION_TYPE);
tdi.addStep(step);
ds.addResultItem(tdi, com.sas.iquery.metadata.business.Role.ROW);
}
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 );
ds.setID("NavigateFilterDataItemOLAPTable");
java.util.Map 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 a filter on an entire query based on a databased value. This example applies filtering to the cross joining of both month and car based on the value in [Measures][Sales_Sum] being greater than 20000.
* A simple method creates a filter 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 FilterOLAPTableDisplay(java.util.List dsDataItems, com.sas.iquery.metadata.business.DataSelection ds) throws Exception
{
// Define number value for databased comparison.
int val = 20000;
Integer vall = new Integer(val);
Object value = vall;
// Define operator
ComparisonOperator operator = ComparisonOperator.COMPARE_GT;
// Set up member list
List measures = new ArrayList();
// Create connection and assign roles to data items.
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 dataitems will be assigned a role of ROW
if ( label.equalsIgnoreCase("SALES_SUM") )
{ ds.addResultItem(tdi, com.sas.iquery.metadata.business.Role.COLUMN);
measures.add(tdi);
}
else
{ ds.addResultItem(tdi, com.sas.iquery.metadata.business.Role.ROW);
}
}
// Create step for dataselection filter and apply step to crossjoin axis.
com.sas.iquery.metadata.business.step.olap.BusinessQueryDataBasedFilter step = new com.sas.iquery.metadata.business.step.olap.BusinessQueryDataBasedFilter(measures, operator, value );
ds.addStep(step, com.sas.iquery.metadata.business.Role.ROW);
// Perform query and get results.
java.util.List selections = new java.util.ArrayList();
selections.add( ds );
ds.setID("FilterQueryOLAPTable");
java.util.Map 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;
}
5) The following coding example sets a filter an entire query based on a databased value for "Chevy" cars. This example applies filtering to the cross joining of both month and dealer based on the value in [Measures][Sales_Sum] being equal to 17000 for "Chevy" cars.
* A simple method creates a filter 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 FilterOLAPTableDisplay(java.util.List dsDataItems, com.sas.iquery.metadata.business.DataSelection ds) throws Exception
{
// Define number value for databased comparison.
int val = 17000;
Integer vall = new Integer(val);
Object value = vall;
// Define operator
ComparisonOperator operator = ComparisonOperator.COMPARE_EQ;
// Set up member list
List memberNames = new ArrayList();
com.sas.iquery.metadata.expr.ResourceScope scope = com.sas.iquery.metadata.expr.ResourceScope.BUSINESS_AND_PHYSICAL_SCOPE;
int type = ExpressionTypes.EXP_TYPE_CHARACTER;
com.sas.iquery.metadata.expr.ExpressionInterface expr1 = com.sas.iquery.metadata.expr.StringExpressionUtil.getInstance().newExpression(Map,"[CARS].[All CARS].[Chevy]", type, scope);
memberNames.add(expr1);
// Create connection and assign roles to data items.
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);
memberNames.add(tdi);
}
else
{ ds.addResultItem(tdi, com.sas.iquery.metadata.business.Role.ROW);
}
}
// Create step for dataselection filter and apply step to crossjoin axis.
com.sas.iquery.metadata.business.step.olap.BusinessQueryDataBasedFilter step = new com.sas.iquery.metadata.business.step.olap.BusinessQueryDataBasedFilter(memberNames, operator, value );
ds.addStep(step, com.sas.iquery.metadata.business.Role.ROW);
// Perform query and get results.
java.util.List selections = new java.util.ArrayList();
selections.add( ds );
ds.setID("FilterQuery2OLAPTable");
java.util.Map 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;
}