-* Capture max. Fiscal Year and Month Number available
TABLE FILE MYDATA
SUM
        MAX.FISCALYEAR AS 'CurrentFiscalYear'
        MAX.FYMTHNUMBER AS 'CurrentFiscalMonthNumber'
ON TABLE HOLD AS HMAXDATA
ON TABLE SET HOLDLIST PRINTONLY
ON TABLE SET ASNAMES ON
END
-RUN
-READFILE HMAXDATA

-* Get measurements for the most current year and period
DEFINE FILE MYDATA
FYTD/D12=IF FISCALYEAR EQ &CurrentFiscalYear THEN QTY ELSE 0;
FMTD/D12=IF FISCALYEAR EQ &CurrentFiscalYear AND FYMTHNUMBER EQ &CurrentFiscalMonthNumber THEN QTY ELSE 0;
ITD/D12 = QTY;
END
TABLE FILE MYDATA
SUM 
ITD AS 'Since, Inception'
FYTD AS 'Fiscal Year, To Date'
FMTD AS 'Fiscal Month, To Date'
BY LOWEST LOCATION
HEADING
"Appointment Count Summary By Location:"
ON TABLE SET PAGE-NUM NOLEAD 
ON TABLE COLUMN-TOTAL AS 'TOTAL'
ON TABLE PCHOLD FORMAT HTML
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *
INCLUDE = endeflt,
$
ENDSTYLE
END

DEFINE FILE MYDATA
CURRYEAR/YY='&YYMD';
CURRMONTH/M='&YYMD';
CURRYRMONTH/YYM='&YYMD';  (optional but useful)
END

quote:

-* Capture max. Fiscal Year and Month Number available
TABLE FILE MYDATA
SUM
MAX.FISCALYEAR AS 'CurrentFiscalYear'
MAX.FYMTHNUMBER AS 'CurrentFiscalMonthNumber'
ON TABLE HOLD AS HMAXDATA
ON TABLE SET HOLDLIST PRINTONLY
ON TABLE SET ASNAMES ON
END
-RUN
-READFILE HMAXDATA

quote:

it is still prompting me for the two values

quote:

njsden's solution - as well as the original post - seems way more complex than required, unless I miss the point of the question.

quote:

That's probably because your procedure sits in the MRE and has the "Prompt for parameters" enabled. Just locate our procedure, right-click it and select "Properties". Look for the "Prompt for parameters" checkbox and uncheck it.

Tried that...it errored out at the &CurrentFiscalYear code


Re: I think the complexity lies in the fact that Robert wants to limit his report to the most recent Fiscal data available in his table which may or may not be related to the current system date; that's why &YYMD and other derived variables wouldn't help in this particular case, or at least that's what I understood from his original post.


Exactly!


Prod/Dev: WF Server 7.7.05M/Win 2008 - WF Client 7.7.05/Win 2008 - Dev. Studio: 7.7.05/Windows 7 - DBMS: Oracle 11g Rel 2
Test: Dev. Studio 7.7.05 /Windows 7 (Local) Output: Mostly HTML and EXL2K

  
DEFINE FILE MYDATA
FYTD/D12=IF FISCALYEAR EQ '&&FCN.EVAL' THEN QTY ELSE 0;
FMTD/D12=IF FISCALYEAR EQ '&&FCN.EVAL' AND FYMTHNUMBER EQ '&&PCN.EVAL' THEN QTY ELSE 0;
ITD/D12 = QTY;
END
TABLE FILE MYDATA
SUM 
ITD AS 'Since, Inception'
FYTD AS 'Fiscal Year, To Date'
FMTD AS 'Fiscal Month, To Date'
BY LOWEST LOCATION
HEADING
"Appointment Count Summary By Location:"
ON TABLE SET PAGE-NUM NOLEAD 
ON TABLE COLUMN-TOTAL AS 'TOTAL'
ON TABLE PCHOLD FORMAT HTML
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *
INCLUDE = endeflt,
$
ENDSTYLE
END

quote:

I want to report QuantitySinceInception, FiscalYearToDate and MonthToDate below by location.

DEFINE FILE HOLD
    YR/YY = YRMTH;
    MTH/M = YRMTH;
    MTH2/I2 = IF MTH LE 5 THEN (MTH + 7) ELSE (MTH - 5);
    MONTH/MT = YRMTH;
    YR1/YY = YRMTH;
    YR2/YY = IF MTH LE 05 THEN (YR1 -1) ELSE YR1;
    YR3/YY = YR2 + 1;
    YEAR/A11 = EDIT(YR2) | ' - ' | EDIT(YR3);
END

