I have the below code executing fine, but the performance is unacceptable. I've been told the main reason for the poor performance is due to the fact I am grouping by a defined field (CBLYNN_A7).

How do I(can I?) group by the indicator (IS_CB_LYNN_IND) and still get row labels to be CB Lynn or Catalog
correctly?

Thanks,
Frankie

-SET &LST_YTD=DATECVT((DATEADD((DATECVT(&YYMD,'I8YYMD','YYMD')),'Y',-1)),'YYMD', 'I8YYMD');
-* Change month/day to Jan 1
-SET &CUR_BEG = EDIT(&DATEYY,'9999') | '0101';
-SET &LST_BEG = EDIT(&LST_YTD,'9999') | '0101';
-SET &CUR_EOM=DATECVT(DATEMOV((DATEADD(DATECVT(&YYMD,'I8YYMD','YYMD'),'M',-1)),EOM),'YYMD', 'I8YYMD');
-SET &LST_EOM=DATECVT(DATEMOV((DATEADD(DATECVT(&YYMD,'I8YYMD','YYMD'),'M',-13)),EOM),'YYMD', 'I8YYMD');
JOIN
RPTN_DIM_PERIOD.RPTN_DIM_PERIOD.PERIOD_KEY IN RPTN_DIM_PERIOD TO ALL
RPTN_FCT_DLY_ITM_SLS.RPTN_FCT_DLY_ITM_SLS.PERIOD_KEY IN RPTN_FCT_DLY_ITM_SLS AS J0
END
JOIN
RPTN_FCT_DLY_ITM_SLS.RPTN_FCT_DLY_ITM_SLS.FOB_KEY IN RPTN_DIM_PERIOD
TO RPTN_DIM_FOB.RPTN_DIM_FOB.FOB_KEY IN RPTN_DIM_FOB AS J1
END
DEFINE FILE RPTN_DIM_PERIOD ADD
CBLYNN_A7/A7=IF IS_CB_LYNN_IND EQ 'Y' THEN 'CB Lynn' ELSE 'Catalog';
YTD_2005/D12.2=IF YR_NBR EQ 2005 THEN ITM_EXT_PRC_US_AMT ELSE 0;
END
TABLE FILE RPTN_DIM_PERIOD
-*AS 'YTD Variance'
SUM
YTD_2005 NOPRINT
SUM
ITM_EXT_PRC_US_AMT/D13.2M AS ' '
BY
FOB_BUS_UNIT_NM AS 'Family, of Business'
BY
CBLYNN_A7 AS 'Product, Group '
ACROSS
YR_NBR AS 'Net Sls'
COMPUTE
Var_%/D6.2% = ( ( C5 - C2 ) / C2 ) * 100;
2005_%_Ttl/D6.2% = ( C4 / YTD_2005 ) * 100;
HEADING
"Product Sales Report"
WHERE ((( INV_DT GE &LST_BEG ) AND ( INV_DT LE &LST_EOM )) OR (( INV_DT GE &CUR_BEG ) AND ( INV_DT LE &CUR_EOM )))
AND ( FOB_BUS_UNIT_NM EQ '&Business_Unit.(Cronatron,Drummond,Kent,Lawson De Mexico,Lawson Products).Business Unit.' );

I have some questions.

Is IS_CB_LYNN_IND is 'Y' or 'N'?
Why are you doing this?

...<br />SUM<br />YTD_2005 NOPRINT<br />SUM<br />...

How much data is being return by this report?
Have you tried moving DEFINE to a COMPUTE?

I'm assuming these tables are relational databases. In order to optimize requests to an RDBMS you need to ensure that the join, sort, aggregation and selection criteria get passed. Mutli-verb requests and sorting on defined fields typically disable optimization. Since the sort on CBLYNN_A7 is probably failing, all the aggregation is probably failing too. Which means your request is pulling back all the details rows and asking FOCUS to sort and aggrgate. I typically try to write an optimized RDBMS pull, hold it, then apply the defines and heavy formatting to the HOLD file. Something like this:

SUM ITM_EXT_PRC_US_AMT
BY FOB_BUS_UNIT_NM
BY IS_CB_LYNN_IND
BY YR_NBR
WHERE ((( INV_DT GE &LST_BEG ) AND ( INV_DT LE &LST_EOM )) OR (( INV_DT GE &CUR_BEG ) AND ( INV_DT LE &CUR_EOM )))
AND ( FOB_BUS_UNIT_NM EQ '&Business_Unit.(Cronatron,Drummond,Kent,Lawson De Mexico,Lawson Products).Business Unit.' );
ON TABLE HOLD AS HOUT1 FORMAT ALPHA
END

DEFINE FILE HOUT1
CBLYNN_A7/A7=IF IS_CB_LYNN_IND EQ 'Y' THEN 'CB Lynn' ELSE 'Catalog';
END

TABLE FILE RPTN_DIM_PERIOD
SUM
ITM_EXT_PRC_US_AMT/D13.2M AS ' '
BY FOB_BUS_UNIT_NM AS 'Family, of Business'
BY CBLYNN_A7 AS 'Product, Group '
ACROSS
YR_NBR AS 'Net Sls'
COMPUTE
Var_%/D6.2% = ( ( C5 - C2 ) / C2 ) * 100;
2005_%_Ttl/D6.2% = ( C4 / YTD_2005 ) * 100;
HEADING
"Product Sales Report"
END

I didn't include the YTD_2005 DEFINE because you might be able to accomplish that with row-total or column-total -- not sure. If not, add those elements back in as well.

Is IS_CB_LYNN_IND is 'Y' or 'N'?
YES
===============
How much data is being return by this report?
2 rows / 6 columns (very little returned, however the fact table is ~ 50mil)
===============
Have you tried moving DEFINE to a COMPUTE?
Not exactly sure how to code/what you mean, something like?:
COMPUTE catalog/D12.2 = IF IS_CB_LYNN_IND = 'N' THEN AMT ELSE 0; AS 'Catalog'
COMPUTE cblynn/D12.2 = IF IS_CB_LYNN_IND = 'Y' THEN AMT ELSE 0; AS 'CB Lynn'
===============
Why are you doing this?

code:
-------------------------------
...
SUM YTD_2005 NOPRINT
SUM
...

-------------------------------

I was having a problem with my compute statement after the across verb, it wasn't recognizing YTD_2005 until I did this.

================================

Bare with me, new to language, trying to develop with dev studio etc.

codermonkey,
thanks for the post. I will give your example a try and keep you posted. I didn't use row-total/column-total because I only wanted that calc on the current year (2005), when I tried using the other method, I would get it (pct of ttl) for all years.

frankie,
Since your final dataset is very small I would try to aggregate your data as much as you can before doing a DEFINE. This what codermonkey did in code that was posted.