Focal Point Banner
Community Center Education Summit Technical Support User Groups
Let's Get Social!

Facebook Twitter LinkedIn YouTube
Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     Missing Data on Across
Go
New
Search
Notify
Tools
Reply
  
Missing Data on Across
 Login/Join
 
<GGOFAnalyst>
posted
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


Expense Reports










&RPTNAME.EVAL



-HTMLFORM END
-EXIT
 
Reply With QuoteReport This Post
Guru
posted Hide Post
try

SET NODATA = '5000'

Frans


Test: WF 8.2
Prod: WF 8.2
DB: Progress, REST, IBM UniVerse/UniData, SQLServer, MySQL, PostgreSQL, Oracle, Greenplum, Athena.
 
Posts: 453 | Location: Europe | Registered: February 05, 2007Reply With QuoteReport This Post
<GGOFAnalyst>
posted
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.
 
Reply With QuoteReport This Post
Expert
posted Hide Post
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
 
Posts: 10577 | Location: Toronto, Ontario, Canada | Registered: April 27, 2005Reply With QuoteReport This Post
Virtuoso
posted Hide Post
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

 
Posts: 2387 | Location: Amsterdam, the Netherlands | Registered: December 03, 2006Reply With QuoteReport This Post
<GGOFAnalyst>
posted
I'm not fimiliar with FML.
 
Reply With QuoteReport This Post
<GGOFAnalyst>
posted
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>,
 
Reply With QuoteReport This Post
Gold member
posted Hide Post
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
 
Posts: 82 | Location: Chicago | Registered: September 28, 2005Reply With QuoteReport This Post
Expert
posted Hide Post
I think you're tackling types of things that you haven't gained enough experience for - yet Smiler

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
 
Posts: 10577 | Location: Toronto, Ontario, Canada | Registered: April 27, 2005Reply With QuoteReport This Post
<GGOFAnalyst>
posted
Yea this is all a learning experiece.
Thanks Francis. I'm gonna play around with that and see what happens.
 
Reply With QuoteReport This Post
Expert
posted Hide Post
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
 
Posts: 10577 | Location: Toronto, Ontario, Canada | Registered: April 27, 2005Reply With QuoteReport This Post
  Powered by Social Strata  
 

Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     Missing Data on Across

Copyright © 1996-2018 Information Builders, leaders in enterprise business intelligence.