# Multi-Dimensional Filtering Table Examples

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.

/**

*

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

}