Business Query Sorting Use Cases.

Example 1: Sort Measure within hierarchy

MDX Generated:

> SELECT {[Measures].[ACTUAL_AVG]} ON COLUMNS,

> ORDER (

> CROSSJOIN({[TIME].[YEAR].MEMBERS},

> {[GEOGRAPHIC].[COUNTRY].MEMBERS}),

> [Measures].[ACTUAL_AVG], DESC) ON ROWS

> FROM PRDMDDB

Sample Code to use:

> List sortCriteria = new ArrayList();

> String[] measures = { measure };<<== where name is [ACTUAL_AVG]

> dataItem.setSortDirection(DataItemActionType.SORT_HIERARCHY_DESCENDING); <<=== where dataitem is YEAR

> businessQuery.setQueryProperty(BusinessQueryProperty.BUSINESS_QUERY_OLAP_SORT_PRECEDENCE, false);

> sortCriteria.put(measures);

> businessQuery.setSortCriteria(sortCriteria,com.sas.iquery.metadata.business.Role.ROW)

> businessQuery.setSortDirection(BusinessQueryActionType.SORT_HIERARCHY_DESCENDING, com.sas.iquery.metadata.business.Role.ROW);

Example 2: Sort Measure within hierarchy and sort YEAR label value.

MDX Generated:

> select generate ( ORDER([TIME].[YEAR].MEMBERS, [TIME].CURRENTMEMBER.NAME, bdesc),

> CrossJoin ( {[TIME].CURRENTMEMBER },

> order( [GEOGRAPHIC].[COUNTRY].MEMBERS, [Measures].[ACTUAL_SUM],BDESC) ) ) on rows,

> {[Measures].[ACTUAL_SUM]} on columns from PRDMDDB

Sample Code to use:

> List sortCriteria = new ArrayList();

> dataItem.setSortDirection(DataItemActionType.SORT_DESCENDING); <<-- where dataitem is TIME.

> String[] measures = { measure };<<== where name is [ACTUAL_AVG]

> sortCriteria.put(measures);

> businessQuery.setSortCriteria(sortCriteria,com.sas.iquery.metadata.business.Role.ROW)

> businessQuery.setSortDirection(BusinessQueryActionType.SORT_HIERARCHY_DESCENDING, com.sas.iquery.metadata.business.Role.ROW);

Example 3: Sort Measures across hierarchy (UI may not be present in V9.1)

MDX Generated:

> SELECT {[Measures].[ACTUAL_AVG]} ON COLUMNS,

> ORDER ( CROSSJOIN({[TIME].[YEAR].MEMBERS},{[GEOGRAPHIC].[COUNTRY].MEMBERS}),

> [Measures].[ACTUAL_AVG], BDESC) ON ROWS FROM PRDMDDB

Sample Code to use:

> List sortCriteria = new ArrayList();

> String[] measures = { measure };<<== where name is [ACTUAL_AVG]

> sortCriteria.put(measures);

> businessQuery.setSortCriteria(sortCriteria,com.sas.iquery.metadata.business.Role.ROW)

> businessQuery.setSortDirection(BusinessQueryActionType.SORT_DESCENDING, com.sas.iquery.metadata.business.Role.ROW);

Example 4: Sort Multiple Measure (by sum) within hierarchy (UI will not be present in V9.1)

MDX Generated:

> SELECT {[Measures].[ACTUAL_AVG], [Measures].[ACTUAL_MAX]} ON COLUMNS,

> ORDER(

> CROSSJOIN({[TIME].[YEAR].MEMBERS},

> {[GEOGRAPHIC].[COUNTRY].MEMBERS}),

> [Measures].[ACTUAL_AVG]+[Measures].[ACTUAL_MAX], DESC)

> ON ROWS FROM PRDMDDB

Sample Code to use:

> List sortCriteria = new ArrayList();

> String[] measures = { measure1, measure2 }; <<== measure1 is ACTUAL_AVG, measure2 is ACTUAL_MAX

> sortCriteria.put(measures);

> businessQuery.setSortCriteria(sortCriteria,com.sas.iquery.metadata.business.Role.ROW)

> businessQuery.setSortDirection(BusinessQueryActionType.SORT_HIERARCHY_DESCENDING, com.sas.iquery.metadata.business.Role.ROW);

Example 5: Sort by a particular measure ( ACTUAL_AVG) for a particular member value 1993

MDX Generated:

> SELECT CROSSJOIN([TIME].[YEAR].Members, [Measures].[ACTUAL_AVG]) ON COLUMNS,

> ORDER (

> CROSSJOIN([PRODUCTLINE].[PRODUCT].MEMBERS, [GEOGRAPHIC].[COUNTRY].MEMBERS), ([Measures].[ACTUAL_AVG], [TIME].[All Time].[1993]), DESC) ON ROWS

> FROM PRDMDDB

Sample Code to use:

> List sortCriteria = new ArrayList();

> sortCriteria.put(measures); << where name is [ACTUAL_AVG]

> sortCriteria.put(member); << where name is [Time].[All Time].[1993]

> businessQuery.setSortCriteria(sortCriteria,com.sas.iquery.metadata.business.Role.ROW)

> businessQuery.setSortDirection(BusinessQueryActionType.SORT_HIERARCHY_DESCENDING, com.sas.iquery.metadata.business.Role.ROW);