Custom Group Banding Points Method

Custom Group Banding Point 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 Points Method helps optimize custom group banding when using the Points 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" using the point 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.

create table ZZMD00 (CUSTOMER_ID SHORT, WJXBFS1 DOUBLE)

insert into ZZMD00 

select a11.CUSTOMER_ID AS CUSTOMER_ID, a11.TOT_DOLLAR_SALES

 as WJXBFS1

from CUSTOMER_SLS a11

create table ZZMD01 (WJXBFS1 DOUBLE)

insert into ZZMD01 

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 ZZMD00 pa1, ZZMD01 pa2

create table ZZMQ02 (CUSTOMER_ID SHORT, DA57 LONG)

[Placeholder for an analytical SQL]

insert into ZZMQ02 values (DummyInsertValue)

Treat banding as normal calculation (default)

select sum(a11.TOT_DOLLAR_SALES) as WJXBFS1

from CUSTOMER_SLS a11, ZZMQ02 a12

where a11.CUSTOMER_ID = a12.CUSTOMER_ID

select a12.DA57 AS DA57, sum(a11.TOT_DOLLAR_SALES) 

 as WJXBFS1

from CUSTOMER_SLS a11, ZZMQ02 a12

where a11.CUSTOMER_ID = a12.CUSTOMER_ID

group by a12.DA57

drop table ZZMD00

drop table ZZMD01

drop table ZZMQ02

Use standard case statement syntax

create table ZZOP03 (CUSTOMER_ID SHORT, DA57 LONG)

insert into ZZOP03 

select pa3.CUSTOMER_ID AS CUSTOMER_ID,

(case 

 when (pa3.WJXBFS1 >= 1 and pa3.WJXBFS1 < 2) then 1

 when (pa3.WJXBFS1 >= 2 and pa3.WJXBFS1 <= 3)then 2

end) as DA57

from ZZMQ02 pa3

select sum(a11.TOT_DOLLAR_SALES) as WJXBFS1

from CUSTOMER_SLS a11, ZZOP03 a12

where a11.CUSTOMER_ID = a12.CUSTOMER_ID

select a12.DA57 AS DA57, sum(a11.TOT_DOLLAR_SALES) 

 as WJXBFS1

from CUSTOMER_SLS a11, ZZOP03 a12

where a11.CUSTOMER_ID = a12.CUSTOMER_ID

group by a12.DA57

drop table ZZMD00

drop table ZZMD01

drop table ZZMQ02

drop table ZZOP03

Insert band range to database and join with metric value

create table ZZOP03 (BandNo LONG, BandStart DOUBLE, 

 BandEnd DOUBLE)

insert into ZZOP03 values (1, 1, 2)

[Insertions for other bands]

create table ZZOP04 (CUSTOMER_ID SHORT, DA57 LONG)

insert into ZZOP04 

select pa3.CUSTOMER_ID AS CUSTOMER_ID, pa4.BandNo as DA57

from ZZMQ02 pa3, ZZOP03 pa4

where ((pa3.WJXBFS1 >= pa4.BandStart

 and pa3.WJXBFS1 < pa4.BandEnd)

 or (pa3.WJXBFS1 = pa4.BandEnd

 and pa4.BandNo = 2))

select sum(a11.TOT_DOLLAR_SALES) as WJXBFS1

from CUSTOMER_SLS a11, ZZOP04 a12

where a11.CUSTOMER_ID = a12.CUSTOMER_ID

select a12.DA57 AS DA57, sum(a11.TOT_DOLLAR_SALES) 

 as WJXBFS1

from CUSTOMER_SLS a11, ZZOP04 a12

where a11.CUSTOMER_ID = a12.CUSTOMER_ID

group by a12.DA57

drop table ZZMD00

drop table ZZMD01

drop table ZZMQ02

drop table ZZOP03

drop table ZZOP04