How to create band report based on Cognos DMR

Check list of all posts

Context
There are 4 different ways to handle bands:
1. Create band in database
2. Create band in Cognos FM model, or tableau model
3. Create band in relational based model
4. Create band in DMR based.

While it is relative simple to create band in relational based model (s. sample below), the DMR based band is difficult to build and has poor performance.

BAND
CASE
WHEN [Sales (query)].[Sales].[Revenue]  < 1000  THEN '<1000'
WHEN [Sales (query)].[Sales].[Revenue]  BETWEEN 1000 AND 2000 THEN '1000 - 2000'
ELSE 'OTHER'
END

Sample to create band report based on Cognos DMR

BAND
set([BAND1],[BAND2],[BAND3])

BAND1
member (
total ( currentMeasure within set filter ( [Sales (analysis)].[Order].[Order].[Order detail code], tuple(currentMember([Sales (analysis)].[Order].[Order]),[Revenue]) < 50000) )
, '< 50000'
, '< 50000'
, [Sales (analysis)].[Order].[Order])

BAND2
member (
total ( currentMeasure within set filter ( [Sales (analysis)].[Order].[Order].[Order detail code], tuple(currentMember([Sales (analysis)].[Order].[Order]),[Revenue]) between  50000 and 100000) )
, '50000 -100000'
, '50000 -100000'
 , [Sales (analysis)].[Order].[Order])

BAND3 
 member (
total ( currentMeasure within set filter ( [Sales (analysis)].[Order].[Order].[Order detail code], tuple(currentMember([Sales (analysis)].[Order].[Order]),[Revenue]) > 100000) )
, '> 100000'
, '> 100000'
, [Sales (analysis)].[Order].[Order])

Performance problem with  to band report based on Cognos DMR

Relational based: All process is done on database side

with Sales as (

select ORDER_DETAILS.QUANTITY*ORDER_DETAILS.UNIT_SALE_PRICE Revenue

 from GOSALES.ORDER_DETAILS ORDER_DETAILS, GOSALES.ORDER_HEADER ORDER_HEADER

 where ORDER_HEADER.ORDER_NUMBER=ORDER_DETAILS.ORDER_NUMBER) 

select 

case  when Sales.Revenue<1000 then '<1000' when Sales.Revenue between 1000 and 2000 then '1000 - 2000' else 'OTHER' end  Band

, sum(Sales.Revenue) Revenue

 from Sales

 group by case  when Sales.Revenue<1000 then '<1000' when Sales.Revenue between 1000 and 2000 then '1000 - 2000' else 'OTHER' end

DMR based: All process is done on report server. Therefore, we need to be very cautious about the data amount to be handled.

with TIME_DIMENSION3 as (
select TIME_DIMENSION.CURRENT_YEAR CURRENT_YEAR, TIME_DIMENSION.MONTH_KEY MONTH_KEY
 from GOSALES.TIME_DIMENSION TIME_DIMENSION
 group by TIME_DIMENSION.CURRENT_YEAR, TIME_DIMENSION.MONTH_KEY) 

select distinct CAST( TIME_DIMENSION3.CURRENT_YEAR AS CHAR( 4 ) ) Yearkey
 from TIME_DIMENSION3
 where CAST( TIME_DIMENSION3.CURRENT_YEAR AS CHAR( 4 ) )='2006' and mod(TIME_DIMENSION3.MONTH_KEY, 100)<>0
 order by Yearkey asc nulls last
with Sales as (
select (EXTRACT( YEAR FROM (ORDER_HEADER.ORDER_DATE) )*10000+EXTRACT( MONTH FROM (ORDER_HEADER.ORDER_DATE) )*100)+EXTRACT( DAY FROM (ORDER_HEADER.ORDER_DATE) ) Day_key__order_date_, ORDER_HEADER.ORDER_NUMBER Order_number, ORDER_DETAILS.ORDER_DETAIL_CODE Order_detail_code, ORDER_DETAILS.QUANTITY*ORDER_DETAILS.UNIT_SALE_PRICE Revenue
 from GOSALES.ORDER_DETAILS ORDER_DETAILS, GOSALES.ORDER_HEADER ORDER_HEADER
 where ORDER_HEADER.ORDER_NUMBER=ORDER_DETAILS.ORDER_NUMBER)
 , TIME_DIMENSION8 as (
select TIME_DIMENSION.DAY_KEY DAY_KEY, TIME_DIMENSION.MONTH_KEY MONTH_KEY, TIME_DIMENSION.CURRENT_YEAR CURRENT_YEAR
 from GOSALES.TIME_DIMENSION TIME_DIMENSION
 where CAST( TIME_DIMENSION.CURRENT_YEAR AS CHAR( 4 ) )='2006')
 , Sales9 as (
select (EXTRACT( YEAR FROM (ORDER_HEADER.ORDER_DATE) )*10000+EXTRACT( MONTH FROM (ORDER_HEADER.ORDER_DATE) )*100)+EXTRACT( DAY FROM (ORDER_HEADER.ORDER_DATE) ) Day_key__order_date_, ORDER_HEADER.ORDER_NUMBER Order_number, ORDER_DETAILS.ORDER_DETAIL_CODE Order_detail_code, ORDER_DETAILS.QUANTITY*ORDER_DETAILS.UNIT_SALE_PRICE Revenue
 from GOSALES.ORDER_DETAILS ORDER_DETAILS, GOSALES.ORDER_HEADER ORDER_HEADER
 where ORDER_HEADER.ORDER_NUMBER=ORDER_DETAILS.ORDER_NUMBER) 

select
CAST( Order_.Order_number AS CHAR( 6 ) ) Order_numberkey
, CAST( Order_.Order_detail_code AS VARCHAR( 8 ) ) Order_detail_codekey
, CAST( TIME_DIMENSION8.CURRENT_YEAR AS CHAR( 4 ) ) Yearkey
, sum(Sales9.Revenue) Revenue
from Sales Order_, TIME_DIMENSION8, Sales9
where mod(TIME_DIMENSION8.MONTH_KEY, 100)<>0
and Sales9.Order_detail_code=Order_.Order_detail_code
and Sales9.Day_key__order_date_=TIME_DIMENSION8.DAY_KEY
group by CAST( Order_.Order_number AS CHAR( 6 ) )
, CAST( Order_.Order_detail_code AS VARCHAR( 8 ) )
, CAST( TIME_DIMENSION8.CURRENT_YEAR AS CHAR( 4 ) )
order by Yearkey asc nulls last