I have data that’s showing the total, budget and variance by region.
I’m using date as my across.
Now for example if there’s no data for the month of Nov. it shows a dot but there’s data in Dec.
Now for the budget even if there’s no data I still want it to show 5000. But because I’m working with across I’m not sure what will help me to show that.

Here’s my code:
-SET &ECHO = ON;
SET EMPTYREPORT = ON
SET PAGE-NUM = OFF
SET ASNAMES = ON

-SET &RPTNAME = 'EXPBMORP';

-DEFAULT &BUDGET = '5000';
-DEFAULT &JBUDGET = '4000';



JOIN PAYEEID IN EXPENSEBASE TO SYSTEMUSERID IN CRMSYSTEMUSER AS J1
-RUN

TABLE FILE EXPENSEBASE
PRINT
PAYEEID
FULLNAME
POSITION
COMPUTE EXPDATE_MMYY/MtYY = EXPENSEDATE;
COSTCENTER
COMPUTE TOTAL/D15.2M = TOTALAMOUNT;
COMPUTE BUDGET/D15.2M = &BUDGET;
COMPUTE VARIANCE/D15.2M = BUDGET - TOTAL;
EXPENSEREGION
EXPENSETYPE
BY EXPENSEID
WHERE EXPENSEREGION NE 'GGOF';
WHERE EXPENSESTATUS EQ 'SUBMITTED';
WHERE EXPENSETYPE EQ 'NON_CONTACT';
WHERE COSTCENTER NE 'F7263' OR COSTCENTER IS MISSING;
WHERE PAYEEID NE '{8E0B13A0-35AA-DB11-9657-001438BFF493}' OR PAYEEID EQ '{D754F242-FE97-DA11-ABEB-001438BFF493}' OR
PAYEEID EQ '{AA3A710F-6322-DB11-8082-001438BFF493}' OR PAYEEID EQ '{60EC0DB1-9C46-DC11-9630-001438BFF493}' OR
PAYEEID EQ '{651BD212-4997-DA11-ABEB-001438BFF493}';
ON TABLE HOLD AS NONCONTACT
END
-RUN

TABLE FILE NONCONTACT
SUM
TOTAL
BY EXPENSEREGION
BY EXPDATE_MMYY
ON TABLE HOLD AS TOTNONCONTACT
END
-RUN

TABLE FILE EXPENSEBASE
PRINT
PAYEEID
FULLNAME
POSITION
COMPUTE EXPDATE_MMYY/MtYY = EXPENSEDATE;
COSTCENTER
COMPUTE TOTAL/D15.2M = TOTALAMOUNT;
COMPUTE BUDGET/D15.2M = &BUDGET;
COMPUTE VARIANCE/D15.2M = BUDGET - TOTAL;
EXPENSEREGION
EXPENSETYPE
BY EXPENSEID
WHERE COSTCENTER NE 'F7263' OR COSTCENTER IS MISSING;
WHERE POSITION IN ('RSM', 'ISR', 'SC');
WHERE EXPENSEREGION NE 'GGOF';
WHERE EXPENSETYPE EQ 'CONTACT';
WHERE EXPENSESTATUS EQ 'SUBMITTED';
WHERE PAYEEID NE '{8E0B13A0-35AA-DB11-9657-001438BFF493}' OR PAYEEID EQ '{D754F242-FE97-DA11-ABEB-001438BFF493}' OR
PAYEEID EQ '{AA3A710F-6322-DB11-8082-001438BFF493}' OR PAYEEID EQ '{60EC0DB1-9C46-DC11-9630-001438BFF493}' OR
PAYEEID EQ '{651BD212-4997-DA11-ABEB-001438BFF493}';
ON TABLE HOLD AS CONTACT
END
-RUN

TABLE FILE CONTACT
SUM
TOTAL
BY EXPENSEREGION
BY EXPDATE_MMYY
ON TABLE HOLD AS TOTCONTACT
END
-RUN

TABLE FILE EXPENSEBASE
PRINT
PAYEEID
FULLNAME
POSITION
COMPUTE EXPDATE_MMYY/MtYY = EXPENSEDATE;
COSTCENTER
COMPUTE TOTAL/D15.2M = TOTALAMOUNT;
COMPUTE BUDGET/D15.2M = &BUDGET;
COMPUTE VARIANCE/D15.2M = BUDGET - TOTAL;
EXPENSEREGION
EXPENSETYPE
BY EXPENSEID
WHERE POSITION EQ 'RSM';
WHERE EXPENSESTATUS EQ 'OPEN';
WHERE COSTCENTER NE 'F7263';
WHERE PAYEEID NE '{8E0B13A0-35AA-DB11-9657-001438BFF493}' OR PAYEEID EQ '{D754F242-FE97-DA11-ABEB-001438BFF493}' OR
PAYEEID EQ '{AA3A710F-6322-DB11-8082-001438BFF493}' OR PAYEEID EQ '{60EC0DB1-9C46-DC11-9630-001438BFF493}' OR
PAYEEID EQ '{651BD212-4997-DA11-ABEB-001438BFF493}';
ON TABLE HOLD AS OPENRSM
END
-RUN

