January 16, 2007, 11:27 AM
simbawrite &variables to an EXCEL report
I have &variables that calculate the number of workdays per month. They are not associated with any table - just purely &variables. Is there anyway I can write the value of these &variables as a line on an EXCEL spreadsheet? Thanks.
January 16, 2007, 11:52 AM
FrankDutchI think there is...
-SET &WORKDAYS=18;
TABLE FILE CAR
COUNT MODEL NOPRINT
BY COUNTRY NOPRINT
HEADING &WORKDAYS
ON TABLE ....EXL2K
END
Or something like that, I do not know the proper coding since I'm not on the office
January 16, 2007, 11:54 AM
FrankDutchI just wonder why you do this?
I'm not a Microsoft guru, but I'm sure there is a function in Excel that can calculate the workdays too.
January 16, 2007, 01:53 PM
simbaThere probably is a way to do this in EXCEL. For the record, I could write this whole report in EXCEL easier than it has been doing it in WebFocus. However, I need to perform 2 calculations based on the workday number. Since I've written the rest of the report in WebFocus, I wanted to do this with WebFocus. Thanks.
January 16, 2007, 02:53 PM
codermonkeyYou want it in a heading or footing? You can simply do the following:
TABLE FILE tablename
HEADING
"Workdays per month: &varname"
PRINT....
END
Is this what you mean?
January 16, 2007, 03:49 PM
simbaI wanted it to be a line in an EXCEL spreadsheet:
Workdays 20 18 22 21 and so on.
The month/year headings are already on the spreadsheet.
January 16, 2007, 11:39 PM
HåkanSimba,
use a DEFINE. Eg.
DEFINE FILE CAR
WORKDAYS/I2 = &WORKDAYS;
END
TABLE FILE CAR
SUM WORKDAYS
ACROSS COUNTRY
ON TABLE PCHOLD FORMAT EXL2K
END
Håkan
January 17, 2007, 03:19 AM
Tony A... or a COMPUTE
T
January 17, 2007, 10:59 AM
FrankDutchwe have a database with year, month fields for our general ledger.
I created this report what exactly gives you the output as wanted
-DEFAULT &YR=2006;
DEFINE FILE KCM_OAS_PERLIST
BVDM/YYMD=DATEMOV(ENDDATE, 'BOM');
WORKD/I5=DATEDIF(BVDM, ENDDATE, 'BD');
END
TABLE FILE KCM_OAS_PERLIST
SUM
WORKD
BY YR
BY PERIOD AS ''
WHERE ( CMPCODE EQ 'F500' ) AND ( YR EQ &YR ) AND ( PERIOD FROM 1 TO 12 );
ON TABLE HOLD
END
DEFINE FILE HOLD
1M/I3=IF PERIOD EQ 1 THEN WORKD ELSE 0;
2M/I3=IF PERIOD EQ 2 THEN WORKD ELSE 0;
3M/I3=IF PERIOD EQ 3 THEN WORKD ELSE 0;
4M/I3=IF PERIOD EQ 4 THEN WORKD ELSE 0;
5M/I3=IF PERIOD EQ 5 THEN WORKD ELSE 0;
6M/I3=IF PERIOD EQ 6 THEN WORKD ELSE 0;
7M/I3=IF PERIOD EQ 7 THEN WORKD ELSE 0;
8M/I3=IF PERIOD EQ 8 THEN WORKD ELSE 0;
9M/I3=IF PERIOD EQ 9 THEN WORKD ELSE 0;
10M/I3=IF PERIOD EQ 10 THEN WORKD ELSE 0;
11M/I3=IF PERIOD EQ 11 THEN WORKD ELSE 0;
12M/I3=IF PERIOD EQ 12 THEN WORKD ELSE 0;
TEXTLINE/A40='Workingdays for the year ';
END
TABLE FILE HOLD
SUM
1M AS ''
2M AS ''
3M AS ''
4M AS ''
5M AS ''
6M AS ''
7M AS ''
8M AS ''
9M AS ''
10M AS ''
11M AS ''
12M AS ''
BY TEXTLINE AS ''
ON TABLE SET PAGE-NUM OFF
ON TABLE NOTOTAL
ON TABLE PCHOLD FORMAT EXL2K
END
You have to create a master with a
yr field (2006)
period (1 to 12)
and than it could work.
January 17, 2007, 01:38 PM
simbaThank you FrankDutch!! I got your solution to work for me.