Custom Group Banding Size Method

Custom Group Banding Size Method is an advanced property that is hidden by default. For information on how to display this property, see Viewing and Changing Advanced VLDB Properties.

The Custom Group Banding Size Method helps optimize custom group banding when using the Size Banding method. You can choose to use the standard method that uses the Analytical Engine or database-specific syntax, or you can choose to use case statements or temp tables.

Levels at Which You Can Set This

Database instance, report, and template

Examples

The following SQL examples were created in MicroStrategy Tutorial. The report contains a Custom Group "Customer Value Banding" that uses the size method and the Revenue metric. The SQL for each of the three settings for this property is presented below. All three options start with the same SQL passes. In this example, the first six passes are the same. The remaining SQL passes differ depending on the Custom Group Banding Count Method selected.

Copy

create table ZZMD000 (CUSTOMER_ID SHORT, WJXBFS1 DOUBLE)
insert into ZZMD000 
select a11.CUSTOMER_ID AS CUSTOMER_ID, a11.TOT_DOLLAR_SALES 
 as WJXBFS1
from CUSTOMER_SLS a11
create table ZZMD001 (WJXBFS1 DOUBLE)
insert into ZZMD001 
select sum(a11.TOT_DOLLAR_SALES) as WJXBFS1
from YR_CATEGORY_SLS a11
select pa1.CUSTOMER_ID AS CUSTOMER_ID,
 (pa1.WJXBFS1 / pa2.WJXBFS1) as WJXBFS1
from ZZMD000 pa1, ZZMD001 pa2
create table ZZMQ002 (CUSTOMER_ID SHORT, WJXBFS1 DOUBLE)
 [Placeholder for an Analytical SQL]
insert into ZZMQ02 values (DummyInsertValue)

Treat banding as normal calculation

Copy

select sum(a11.TOT_DOLLAR_SALES) as WJXBFS1
from CUSTOMER_SLS a11, ZZMQ002 a12
where a11.CUSTOMER_ID = a12.CUSTOMER_ID
select a12.DA57 AS DA57, sum(a11.TOT_DOLLAR_SALES) 
 as WJXBFS1
from CUSTOMER_SLS a11, ZZMQ002 a12
where a11.CUSTOMER_ID = a12.CUSTOMER_ID
group by a12.DA57
drop table ZZMD000
drop table ZZMD001
drop table ZZMQ002

Use standard CASE statement syntax

Copy

create table ZZOP003 (CUSTOMER_ID SHORT, DA57 LONG)
insert into ZZOP003 
select pa3.CUSTOMER_ID AS CUSTOMER_ID,
 (case 
 when (pa3.WJXBFS1 >= 0 and pa3.WJXBFS1 < .2) then 1
 when (pa3.WJXBFS1 >= .2 and pa3.WJXBFS1 < .4)then 2
 when (pa3.WJXBFS1 >= .4 and pa3.WJXBFS1 < .6)then 3
 when (pa3.WJXBFS1 >= .6 and pa3.WJXBFS1 < .8)then 4
 when (pa3.WJXBFS1 >= .8 and pa3.WJXBFS1 <= 1)then 5
 end) as DA57
from ZZMQ002 pa3
drop table ZZMD000
drop table ZZMD001
drop table ZZMQ002
drop table ZZOP003

Insert band range to database and join with metric value

Copy

create table ZZOP003 (BandNo LONG, BandStart DOUBLE, 
 BandEnd DOUBLE)
insert into ZZOP003 values (1, 0, .2)
[Insertions for other bands]
create table ZZOP004 (
 CUSTOMER_ID SHORT, 
 DA57 LONG)
insert into ZZOP004 
select pa3.CUSTOMER_ID AS CUSTOMER_ID, pa4.BandNo as DA57
from ZZMQ002 pa3, ZZOP003 pa4
where ((pa3.WJXBFS1 >= pa4.BandStart
 and pa3.WJXBFS1 < pa4.BandEnd)
 or (pa3.WJXBFS1 = pa4.BandEnd
 and pa4.BandNo = 5))
select sum(a11.TOT_DOLLAR_SALES) as WJXBFS1
from CUSTOMER_SLS a11, ZZOP004 a12
where a11.CUSTOMER_ID = a12.CUSTOMER_ID
select a12.DA57 AS DA57,sum(a11.TOT_DOLLAR_SALES) as WJXBFS1
from CUSTOMER_SLS a11, ZZOP004 a12
where a11.CUSTOMER_ID = a12.CUSTOMER_ID
group by a12.DA57
drop table ZZMD000
drop table ZZMD001
drop table ZZMQ002
drop table ZZOP003
drop table ZZOP004