TABLE FILE OPENRSM
SUM
TOTAL
BY EXPENSEREGION
BY EXPDATE_MMYY
ON TABLE HOLD AS TOTOPENRSM
END
-RUN

TABLE FILE EXPENSEBASE
PRINT
PAYEEID
FULLNAME
POSITION
COMPUTE EXPDATE_MMYY/MtYY = EXPENSEDATE;
COSTCENTER
COMPUTE TOTAL/D15.2M = TOTALAMOUNT;
COMPUTE BUDGET/D15.2M = &JBUDGET;
COMPUTE VARIANCE/D15.2M = BUDGET - TOTAL;
COMPUTE EXPENSEREGION/A50 = 'John Boeckh';
EXPENSETYPE
BY EXPENSEID
WHERE PAYEEID EQ '{8E0B13A0-35AA-DB11-9657-001438BFF493}';
WHERE EXPENSEREGION NE 'GGOF';
ON TABLE HOLD AS JBEXPENSE
END
-RUN

TABLE FILE JBEXPENSE
SUM
TOTAL
BY EXPENSEREGION
BY EXPDATE_MMYY
ON TABLE HOLD AS TOTJBEXPENSE
END
-RUN

TABLE FILE MERCHANDISEREQUEST
PRINT
REQUESTID AS 'PAYEEID'
COMPUTE FULLNAME/A160 = ' ';
COMPUTE POSITION/A100 = ' ';
COMPUTE EXPDATE_MMYY/MtYY = REQUESTDATE;
COMPUTE COSTCENTER/A5 = ' ';
COMPUTE TOTAL/D15.2M = FILLEDAMOUNT;
COMPUTE BUDGET/D15.2M = &BUDGET;
COMPUTE VARIANCE/D15.2M = BUDGET - TOTAL;
REGION AS 'EXPENSEREGION'
COMPUTE EXPENSETYPE/A20 = ' ';
BY REQUESTID AS 'EXPENSEID'
WHERE STATUS = 'FILLED';
ON TABLE HOLD AS MERCH
END
-RUN

TABLE FILE MERCH
SUM
TOTAL
BY EXPENSEREGION
BY EXPDATE_MMYY
ON TABLE HOLD AS TOTMERCH
END
-RUN

TABLE FILE TOTMERCH
PRINT *
ON TABLE HOLD AS EXPENSES
MORE
FILE TOTJBEXPENSE
MORE
FILE TOTOPENRSM
MORE
FILE TOTCONTACT
MORE
FILE TOTNONCONTACT
END
-RUN

TABLE FILE EXPENSES
SUM
TOTAL
BY EXPENSEREGION
BY EXPDATE_MMYY
ON TABLE HOLD AS TOTEXPENSES
END
-RUN

TABLE FILE TOTEXPENSES
SUM
TOTAL
COMPUTE BUDGET/D15.2M = IF EXPENSEREGION EQ 'John Boeckh' THEN &JBUDGET ELSE &BUDGET;
COMPUTE VARIANCE/D15.2M = BUDGET - TOTAL;
BY EXPENSEREGION
BY EXPDATE_MMYY
ON TABLE HOLD AS ALLEXPENSES
END
-RUN

DEFINE FILE ALLEXPENSES
JREGION/A50 = IF EXPENSEREGION EQ 'John Boeckh' THEN 'ZZZZZ' ELSE EXPENSEREGION;
END

