Business Query Rank Filter Use Cases

This rank filter is applied to determine the top count of values under Canada for the cross join of Year and Product Type.

 

 

MDX Generated:

 

WITH 
SET [&1Time] AS '[TIME].[YEAR].MEMBERS' 
SET [&1Productline] AS '[PRODUCTLINE].[PRODTYPE].MEMBERS' 
SET [&1Geographic] AS '[GEOGRAPHIC].[COUNTRY].MEMBERS' 
MEMBER [Measures].[&1Sum of predict] AS '[Measures].[PREDICT_SUM]', FORMAT_STRING="DOLLAR12.2" 
SET [&&COL&] as 'CROSSJOIN({[&1Geographic]},{[Measures].[&1Sum of predict]} )' 
SET [&&ROW&] as 
'ORDER(TopCount({CROSSJOIN({[&1Time]} ,{[&1Productline]} )}, 2, 
([GEOGRAPHIC].[All GEOGRAPHIC].[CANADA], [Measures].[PREDICT_SUM])),[GEOGRAPHIC].[All GEOGRAPHIC].[CANADA], BASC)' 
SELECT {[&&COL&]} ON COLUMNS , {[&&ROW&]} ON ROWS FROM PRDMDDB

Sample Code to use:

int val = 2;

Integer vall = new Integer(val);

Object value = (Object) vall;

List byRank = 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.ExpressionInterfacestringExpr = null;

try

{    stringExpr  = com.sas.iquery.metadata.expr.StringExpressionUtil.getInstance().newExpression(Map,

"[GEOGRAPHIC].[All GEOGRAPHIC].[CANADA]",

 type, scope);

}

byRank.add(stringExpr );   

byRank.add(stringExpr );  <<== where measureDataItem is [Measures].[Sum of predict]

BusinessQueryRankFilter step = new BusinessQueryRankFilter(BusinessQueryRankFilter.TOP, value, byRank , BusinessQueryRankFilter.COUNT);

dataSelection.addStep(step);  <<where the dataSelection is the query that contains the cross join of the Year and Product Type dimension.