MicroStrategy ONE
Custom Group Banding Count Method
Custom Group Banding Count 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 Count Method helps optimize custom group banding when using the Count Banding method. You have the following options:
- Treat banding as normal calculation (default): Select this option to allow the MicroStrategy Analytical Engine to perform the custom group banding.
- Use standard case statement syntax: Select this option to utilize case statements within your database to perform the custom group banding.
- Insert band range to database and join with metric value: Select this option to use temporary tables to perform the custom group banding.
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", which uses the Count 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 setting 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 < 100.9)
then 1
when (pa3.WJXBFS1 >= 100.9 and pa3.WJXBFS1 200.8)
then 2
when (pa3.WJXBFS1 >= 200.8 and pa3.WJXBFS1 300.7)
then 3
when (pa3.WJXBFS1 >= 300.7 and pa3.WJXBFS1 400.6)
then 4
when (pa3.WJXBFS1 >= 400.6 and pa3.WJXBFS1 500.5)
then 5
when (pa3.WJXBFS1 >= 500.5 and pa3.WJXBFS1 600.4)
then 6
when (pa3.WJXBFS1 >= 600.4 and pa3.WJXBFS1 700.3)
then 7
when (pa3.WJXBFS1 >= 700.3 and pa3.WJXBFS1 800.2)
then 8
when (pa3.WJXBFS1 >= 800.2 and pa3.WJXBFS1 900.1)
then 9
when (pa3.WJXBFS1 >= 900.1 and pa3.WJXBFS1 <= 1000
then 10
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, 100.9)
[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 = 10))
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