TABLE FILE ALLEXPENSES
SUM
TOTAL/C AS 'Actual'
MAX.BUDGET/C AS 'Budget'
MAX.VARIANCE/C AS 'Variance'
BY JREGION NOPRINT
BY EXPENSEREGION AS 'Region'
ACROSS EXPDATE_MMYY AS ' ' ROW-TOTAL AS 'FYTD Total'
WHERE EXPENSEREGION NE ' ';
ON TABLE COLUMN-TOTAL
ON TABLE SET ONLINE-FMT HTML
ON TABLE SET STYLE *
UNITS = IN, PAGESIZE = LEGAL, ORIENTATION = LANDSCAPE, GRID = OFF, SQUEEZE = ON,
LEFTMARGIN = 0.5, TOPMARGIN = 0.25, RIGHTMARGIN = 0.025, BOTTOMMARGIN = 0.025, LEFTGAP = 0.055, RIGHTGAP = 0.055, $
TYPE = REPORT, FONT = 'Arial', SIZE = 9, $
TYPE = HEADING, SIZE = 9, STYLE = BOLD, $
TYPE = TITLE, SIZE = 9, STYLE = BOLD, $
TYPE = DATA, SIZE = 8, JUSTIFY = LEFT, COLUMN = EXPENSEREGION, BACKCOLOR = ( RGB(243 244 248) RGB(255 255 255) ), $
TYPE = DATA, COLUMN = EXPENSEREGION, FOCEXEC = EXPBMOTY(RGN = EXPENSEREGION), $
TYPE = DATA, SIZE = 8, JUSTIFY = RIGHT, BACKCOLOR = ( RGB(243 244 248) RGB(255 255 255) ), $
TYPE = ACROSS, FONT = 'ARIAL', SIZE = 9, STYLE = BOLD, JUSTIFY = CENTER, $
TYPE = GRANDTOTAL, FONT = 'ARIAL', SIZE = 8, STYLE = BOLD, $
TYPE = TITLE, COLUMN = ROWTOTAL(1), STYLE = BOLD, $
TYPE = TITLE, COLUMN = ROWTOTAL(2), STYLE = BOLD, $
TYPE = TITLE, COLUMN = ROWTOTAL(3), STYLE = BOLD, $
TYPE = DATA, COLUMN = ROWTOTAL(1), STYLE = BOLD, $
TYPE = DATA, COLUMN = ROWTOTAL(2), STYLE = BOLD, $
TYPE = DATA, COLUMN = ROWTOTAL(3), STYLE = BOLD, $
TYPE = ACROSSVALUE, COLUMN = ROWTOTAL(1), STYLE = BOLD, $
ENDSTYLE
END

-HTMLFORM BEGIN

-HTMLFORM END
-EXIT

try

SET NODATA = '5000'

Frans

I've tried that already. That won't work because even if under Totals I have no data, it will show $5000 there too. I only need it to show $5000 under budget if there's no data.

Use the COLUMNS keyword in the ACROSS statement to 1) force missing across values to display, 2) exclude non-mentioned values from the report or 3) to define your own across sequence.

TABLE FILE CAR
SUM
SALES/D6
ACROSS COUNTRY COLUMNS 'FRANCE' AND 'england' AND 'CANADA' AND 'CHINA' AND 'JAPAN' 
BY SEATS
END

This, of course, does not solve the missing Budget values.

As I've said before, it might be a good idea to generate HOLD files that contain all the data (zero or otherwise) that you want to show in your report. You make up the missing data as zero values in a HOLD file so they will then be available for the report. It's a bit more complex than a simple report.

I would suggest to use FML to do this.
You can "compute" a budget to become 5000 if it is empty and you have better control over the rows and columns.

I'm not fimiliar with FML.

I've used all the different methods mentioned, however, I still can't get this to work.

This is what the report looks like right now:

Region______Oct. 2007_____________Nov. 2007_____________Dec. 2007_____________FYTD Total
__________Total|Budget|Variance___Total|Budget|Variance___Total|Budget|Variance___Total|Budget|Variance
Region 1__$100_|$5000_|$4900____$550_|$5000_|$4450____$1000|$5000_|$4000____$1650|$15000|$13350
Region 2__$0___|$0____|$0_______$2000|$5000_|$3000____$0___|$0____|$0_______$2000|$5000_|$3000
Region 3__$500_|$5000_|$4500____$500_|$5000_|$4500____$1000|$5000_|$4000____$2000|$15000|$13000
Region 4__$0___|$0____|$0_______$2000|$5000_|$3000____$0___|$0____|$0_______$2000|$5000_|$3000
Region 5__$0___|$0____|$0_______$2000|$5000_|$3000____$0___|$0____|$0_______$2000|$5000_|$3000
Region 6__$100_|$5000_|$4900____$550_|$5000_|$4450____$1000|$5000 |$4000_____$1650|$15000|$13350

What I need to happen is that where ever there's $0, regardless of having a $0 the budget should always be $5000. Thus, making the FYTD Total Budget $15000 since there are 3 months so far. So every month that gets added the budget increases.

I need it to look like this basically:

Region______Oct. 2007_____________Nov. 2007_____________Dec. 2007_____________FYTD Total
__________Total|Budget|Variance___Total|Budget|Variance___Total|Budget|Variance___Total|Budget|Variance
Region 1__$100_|$5000_|$4900____$550_|$5000_|$4450____$1000|$5000_|$4000____$1650|$15000|$13350
Region 2__$0___|$5000_|$5000____$2000|$5000_|$3000____$0___|$5000_|$5000____$2000|$15000|$3000
Region 3__$500_|$5000_|$4500____$500_|$5000_|$4500____$1000|$5000_|$4000____$2000|$15000|$13000
Region 4__$0___|$5000_|$5000____$2000|$5000_|$3000____$0___|$5000_|$5000____$2000|$15000|$3000
Region 5__$0___|$5000_|$5000____$2000|$5000_|$3000____$0___|$5000_|$5000____$2000|$15000|$3000
Region 6__$100_|$5000_|$4900____$550_|$5000_|$4450____$1000|$5000 |$4000_____$1650|$15000|$13350