TABLE FILE HOLD
PRINT
     'HOLD.HOLD.TOTCOMMSLS' AS ' COMMISSION,    FOB,   MONTHLY'
     COMPUTE CUMFOB/D13.2 = IF MONTH EQ 'JUN' THEN TOTCOMMSLS ELSE TOTCOMMSLS + LAST CUMFOB;
 AS ' COMMISSION ,    FOB, CUMULATIVE'
     'HOLD.HOLD.TOTCOMM' AS ' COMMISSION,   INCOME,   MONTHLY'
     COMPUTE CUMCOMM/D13.2 = IF MONTH EQ 'JUN' THEN TOTCOMM ELSE TOTCOMM + LAST CUMCOMM;
.
.
.
.
.

BY 'HOLD.HOLD.YEAR' NOPRINT
BY 'HOLD.HOLD.MTH2' NOPRINT
BY 'HOLD.HOLD.MONTH'

ON HOLD.HOLD.YEAR SUBTOTAL
     'HOLD.HOLD.TOTCOMMSLS'
     'HOLD.HOLD.TOTCOMM'
     'HOLD.HOLD.TOTDIRSLS'
     'HOLD.HOLD.PROFIT'
     'HOLD.HOLD.GROSSINC'
     'HOLD.HOLD.EARNINGS'
     'HOLD.HOLD.EXPENSE'
     NETP AS ''
ON HOLD.HOLD.YEAR PAGE-BREAK
.
.
.

quote:

I tried the READFILE suggestion..did not work.

quote:

FMTD/D12=IF WALKINDATA.WALKINDA.FISCALYEAR EQ &CurrentFiscalYear AND WALKINDATA.WALKINDA.PERIOD = &CurrentPeriod THEN WALKINDATA.WALKINDA.APPTS ELSE 0;

DEFINE FILE WALKINDATA
CURR_MTH/M=&YYMD;
CURR_YEAR/YY=&YYMD;
CURR_FISCAL_YEAR/YY = IF CURR_MTH LE 05 THEN (CURR_YEAR -1) ELSE CURR_YEAR;

FYTD/D12=IF FISCALYEAR EQ CURR_FISCAL_YEAR THEN APPTS ELSE 0;
FMTD/D12=IF FISCALYEAR EQ CURR_FISCAL_YEAR AND PERIOD EQ CURR_MTH THEN APPTS ELSE 0;

-* The following line is not necessary since you want all appointments to date without condition
-* ITD/D12=IF FISCALYEAR LE &CurrentFiscalYear THEN APPTS ELSE 0;
END

TABLE FILE WALKINDATA
SUM 
 LOCATIONNAME
-* ITD AS 'Since, Inception'
 APPTS AS 'Since, Inception'
 FYTD AS 'Fiscal Year, To Date'
 FMTD AS 'Fiscal Month, To Date'

-* BY LOWEST isn't doing anything - it's assumed
-* BY LOWEST WALKINDATA.WALKINDA.LOCATION

BY WALKINDATA.WALKINDA.LOCATION
HEADING
"Appointment Count Summary By Location:"
"Inception thru &CurrentPeriod"
ON TABLE SET PAGE-NUM NOLEAD 
ON TABLE COLUMN-TOTAL AS 'TOTAL'
ON TABLE PCHOLD FORMAT HTML
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *
 INCLUDE = endeflt,
$
TYPE=REPORT,

-*  hmmm.... the following line looks fishy to me - you are doing a TABLE request but this references a GRAPH
-*  but I don't have much experience with graphs so no comment ..
 GRAPHCOLOR='GREEN',
...etc
ENDSTYLE
END

TABLE FILE WALKINDATA
SUM
MAX.FISCALYEAR AS 'CURRENTFISCALYEAR'
MAX.PERIOD AS 'CURRENTPERIOD'
PRINT *
ON TABLE HOLD AS WALKIN_HOLD
ON TABLE SET ASNAMES ON
END

DEFINE FILE WALKIN_HOLD
FYTD/D12=IF FISCALYEAR EQ CURRENTFISCALYEAR THEN APPTS ELSE 0;
FMTD/D12=IF FISCALYEAR EQ CURRENTFISCALYEAR AND PERIOD EQ CURRENTPERIOD THEN APPTS ELSE 0;
ITD/D12=IF FISCALYEAR LE CURRENTFISCALYEAR THEN APPTS ELSE 0;
END

TABLE FILE WALKIN_HOLD
SUM 
LOCATIONNAME
ITD AS 'Since, Inception'
FYTD AS 'Fiscal Year, To Date'
FMTD AS 'Fiscal Month, To Date'
BY LOCATION
HEADING
"Appointment Count Summary By Location:"
"Inception thru <CURRENTPERIOD"
ON TABLE SET PAGE-NUM NOLEAD 
ON TABLE COLUMN-TOTAL AS 'TOTAL'
ON TABLE PCHOLD FORMAT HTML
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *
INCLUDE = endeflt, 
.
.
.