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
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
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.
December 13, 2007, 03:40 PM
Francis Mariani
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.
Francis
Give me code, or give me retirement. In FOCUS since 1991
Production: WF 7.7.05M, Dev Studio, BID, MRE, WebSphere, DB2 / Test: WF 8.1.05M, App Studio, BI Portal, Report Caster, jQuery, HighCharts, Apache Tomcat, MS SQL Server
December 13, 2007, 04:05 PM
FrankDutch
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.
Frank
prod: WF 7.6.10 platform Windows, databases: msSQL2000, msSQL2005, RMS, Oracle, Sybase,IE7 test: WF 7.6.10 on the same platform and databases,IE7
December 14, 2007, 10:12 AM
<GGOFAnalyst>
I'm not fimiliar with FML.
December 18, 2007, 02:06 PM
<GGOFAnalyst>
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>,
December 18, 2007, 02:53 PM
TryFocus
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).
Prod: WF 7.6.10 windows. -- MRE/Dashboard/Self Service/ReportCaster - Windows XP
December 18, 2007, 03:24 PM
Francis Mariani
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
Francis
Give me code, or give me retirement. In FOCUS since 1991
Production: WF 7.7.05M, Dev Studio, BID, MRE, WebSphere, DB2 / Test: WF 8.1.05M, App Studio, BI Portal, Report Caster, jQuery, HighCharts, Apache Tomcat, MS SQL Server
December 18, 2007, 04:06 PM
<GGOFAnalyst>
Yea this is all a learning experiece. Thanks Francis. I'm gonna play around with that and see what happens.
December 19, 2007, 10:28 AM
Francis Mariani
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
Francis
Give me code, or give me retirement. In FOCUS since 1991
Production: WF 7.7.05M, Dev Studio, BID, MRE, WebSphere, DB2 / Test: WF 8.1.05M, App Studio, BI Portal, Report Caster, jQuery, HighCharts, Apache Tomcat, MS SQL Server