Hey all!

So, I'm fairly new to using WF8 to build out reports with trends and drill-downs.

I have a scenario where I have a WeekEndDate field, and when filtered against, I need to show data for various measures for a set of individuals belonging to a given account (another filter) by the business week the WeekEndDate selected belongs to.

I then need to make a report that is being drilled down to by an individual's name at the above described summary level that gives similar summary data for that individual drilled to, showing that business week's data, as well as the 3 previous business week's data for that individual.

I've currently set up my top level summary report as follows:
Unresolved params: WeekEndDate & Account Name
Header: For: Account Name/A50V
Fields: WeekEndDate/MDYY (BY) - Individuals' Names/A35V (SUM) - Measure 1 - Measure 2 - Measure x...

For the above set up, I want to see the 1 selected week's measures for each and every individual belonging to the selected account in summary for that week. The individuals will then be drillable.

My drill-down lower level summary report for each individual is to be set up similarly, but show the previous 3 weeks' data as well for the given individual selected:

WeekEndDate - Individual's Name - Measure 1 - Measure 2 - Measure x...
WeekEndDate (previous week) <- want
WeekEndDate (2wks previous) <- want
WeekEndDate (3wks previous) <- want

For some reason, I can't get my top-level summary report to show all individuals for the selected account summarized for the one business week (WeekEndDate) selected. If I use the SUM verb I get one individual to populate and the rest are missing. If I use the PRINT verb, it gives me all individuals for the selected account for the selected business week, but gives me duplicate entries due to several business dates' data showing for that business week.

On the opposite coin, for my lower level report, I can't sort by WeekEndDate due to it only giving my the one week selected and not the previous 3 I need as well.

Is the issue due to not being able to SUM alphas? Or do I need to sort by an addition hidden date dimension or something you think?

Any guidance would be greatly appreciated.

Thanks in advance!

This message has been edited. Last edited by: CoolGuy,

CoolGuy,

Thats a lot of stuff to follow... Can you post what you have so far? This is probably simpler then how your describing it, but its hard to follow.

Thanks for helping eric.woerle,

Here's the code thus far minus styling code and defines:

-* Test report to develop top-level summary report that displays user-selected business week summary data for each
-* cashier within a selected account/store.

-* Should show only the business week selected, all cashiers within selected account once, and their summarized measures.
-* This report will allow user to drill down by cashier and get that cashier's summarized measures seen in top-level report
-* and that cashier's summarized measures for the 3 weeks previous as well.

TABLE FILE SRPROD
PRINT
     SRPROD.SRPROD.SRPDCASHIERNAME AS 'Cashier'
     SRPROD.SRPROD.RINGTIMEMIN AS 'Ring Time,per min'
     SRPROD.SRPROD.TNDRTIMEMIN AS 'Tender Time,per min'
     SRPROD.SRPROD.SESSTIMEMIN AS 'Session Time,per min'
     SRPROD.SRPROD.IDLETIMEMIN AS 'Idle Time,per min'
     SRPROD.SRPROD.LCKTIMEMIN AS 'Lock Time,per min'
BY  LOWEST SRPROD.DIMDATE.WEEKENDING AS 'Week Ending'
ON TABLE SUBHEAD
"Cashier Statistics (Summary level) for <SRPROD.DIMSTORE.DSNAME"
WHERE ( SRPROD.DIMSTORE.DSNAME EQ '&STRNAME.(FIND SRPROD.DIMSTORE.DSNAME IN SRPROD).Store:.' ) AND ( SRPROD.DIMDATE.WEEKENDING EQ '&WEEKENDING.(FIND SRPROD.DIMDATE.WEEKENDING IN SRPROD).Week Ending:.' );
ON TABLE SET AUTOFIT ON
ON TABLE SET PAGE-NUM NOLEAD
ON TABLE SET ASNAMES ON
ON TABLE NOTOTAL
ON TABLE PCHOLD FORMAT HTML

The report canvas/painter produced it, so that's why it's so ugly. Check my comments in the code for a possibly better explanation of what I'm trying to get to work for this report.

Thanks!

The closest I have gotten was:

TABLE FILE SRPROD
PRINT
     DST.SRPROD.SRPROD.SRPDCASHIERNAME AS 'Cashier'
     SRPROD.SRPROD.RINGTIMEMIN AS 'Ring Time,per min'
     SRPROD.SRPROD.TNDRTIMEMIN AS 'Tender Time,per min'
     SRPROD.SRPROD.SESSTIMEMIN AS 'Session Time,per min'
     SRPROD.SRPROD.IDLETIMEMIN AS 'Idle Time,per min'
     SRPROD.SRPROD.LCKTIMEMIN AS 'Lock Time,per min'
BY  LOWEST SRPROD.DIMDATE.WEEKENDING AS 'Week Ending'

Adding the DST. to the cashier name column brings down the list of cashier name entries to only distinct values by the week end date given. Then the measures I try to display must be under the PRINT verb as well due to the DST. being used. Tried inserting the SUM verb after the PRINT verb, but WF didn't like that.

Once I can figure that out, I need to figure out how to display 4 business weeks' worth of measures for a single cashier that has been drilled down to:

BY WEEKENDING AS 'Week Ending' but show 4 weeks instead of just the 1.

eric.woerle and others,

I just opened up a case for this one. Thank you all for your help anyways.

quote:

PRINT
DST.SRPROD.SRPROD.SRPDCASHIERNAME AS 'Cashier'
SRPROD.SRPROD.RINGTIMEMIN AS 'Ring Time,per min'
SRPROD.SRPROD.TNDRTIMEMIN AS 'Tender Time,per min'
SRPROD.SRPROD.SESSTIMEMIN AS 'Session Time,per min'
SRPROD.SRPROD.IDLETIMEMIN AS 'Idle Time,per min'
SRPROD.SRPROD.LCKTIMEMIN AS 'Lock Time,per min'
BY LOWEST SRPROD.DIMDATE.WEEKENDING AS 'Week Ending'

Sounds more like you need...

SUM
     RINGTIMEMIN AS 'Ring Time,per min'
     TNDRTIMEMIN AS 'Tender Time,per min'
     SESSTIMEMIN AS 'Session Time,per min'
     IDLETIMEMIN AS 'Idle Time,per min'
     LCKTIMEMIN AS 'Lock Time,per min'
BY   WEEKENDING AS 'Week Ending'
BY   SRPDCASHIERNAME AS 'Cashier'

CoolGuy. I think you are overthinking this in your head. Why not just make it all a sum and BY WEEKENDING BY SRPDCASHIERNAME?

 TABLE FILE SRPROD
SUN
     SRPROD.SRPROD.RINGTIMEMIN AS 'Ring Time,per min'
     SRPROD.SRPROD.TNDRTIMEMIN AS 'Tender Time,per min'
     SRPROD.SRPROD.SESSTIMEMIN AS 'Session Time,per min'
     SRPROD.SRPROD.IDLETIMEMIN AS 'Idle Time,per min'
     SRPROD.SRPROD.LCKTIMEMIN AS 'Lock Time,per min'
BY  LOWEST SRPROD.DIMDATE.WEEKENDING AS 'Week Ending'
BY SRPROD.SRPROD.SRPDCASHIERNAME AS 'Cashier' 

I'm assuming that time is numeric. Summarizing should be fine.'

When you pass the Week Ending Date from the drill. I would use the Dialogue Manager command AYMD()to roll that date back 28 days and filter where the date is in between the passed date and the calculated varialbe. Easy peasy.

 -DEFAULTH &WKENDDATE = '20150702';

-SET &BEGDATE = AYMD(&WKENDDATE.QUOTEDSTRING, -28,'I8YYMD');

-TYPE &BEGDATE 

As to the drilldown, you'll pass columns SRPDCASHIERNAME and DSNAME and WEEKENDING as the drilldown parameters. You need to compute the weekending value of the third prior week, and screen on the WEEKENDING database column accordingly in the drilldown report.

If &WEEKENDING is received as 'yyyymmdd',

-SET &FIRSTWEEKSEND=AYMD(&WEEKENDING ,-21,I8YYMD);

should give you the required start-week value, and

WHERE
    ( SRPROD.DIMDATE.WEEKENDING GE '&FIRSTWEEKSEND') 
AND ( SRPROD.DIMDATE.WEEKENDING LE '&WEEKENDING')
... ;

will pull the required four weeks

Thanks for your help Jack and Eric!

Your way of organizing the fields does work for my top-level report layout. I really appreciate it.

I got the drill-down working fine, and added in the additional -SET utilizing the AYMD() function to subtract 28 days from the &WEEKENDING value storing it in &FOURWKSPREV to be screened on in the request.