Please help, I've tried everything that I could think of. I've created different hold files but I think my problem is the across on the dates.

This message has been edited. Last edited by: <GGOFAnalyst>,

I completely agree with

quote:

Francis Mariani

Try creating Hold file with all values that you want (Replacing zeros with values or what ever).
then try using Required formating (Across or totals).

I think you're tackling types of things that you haven't gained enough experience for - yet

Extract your expense data for the regions you need to report on, creating a dummy field that will be used in a subsequent join.

Then to generate a hold file that will contain a row for each month in a fiscal year, you can use the following idea: extract the year/month info for the 12 months from the Time dimension table you have - create a focus database with this using a dummy field as an index. This ensures you will always have 12 months - because you're going after existing data (in the Time table), and NOT non-existing data in the Expense table.

Join the two files and out pops 12 rows per region (or whatever other sort fields you might have).

Give the code below a try, I'm hoping this gets you back on the road to success...

-SET &EXPFISYEAR = 2007;

SET HOLDLIST=PRINTONLY
SET ASNAMES=ON
SET HOLDFORMAT=ALPHA

-*-- RETRIEVE EXPENSE REGIONS
TABLE FILE EXPENSEBASE
SUM
COMPUTE DUMMY1/A1 = '';
BY EXPENSEREGION
ON TABLE HOLD AS TOTEXPENSES
END
-RUN

-*-- RETRIEVE YEAR/MONTH FOR ONE FISCAL YEAR
TABLE FILE TIME
SUM
MIN.YMONTH/I6
COMPUTE DUMMY1/A1 = '';
BY YMONTH NOPRINT
WHERE FISCAL_YEAR EQ &EXPFISYEAR
ON TABLE HOLD AS HTIME FORMAT FOCUS INDEX DUMMY1
END

JOIN DUMMY1 IN TOTEXPENSES TO ALL DUMMY1 IN HTIME AS J1

TABLE FILE TOTEXPENSES
SUM
COMPUTE BUDGET/D6 = 5000;
BY EXPENSEREGION
BY YMONTH
END

Yea this is all a learning experiece.
Thanks Francis. I'm gonna play around with that and see what happens.

To combine your actual expenses and the budget:

-SET &EXPFISYEAR  = 2008;
-SET &EXPFISYEAR1 = &EXPFISYEAR - 1;
-SET &EXPFISSTART = &EXPFISYEAR1 | '-11-01';
-SET &EXPFISEND   = &EXPFISYEAR  | '-11-01';

SET HOLDLIST=PRINTONLY
SET ASNAMES=ON
SET HOLDFORMAT=ALPHA
-RUN

-*-- RETRIEVE EXPENSE ACTUAL AMOUNTS
TABLE FILE EXPENSEBASE
SUM
EXPENSEDATE/I6YYM AS 'YMONTH'
TOTALAMOUNT/D20 
COMPUTE BUDGET/D20 = 0;
BY EXPENSEREGION
BY EXPENSEDATE NOPRINT
WHERE EXPENSEDATE  GE '&EXPFISSTART' AND EXPENSEDATE LT '&EXPFISEND'
ON TABLE HOLD AS ACTEXPENSES
END
-RUN

-*-- RETRIEVE EXPENSE REGIONS
TABLE FILE EXPENSEBASE
SUM
COMPUTE DUMMY1/A1 = '';
BY EXPENSEREGION
ON TABLE HOLD AS REGEXPENSES
END
-RUN

-*-- RETRIEVE YEAR/MONTH FOR ONE FISCAL YEAR
TABLE FILE TIME
SUM
MIN.YMONTH/I6YYM
COMPUTE DUMMY1/A1 = '';
BY YMONTH NOPRINT
WHERE FISCAL_YEAR EQ &EXPFISYEAR
ON TABLE HOLD AS HTIME FORMAT FOCUS INDEX DUMMY1
END
-RUN

JOIN DUMMY1 IN REGEXPENSES TO ALL DUMMY1 IN HTIME AS J1
-RUN

TABLE FILE REGEXPENSES
SUM
COMPUTE TOTALAMOUNT/D20 = 0;
COMPUTE BUDGET/D20 = 5000;
BY EXPENSEREGION
BY YMONTH
ON TABLE HOLD AS BUDEXPENSES
END
-RUN

TABLE FILE ACTEXPENSES
SUM 
TOTALAMOUNT
BUDGET
BY EXPENSEREGION
ACROSS YMONTH
MORE
FILE BUDEXPENSES
END
-RUN