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 PRDMDDBSample 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.