For some reason, I am only getting the &WEEKENDING value to show in my lower-level drill-down report.

I want it to display 4 rows showing the same data layout as the top-level report but for the 4 wks involved as follows:

cashier name - 2015/07/04 - measure 1 - measure 2- ...
- 2015/06/27 - measure 1 - measure 2- ...
- 2015/06/20 - measure 1 - measure 2- ...
- 2015/06/13 - measure 1 - measure 2- ...

What I'm getting:

cashier name - 2015/07/04 - measure 1 - measure 2- ...

We're so close!! lol

Here's my code for the lower-level drill-down report:

-* Test report to develop lower-level summary report that displays a cashier's summarized measures seen in top-level report
-* and that cashier's summarized measures for the 3 weeks previous as well.

-SET &FOURWKSPREV = AYMD(&WEEKENDING, -28, YYMD);

DEFINE FILE SRPROD
RINGTIMEMIN/P11.2=SRPROD.SRPROD.SRPDRINGTIMESEC / 60;
TNDRTIMEMIN/P11.2=SRPROD.SRPROD.SRPDTENDERTIMES / 60;
SESSTIMEMIN/P11.2=SRPROD.SRPROD.SRPDSESSIONTIMESEC / 60;
IDLETIMEMIN/P11.2=SRPROD.SRPROD.SRPDIDLETIMESEC / 60;
LCKTIMEMIN/P11.2=SRPROD.SRPROD.SRPDLOCKTIMESEC / 60;
RTPERCUST/D12.2=RINGTIMEMIN / SRPROD.SRPROD.SRPDCUSTCNT;
RTPERITMSOLD/D12.2=RINGTIMEMIN / SRPROD.SRPROD.SRPDITEMSOLD;
TTPERCUST/D12.2=TNDRTIMEMIN / SRPROD.SRPROD.SRPDCUSTCNT;
TTPERITMSOLD/D12.2=TNDRTIMEMIN / SRPROD.SRPROD.SRPDITEMSOLD;
STPERCUST/D12.2=SESSTIMEMIN / SRPROD.SRPROD.SRPDCUSTCNT;
STPERITMSOLD/D12.2=SESSTIMEMIN / SRPROD.SRPROD.SRPDITEMSOLD;
ITPERCUST/D12.2=IDLETIMEMIN / SRPROD.SRPROD.SRPDCUSTCNT;
LTPERCUST/D12.2=LCKTIMEMIN / SRPROD.SRPROD.SRPDCUSTCNT;
END
-RUN
TABLE FILE SRPROD
SUM
     SRPROD.SRPROD.SRPDOPENDEPTCNT AS 'Open Dept Cnt'
     SRPROD.SRPROD.SRPDKEYENTRYCNT AS 'Keyed Entry Cnt'
     SRPROD.SRPROD.SRPDVOIDAMT/P10.2M AS 'Void Amt'
     SRPROD.SRPROD.SRPDNOSALECNT AS 'No Sale Cnt'
     SRPROD.SRPROD.SRPDSTORECOUPCNT AS 'Store Cpn Cnt'
     SRPROD.SRPROD.SRPDSTORECOUPAMT/P10.2M AS 'Store Cpn Amt'
     SRPROD.SRPROD.SRPDCUSTCNT AS 'Cstmr,Cnt'
     SRPROD.SRPROD.SRPDAVGCUSTPHR AS 'Avg Cstmrs,per hour'
     SRPROD.SRPROD.SRPDITEMSOLD AS 'Items,Sold'
     SRPROD.SRPROD.SRPDAVGSPHR/P10.2CM AS 'Avg Sales,per hour'
     SRPROD.SRPROD.SRPDAVGITMSPERMIN AS 'Avg Items,per min'
     SRPROD.SRPROD.SRPDAVGSCANSPERMIN AS 'Avg Scans,per min'
     SRPROD.SRPROD.RTPERCUST AS 'Ringtime,per cstmr'
     SRPROD.SRPROD.RTPERITMSOLD AS 'Ringtime,per items sold'
     SRPROD.SRPROD.TTPERCUST AS 'Tendertime,per cstmr'
     SRPROD.SRPROD.TTPERITMSOLD AS 'Tendertime,per items sold'
     SRPROD.SRPROD.STPERCUST AS 'Sessiontime,per cstmr'
     SRPROD.SRPROD.STPERITMSOLD AS 'Sessiontime,per items sold'
     SRPROD.SRPROD.ITPERCUST AS 'Idletime,per cstmr'
     SRPROD.SRPROD.LTPERCUST AS 'Locktime,per cstmr'
