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