Below is code I'm trying to get working. How do I get BUDGET_US_AMT from join J3 availble to the final report. I would like it to the right of Ytd_Var_%. FYI - All Tables are DB2 (except for the hold I'm trying to use!).

Since I'm asking for coding help, I also would like to put some nice formatting on the ITM_EXT_PRC_L_AMT by year columns, but then it hoses up my using them in the ytd_var_% calc, can I format the columns ($ and ,) then EDIT them out in the compute statement for ytd_var_%?

-OLAP OFF
-* Subtract 1 yr
-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';
JOIN
RPTN_FCT_MO_BUDGET.RPTN_FCT_MO_BUDGET.PERIOD_KEY IN RPTN_FCT_MO_BUDGET TO ALL
RPTN_DIM_PERIOD.RPTN_DIM_PERIOD.PERIOD_KEY IN RPTN_DIM_PERIOD AS J3
END
TABLE FILE RPTN_FCT_MO_BUDGET
SUM
BUDGET_US_AMT
WHERE ((INV_DT GE &CUR_BEG) AND (INV_DT LE &YYMD));
ON TABLE NOTOTAL
ON TABLE HOLD AS HOLD_BUD FORMAT FOCUS INDEX YR_NBR;
ON TABLE SET STYLE *
UNITS=IN,
PAGESIZE='Letter',
LEFTMARGIN=0.250000,
RIGHTMARGIN=0.250000,
TOPMARGIN=0.250000,
BOTTOMMARGIN=0.250000,
SQUEEZE=ON,
ORIENTATION=PORTRAIT,
$
TYPE=REPORT,
GRID=OFF,
FONT='TIMES NEW ROMAN',
SIZE=10,
COLOR='BLACK',
BACKCOLOR='NONE',
STYLE=NORMAL,
$
ENDSTYLE
END
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 ALL
RPTN_DIM_FOB.RPTN_DIM_FOB.FOB_KEY IN RPTN_DIM_FOB AS J1
END
TABLE FILE RPTN_DIM_PERIOD
-*AS 'YTD Variance'
-*ON TABLE SUMMARIZE
SUM
ITM_EXT_PRC_L_AMT AS ' '
BY
FOB_CORP_NM AS ' '
ACROSS
YR_NBR AS 'YTD Totals'
COMPUTE
Ytd_Var_%/D6.2% = ( ( C2 - C1 ) / C1 ) * 100;
HEADING
"Company Total Net Sales Report &DATE Sales Data Warehouse "
" "
" Drill to Sub-Reports via link below"
" "
WHERE TRANS_LVL_CD EQ 1
AND (((INV_DT GE &LST_BEG) AND (INV_DT LE &LST_YTD)) OR
((INV_DT GE &CUR_BEG) AND (INV_DT LE &YYMD)));
ON TABLE SET PAGE-NUM OFF
ON TABLE NOTOTAL
ON TABLE SET ONLINE-FMT HTML
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *
UNITS=IN,
PAGESIZE='SCREEN',
LEFTMARGIN=0.000000,
RIGHTMARGIN=0.000000,
TOPMARGIN=0.000000,
BOTTOMMARGIN=0.000000,
SQUEEZE=ON,
ORIENTATION=PORTRAIT,
$
TYPE=REPORT,
GRID=OFF,
FONT='TIMES NEW ROMAN',
SIZE=10,
COLOR='BLACK',
BACKCOLOR='NONE',
STYLE=NORMAL,
$
TYPE=HEADING,
LINE=1,
JUSTIFY=CENTER,
$
TYPE=HEADING,
LINE=2,
JUSTIFY=CENTER,
$
TYPE=HEADING,
LINE=3,
JUSTIFY=CENTER,
$
TYPE=ACROSSVALUE,
ACROSS=1,
JUSTIFY=RIGHT,
$
TYPE=ACROSSTITLE,
ACROSS=1,
JUSTIFY=LEFT,
$
ENDSTYLE
END

Looking at the code the first request should include BY FOB_CORP_NM and the index on the hold statement should be INDEX FOB_CORP_NM. (assuming that column is in table RPTN_FCT_MO_BUDGET.)

Then obviously you need to issue a join to the hold file.

From a coding point of view you need a dummy multiverb request. change your code as follows

TABLE FILE RPTN_DIM_PERIOD
SUM BUDGET_US_AMT NOPRINT
BY FOB_CORP_NM
SUM ITM_EXT_PRC_L_AMT AS ' '
BY FOB_CORP_NM AS ' '
ACROSS YR_NBR AS 'YTD Totals'
COMPUTE Ytd_Var_%/D6.2% = ( ( C3 - C2 ) / C2 ) * 100;
COMPUTE BUDGET/D12.2=C1;

Notice the column numbers in your first compute have to be increased by 1.

From an efficiency point of view don�t join your hold file directly to DB2. Do an extract from the DB2 for the second request and join the two hold files.

From a formatting point of view what is happening is that if you code SUM ITM_EXT_PRC_L_AMT/D12.M AS ' ' it introduces an additional column so in the revised code above
COMPUTE Ytd_Var_%/D6.2% = ( ( C3 - C2 ) / C2 ) * 100;

should be coded as

COMPUTE Ytd_Var_%/D6.2% = ( ( C4 - C2 ) / C2 ) * 100;

The important thing to remember is that NOPRINT columns are always counted when using C notation and that applying formatting in the body of the report is equivalent to coding field NOPRINT newfield. Also all fields used in a COMPUTE are counted based on their order of appearance in the compute statement, if a field is used twice in the compute you only count it the first time

JG - Thanks, I got it working. Your post (and this message board in general) is fantastic! I am a newbie to this tool (actually any front end tool), and the examples in WF provided docs are never real world enough to reverse engineer to work in practice (that is until you get used to the language / flow of tool).

Again ... thanks. Many more posts/questions a coming,
Frankie