BY  LOWEST SRPROD.SRPROD.SRPDCASHIERNAME AS 'Cashier'
BY  SRPROD.DIMDATE.WEEKENDING AS 'Week Ending'
ON TABLE SUBHEAD
"<SRPROD.DIMSTORE.DSNAME Cashier Stats for Week Ending <SRPROD.DIMDATE.WEEKENDING "
WHERE ( SRPROD.DIMSTORE.DSNAME EQ '&STRNAME.(FIND SRPROD.DIMSTORE.DSNAME IN SRPROD).Store:.' );
WHERE ( SRPROD.DIMDATE.WEEKENDING EQ '&WEEKENDING.(FIND SRPROD.DIMDATE.WEEKENDING IN SRPROD).Week Ending:.' );
WHERE ( SRPROD.SRPROD.SRPDCASHIERNAME EQ '&SRPDCASHIERNAME.(FIND SRPROD.SRPROD.SRPDCASHIERNAME IN SRPROD).SRPDCASHIERNAME:.' );
WHERE ( SRPROD.DIMDATE.WEEKENDING GE '&FOURWKSPREV');
WHERE ( SRPROD.DIMDATE.WEEKENDING LE '&WEEKENDING');
ON TABLE SET AUTOFIT ON
ON TABLE SET PAGE-NUM NOLEAD
ON TABLE SET ASNAMES ON
ON TABLE NOTOTAL
ON TABLE PCHOLD FORMAT HTML
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *
     INCLUDE = IBFS:/EDA/EDASERVE/_EDAHOME/ETC/endeflt.sty,
$
     ORIENTATION=LANDSCAPE,
$
TYPE=REPORT,
     LINES-PER-PAGE=UNLIMITED,
$
TYPE=TABHEADING,
     SIZE=12,
     STYLE=BOLD,
     JUSTIFY=LEFT,
$
ENDSTYLE
END

Thanks for getting me further along guys!

quote:

WHERE ( SRPROD.DIMSTORE.DSNAME EQ '&STRNAME.(FIND SRPROD.DIMSTORE.DSNAME IN SRPROD).Store:.' );
WHERE ( SRPROD.DIMDATE.WEEKENDING EQ '&WEEKENDING.(FIND SRPROD.DIMDATE.WEEKENDING IN SRPROD).Week Ending:.' );
WHERE ( SRPROD.SRPROD.SRPDCASHIERNAME EQ '&SRPDCASHIERNAME.(FIND SRPROD.SRPROD.SRPDCASHIERNAME IN SRPROD).SRPDCASHIERNAME:.' );
WHERE ( SRPROD.DIMDATE.WEEKENDING GE '&FOURWKSPREV');
WHERE ( SRPROD.DIMDATE.WEEKENDING LE '&WEEKENDING');

You have conflicting where statements. Remove the WEEKENDING EQ ... and you'll be fine.

Eric,

You rock! Thank you for helping me! Both reports are now working the way I wanted them to! Appreciate it a ton!

Thanks again!

Jack,

Thank you as well!

Eric, Jack, others,

Now that I got this working, my project lead wants me to change from having just the week selected's summed measures to having the total of the 4 weeks for each cashier in the summary/top level report, and then have the drill down showing what it does: the 4 weeks' measures separately (for detail).

So, I need to figure out how to switch the 1 selected week's measures there for the total of all 4 weeks' measures.

So, the summary level report would be like:

Cashier Name - 4wk total of measure 1 - 4wk total of measure 2 - ...

Any advice on how to go about doing this?

Also, thank you so much for what you've already helped me to understand! I'm learning quite a bit from you guys! I look forward to the day when I'm as knowledgeable as you all! lol

Here's my code for my summary/top level report thus far:

-* Test report to develop top-level summary report that displays user-selected business week summary data for each
-* cashier within a selected account/store.

-* Should show the 4 week total that includes the business week selected and 3 previous weeks for each cashier of their measures shown.
-* This report will allow user to drill down by cashier and get that cashier's summarized measures seen in top-level report
-* and that cashier's summarized measures for the 3 weeks previous as well.

DEFINE FILE SRPROD
RINGTIMEMIN/P11.2=SRPROD.SRPROD.SRPDRINGTIMESEC / 60;
TNDRTIMEMIN/P11.2=SRPROD.SRPROD.SRPDTENDERTIMES / 60;
SESSTIMEMIN/P11.2=SRPROD.SRPROD.SRPDSESSIONTIMESEC / 60;
IDLETIMEMIN/P11.2=SRPROD.SRPROD.SRPDIDLETIMESEC / 60;
LCKTIMEMIN/P11.2=SRPROD.SRPROD.SRPDLOCKTIMESEC / 60;
RTPERCUST/D12.2=RINGTIMEMIN / SRPROD.SRPROD.SRPDCUSTCNT;
RTPERITMSOLD/D12.2=RINGTIMEMIN / SRPROD.SRPROD.SRPDITEMSOLD;
TTPERCUST/D12.2=TNDRTIMEMIN / SRPROD.SRPROD.SRPDCUSTCNT;
TTPERITMSOLD/D12.2=TNDRTIMEMIN / SRPROD.SRPROD.SRPDITEMSOLD;
STPERCUST/D12.2=SESSTIMEMIN / SRPROD.SRPROD.SRPDCUSTCNT;
STPERITMSOLD/D12.2=SESSTIMEMIN / SRPROD.SRPROD.SRPDITEMSOLD;
ITPERCUST/D12.2=IDLETIMEMIN / SRPROD.SRPROD.SRPDCUSTCNT;
LTPERCUST/D12.2=LCKTIMEMIN / SRPROD.SRPROD.SRPDCUSTCNT;
WKEND_MDYY/MDYY=DATECVT(SRPROD.DIMDATE.WEEKENDING, 'YYMD', 'MDYY');
END
-RUN
TABLE FILE SRPROD
SUM
     SRPROD.SRPROD.SRPDOPENDEPTCNT AS 'Open Dept Cnt'
     SRPROD.SRPROD.SRPDKEYENTRYCNT AS 'Keyed Entry Cnt'
     SRPROD.SRPROD.SRPDVOIDAMT/P10.2M AS 'Void Amt'
     SRPROD.SRPROD.SRPDNOSALECNT AS 'No Sale Cnt'
     SRPROD.SRPROD.SRPDSTORECOUPCNT AS 'Store Cpn Cnt'
     SRPROD.SRPROD.SRPDSTORECOUPAMT/P10.2M AS 'Store Cpn Amt'
BY  LOWEST SRPROD.DIMDATE.WEEKENDING NOPRINT
BY  LOWEST SRPROD.SRPROD.SRPDCASHIERNAME AS 'Cashier'
ON TABLE SUBHEAD
"<SRPROD.DIMSTORE.DSNAME Cashier Stats for Week Ending <SRPROD.DIMDATE.WKEND_MDYY "
WHERE ( SRPROD.DIMSTORE.DSNAME EQ '&STRNAME.(FIND SRPROD.DIMSTORE.DSNAME IN SRPROD).Store:.' ) AND ( SRPROD.DIMDATE.WEEKENDING EQ '&WEEKENDING.(FIND SRPROD.DIMDATE.WEEKENDING IN SRPROD).Week Ending:.' );
ON TABLE SET AUTOFIT ON
ON TABLE SET PAGE-NUM NOLEAD
ON TABLE SET ASNAMES ON
ON TABLE NOTOTAL
ON TABLE PCHOLD FORMAT HTML
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *
     INCLUDE = IBFS:/EDA/EDASERVE/_EDAHOME/ETC/endeflt.sty,
$
     ORIENTATION=LANDSCAPE,
$
TYPE=REPORT,
     LINES-PER-PAGE=UNLIMITED,
$
TYPE=DATA,
     FOCEXEC=IBFS:/WFC/Repository/retail_reporting/retail_reports/Productivity/Cashier_Trending_Drilldown.fex( \
     SRPDCASHIERNAME=N2 \
     STRNAME=&STRNAME.QUOTEDSTRING \
     WEEKENDING=&WEEKENDING.QUOTEDSTRING \
     ),
          TARGET='_self',
$
TYPE=TABHEADING,
     SIZE=14,
     STYLE=BOLD,
     JUSTIFY=LEFT,
$
TYPE=TABHEADING,
     LINE=1,
     OBJECT=TEXT,
     ITEM=1,
     SIZE=12,
$
TYPE=TABHEADING,
     LINE=1,
     OBJECT=TEXT,
     ITEM=2,
     SIZE=12,
$
TYPE=TABHEADING,
     LINE=1,
     OBJECT=FIELD,
     ITEM=1,
     SIZE=12,
$
TYPE=TABHEADING,
     LINE=1,
     OBJECT=FIELD,
     ITEM=2,
     SIZE=12,
$
ENDSTYLE
END

This message has been edited. Last edited by: CoolGuy,

Hey CoolGuy,

If you think about this then you'll realise that you do not want it aggregated by Weekending, so remove -

BY LOWEST SRPROD.DIMDATE.WEEKENDING NOPRINT

You'll also want to extend your selection test to include the other 3 weeks that you want.

Try this -

-SET &ECHO = ALL;

-PROMPT '&WEEKENDING.(FIND DATE IN GGSALES).Week Ending:.'
-SET &WEEKBEGINING = DATECVT(DATEADD(DATECVT(&WEEKENDING, 'I8YYMD', 'YYMD'), 'M', -3), 'YYMD', 'I8YYMD');

-? &WEEK

T

Tony,

Thank you for your insight on not needing to sort by WEEKENDING. It's true that I will already get the same results without having that field in play.

In regards to how to extend my selection test for the additional weeks I need included for display, I need the previous 3 weeks included with the selected week. Not the previous 3 months. Also, WEEKENDING is already in YYMD format, so the conversion is unneeded. But thank you for your help in knowing how to get a new weekending value by utilizing DATEADD().

Everyone,

For my -SET to get a week ending date, I've used:

-SET &THREEWKSPREV = AYMD(&WEEKENDING.QUOTEDSTRING, -21, YYMD);

This seems to work to get said value.

I inserted:

WHERE ( SRPROD.DIMDATE.WEEKENDING GE '&THREEWKSPREV');
WHERE ( SRPROD.DIMDATE.WEEKENDING LE '&WEEKENDING');

under:

WHERE ( SRPROD.DIMSTORE.DSNAME EQ '&STRNAME.(FIND SRPROD.DIMSTORE.DSNAME IN SRPROD).Store:.' );

and now get the 4 week running total that I was looking for.

I want to add the value of &THREEWKSPREV to my header but WF says I exceed the header length allowed, etc. if I try to do such. I tried defining the value as an additional field, but I don't get any values back.

-SET &THREEWKSPREV = AYMD(&WEEKENDING.QUOTEDSTRING, -21, YYMD);

OR

-*THREEWKSPREV/YYMD=AYMD(SRPROD.DIMDATE.WEEKENDING, -21, 'YYMD');

do not want to either let me display the week end date 3 wks previous or give me any values back to display.

Any additional insights/help will be greatly appreciated.

Thanks!

This message has been edited. Last edited by: CoolGuy,

quote:

-SET &THREEWKSPREV = AYMD(&WEEKENDING.QUOTEDSTRING, -21, YYMD);

Read the verschlugener manual!

- - - - -

How to Add or Subtract Days to or From a Date
AYMD(indate, days, output)
where:

indate
I6, I6YMD, I8, or I8YYMD
Is the legacy date in year-month-day format. If the date is not valid, the function returns the
value 0 (zero).

days
Integer
Is the number of days you are adding to or subtracting from indate. To subtract days, use a
negative number.

output
I6, I6YMD, I8, I8YYMD
Is the name of the field that contains the result, or the format of the output value enclosed
in single quotation marks. ...

- - - - -

So, in the Dialog Manager -SET, the final parameter should specify an appropriate integer format -- as Eric and I posted -- not a date format . Thus:

-SET &THREEWKSPREV = AYMD(&WEEKENDING.QUOTEDSTRING, -21, 'I8');

Jack,

I have read the manual. The way I've set up my AYMD() does work with the output designated as 'YYMD'.

-DEFAULTH &WEEKENDING = '20150620';
-SET &THREEWKSPREV = AYMD(&WEEKENDING.QUOTEDSTRING, -21, 'YYMD');
-TYPE &THREEWKSPREV

Result/output:
20150530

It is when I try to reference the value of &THREEWKSPREV as <&THREEWKSPREV in my report header that I get the following error:

(FOC021) THE MAXIMUM HEADING/FOOTING SIZE HAS BEEN EXCEEDED
(FOC009) INCOMPLETE REQUEST STATEMENT
BYPASSING TO END OF COMMAND

The trouble with many of us programmers/developers is that sometimes we take something another tells us far too literally (i.e. online docs), and then in turn jump to conclusions that negatively effect others. A lot of the time it is because the ones giving us information aren't as thorough as they ought to be (IBI). The "output" parameter does accept 'YYMD' as valid. The above error takes place no matter if the "output" parameter is YYMD or I8...

Thank you anyway for what help you've been.

quote:

The "output" parameter does accept 'YYMD' as valid.

in today's version, but not necessarily tomorrow's.

Anyway,

<&WEEKENDING

-- which boils down to, say,

<20150706

will be interpreted (in a HEADING) as a spot-marker, tabbing to position 2,0150,706 --
so the FOC021 is quite appropriate.

Drop the <

CoolGuy,

A couple things to remember for next time. The < symbol is only for fields that are a part of the report or, as Jack mentions for tabbing to a position. Whenever using variables, just leave the variable as it is. If anything use .EVAL to evaluate it first. Otherwise, leave it alone once in the heading. The other thing is, make sure that you are running your echo and looking at what is being passed. If you run the echo on your report, you should see pretty quickly that what is being parsed is

<20150706 instead of <&WEEKENDING 

This should be a pretty quick indicator for you that things aren't going as you expected.

As for the AYMD() Function. Again Jack is right that you should be declaring it as an I8YYMD. The dialogue manager has no concept of Smart Dates. That really only exists with in the Table File Request. This might be working now, but as Jack pointed out, code tightening happens every release, and that is precisely the type of sloppy coding that tends to get tightened. I would correct it now instead of having to go through the pain of finding every where you did that the next time you upgrade.

Jack,

True that.

Thank you for your explanation on why the error was throwing, and what was going on with the "<" character. I did as you directed and did get my value for &THREEWKSPREV to display finally.

My code ended up being:

-SET &THREEWKSPREV = AYMD(&WEEKENDING.QUOTEDSTRING, -21, 'YYMD');
-SET &FRMT3WKSPRV = CHGDAT('YYMD', 'MDYY', &THREEWKSPREV, 'A10');
-SET &THWKSPRV = EDIT(&FRMT3WKSPRV, '99/99/9999');

...

ON TABLE SUBHEAD
"Cashier Stats - 4 Week Running Totals"
"including Week Ending &THWKSPRV to Week Ending <SRPROD.DIMDATE.WKEND_MDYY at <SRPROD.DIMSTORE.DSNAME"

Again, thank you for your time and help. Appreciate your patience as I learn.

Now to get my drill-down report data showing in a graph representation of the same...

Eric,

Thank you also for your added insights and tips. I will remember to use ECHO to check run-time output to see what's going on. So, for my AYMD() output choice, I need to just switch it out with 'I8YMDD' and I should be good?

My new code would be?:

-SET &THREEWKSPREV = AYMD(&WEEKENDING.QUOTEDSTRING, -21, 'I8YYMD');
-SET &FRMT3WKSPRV = CHGDAT('I8YYMD', 'MDYY', &THREEWKSPREV, 'A10');
-SET &THWKSPRV = EDIT(&FRMT3WKSPRV, '99/99/9999');

And would my selection test still work?:
(*Note: &WEEKENDING will be in format YYMD.*)

WHERE ( SRPROD.DIMDATE.WEEKENDING GE '&THREEWKSPREV');
WHERE ( SRPROD.DIMDATE.WEEKENDING LE '&WEEKENDING');

*Will try it... Works.*

Cool. Thanks again Eric and Jack! Appreciate all your help and patience with me! I will increasingly be less needy and oblivious over time! I promise! lol

quote:

Thank you also for your added insights and tips. I will remember to use ECHO to check run-time output to see what's going on. So, for my AYMD() output choice, I need to just switch it out with 'I8YMDD' and I should be good?

yup, thats it. Remember at the end of the day, variables don't really have a data type to them. So the function will need to know what the datatype is "Supposed" to be, so that it knows how to handle the command.

Eric,

Thanks! I've noted such. Used to assigning a data type to variables from working in Java and C#. Slowly learning the quirks to WF and the DM languages. I guess it's kind of like Js. Dynamic typing.