Hey all,

So, I've built a report request that gets passed a week end date and store # and then should display the top 5 performers for a given metric. If I set the output to AHTML and within the report styles set LINES-PER-PAGE=5, I get what I'm looking for. But if I change the output to HTML I no longer get just the top 5 performers, but all cashiers for a given store. I thought I could set the record limit to 5 by adding WHERE RECORDLIMIT EQ 5; to my code, but for some reason when I do such I get 5 totally random other cashiers with their numbers being different from what they were previous to adding the WHERE RECORDLIMIT EQ 5 clause. How can I get what I see without the record limit set to display when I do limit the records to 5?

Currently Get:

 
cashier 1    99999
cashier 2    88888
cashier 3    77777
cashier 4    66666
cashier 5    55555
cashier 6    44444
cashier 7    33333
cashier 8    22222
cashier 9    11111
cashier 10   01111
Etc...

After adding WHERE RECORDLIMIT EQ 5;:

 
cashier 6    02365
cashier 8    01739
cashier 14   xxxxx
cashier 22   xxxxx
cashier 54   xxxxx

I get only 5 records, but not the top 5 from the report before the record limit was set; and the records I do get are off from what they were beforehand as well.

Does anyone know what I am doing wrong and how I can fix this so I get a report that shows:

 
cashier 1    99999
cashier 2    88888
cashier 3    77777
cashier 4    66666
cashier 5    55555

and not what I show from above?

My code:

-* Test report for Top/Bottom 5 Performers report.

-SET &ECHO = ALL;

-DEFAULTH &HIDDEN = 'Y';
-*-DEFAULTH &HIDDEN = 'N';
-IF &HIDDEN = 'N' THEN GOTO Wait;

-DEFAULTH &DSACCTNO = '10982';
-DEFAULTH &WEEKENDING = '20150711';

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

DEFINE FILE SRPROD
WKEND_MDYY/MDYY=DATECVT(SRPROD.DIMDATE.WEEKENDING, 'YYMD', 'MDYY');
INT_CASHIERNO/I10 = EDIT(SRPROD.SRPROD.SRPDCASHIERNO);
INT_STORENO/I5 = IF INT_CASHIERNO GT 9999 THEN INT_CASHIERNO;
STORENO/A5 = EDIT(INT_STORENO);
END
-RUN
TABLE FILE SRPROD
SUM
     SRPROD.SRPROD.SRPDCASHIERNO NOPRINT  AS 'Cashier #'
     SRPROD.SRPROD.SRPDGROSSSALES/P10.2M AS 'Overall Sales,for 4 wks'
BY TOTAL HIGHEST SRPROD.SRPROD.SRPDGROSSSALES NOPRINT
BY  SRPROD.SRPROD.SRPDCASHIERNAME AS 'Cashier'
ON TABLE SUBHEAD
"Top 5 Performers from <SRPROD.DIMSTORE.DSNAME"
"from Week Ending &THWKSPRV "
"to Week Ending <SRPROD.DIMDATE.WKEND_MDYY"
WHERE ( SRPROD.DIMSTORE.DSACCTNO EQ &DSACCTNO.(FIND SRPROD.DIMSTORE.DSACCTNO IN SRPROD).DSACCTNO:. ) AND ( SRPROD.DIMDATE.WEEKENDING EQ '&WEEKENDING.(FIND SRPROD.DIMDATE.WEEKENDING IN SRPROD).WEEKENDING:.' );
WHERE ( SRPROD.DIMDATE.WEEKENDING GE '&THREEWKSPREV');
WHERE ( SRPROD.DIMDATE.WEEKENDING LE '&WEEKENDING');
WHERE SRPDCASHIERNO NE STORENO;
WHERE SRPDCASHIERNO NE '800';
WHERE SRPDCASHIERNO NE '997';
WHERE RECORDLIMIT EQ 5;
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,
     ARGRAPHENGINE=JSCHART,
$
TYPE=TABHEADING,
     SIZE=14,
     STYLE=BOLD,
     JUSTIFY=CENTER,
$
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=TEXT,
     ITEM=3,
     SIZE=12,
$
TYPE=TABHEADING,
     LINE=1,
     OBJECT=FIELD,
     ITEM=1,
     SIZE=12,
     BACKCOLOR='WHITE',
$
TYPE=TABHEADING,
     LINE=1,
     OBJECT=FIELD,
     ITEM=2,
     SIZE=12,
$
TYPE=TABHEADING,
     LINE=1,
     OBJECT=FIELD,
     ITEM=3,
     SIZE=12,
$
TYPE=TABHEADING,
     LINE=2,
     OBJECT=TEXT,
     ITEM=1,
     SIZE=12,
$
TYPE=TABHEADING,
     LINE=2,
     OBJECT=TEXT,
     ITEM=2,
     SIZE=12,
$
TYPE=TABHEADING,
     LINE=2,
     OBJECT=FIELD,
     ITEM=1,
     SIZE=12,
$
TYPE=TABHEADING,
     LINE=2,
     OBJECT=FIELD,
     ITEM=2,
     SIZE=12,
$
TYPE=TABHEADING,
     LINE=3,
     OBJECT=TEXT,
     ITEM=1,
     SIZE=12,
$
TYPE=TABHEADING,
     LINE=3,
     OBJECT=FIELD,
     ITEM=1,
     SIZE=12,
$
TYPE=REPORT,
     OBJECT=STATUS-AREA,
     PAGE-LOCATION=OFF,
$
ENDSTYLE
END
-IF &HIDDEN EQ 'Y' THEN GOTO TheEnd;

-Wait

-HTMLFORM IBFS:/WFC/Repository/.../PlzWaitForFilters.htm

-TheEnd

Thanks in advance!

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

Perhaps BY HIGHEST n will work:

BY TOTAL HIGHEST 5 SRPROD.SRPROD.SRPDGROSSSALES NOPRINT

Remove the WHERE RECORDLIMIT

Francis,

Thank you! That worked to get the top 5 correct records I was looking for. Although, while I was troubleshooting, I discovered that for some reason the sales figures are for only the business week passed to it (via &WEEKENDING). The sales figures are missing for the previous 3 weeks before the passed business week. Do you see anywhere in my code that would be the reason for why I am only getting back just one week of sales figures?

Thanks so much for helping me figure out the limit issue though.

WHERE RECORDLIMIT EQ 5; issue solved.

Newly discovered issue:

My report is only showing 1 business week of sales data, when I need it to show the 4 week running total. The sales data for the business week passed to the request is the only sales data being shown. (via &WEEKENDING)

Can anyone here see why? I've got this same code setup working in other reports, but for some reason it isn't giving me all 4 weeks in this one.

My current (updated) code:

-* Test report for Top/Bottom 5 Performers report.

-SET &ECHO = ALL;

-DEFAULTH &HIDDEN = 'Y';
-*-DEFAULTH &HIDDEN = 'N';
-IF &HIDDEN = 'N' THEN GOTO Wait;

-DEFAULTH &DSACCTNO = '12312';
-DEFAULTH &WEEKENDING = '20150711';

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

DEFINE FILE SRPROD
WKEND_MDYY/MDYY=DATECVT(SRPROD.DIMDATE.WEEKENDING, 'YYMD', 'MDYY');
INT_CASHIERNO/I10 = EDIT(SRPROD.SRPROD.SRPDCASHIERNO);
INT_STORENO/I5 = IF INT_CASHIERNO GT 9999 THEN INT_CASHIERNO;
STORENO/A5 = EDIT(INT_STORENO);
END
-RUN
TABLE FILE SRPROD
SUM
     SRPROD.SRPROD.SRPDCASHIERNO NOPRINT  AS 'Cashier #'
     SRPROD.SRPROD.SRPDGROSSSALES/P10.2M AS 'Overall Sales,for 4 wks'
BY TOTAL HIGHEST 5 SRPROD.SRPROD.SRPDGROSSSALES NOPRINT
BY  SRPROD.SRPROD.SRPDCASHIERNAME AS 'Cashier'
ON TABLE SUBHEAD
"Top 5 Performers from <SRPROD.DIMSTORE.DSNAME"
"from Week Ending &THWKSPRV "
"to Week Ending <SRPROD.DIMDATE.WKEND_MDYY"
WHERE ( SRPROD.DIMSTORE.DSACCTNO EQ &DSACCTNO.(FIND SRPROD.DIMSTORE.DSACCTNO IN SRPROD).DSACCTNO:. ) AND ( SRPROD.DIMDATE.WEEKENDING EQ '&WEEKENDING.(FIND SRPROD.DIMDATE.WEEKENDING IN SRPROD).WEEKENDING:.' );
WHERE ( SRPROD.DIMDATE.WEEKENDING GE '&THREEWKSPREV');
WHERE ( SRPROD.DIMDATE.WEEKENDING LE '&WEEKENDING');
WHERE SRPDCASHIERNO NE STORENO;
WHERE SRPDCASHIERNO NE '800';
WHERE SRPDCASHIERNO NE '997';
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,
     ARGRAPHENGINE=JSCHART,
$
TYPE=DATA,
     COLUMN=N2,
     FOCEXEC=IBFS:/WFC/Repository/.../test_top-bottom_5_performers_drilldown.fex( \
     SRPDCASHIERNAME=N2 \
     DSACCTNO=&DSACCTNO.QUOTEDSTRING \
     WEEKENDING=&WEEKENDING.QUOTEDSTRING \
     ),
          TARGET='_self',
$
TYPE=TABHEADING,
     SIZE=14,
     STYLE=BOLD,
     JUSTIFY=CENTER,
$
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=TEXT,
     ITEM=3,
     SIZE=12,
$
TYPE=TABHEADING,
     LINE=1,
     OBJECT=FIELD,
     ITEM=1,
     SIZE=12,
     BACKCOLOR='WHITE',
$
TYPE=TABHEADING,
     LINE=1,
     OBJECT=FIELD,
     ITEM=2,
     SIZE=12,
$
TYPE=TABHEADING,
     LINE=1,
     OBJECT=FIELD,
     ITEM=3,
     SIZE=12,
$
TYPE=TABHEADING,
     LINE=2,
     OBJECT=TEXT,
     ITEM=1,
     SIZE=12,
$
TYPE=TABHEADING,
     LINE=2,
     OBJECT=TEXT,
     ITEM=2,
     SIZE=12,
$
TYPE=TABHEADING,
     LINE=2,
     OBJECT=FIELD,
     ITEM=1,
     SIZE=12,
$
TYPE=TABHEADING,
     LINE=2,
     OBJECT=FIELD,
     ITEM=2,
     SIZE=12,
$
TYPE=TABHEADING,
     LINE=3,
     OBJECT=TEXT,
     ITEM=1,
     SIZE=12,
$
TYPE=TABHEADING,
     LINE=3,
     OBJECT=FIELD,
     ITEM=1,
     SIZE=12,
$
TYPE=REPORT,
     OBJECT=STATUS-AREA,
     PAGE-LOCATION=OFF,
$
ENDSTYLE
END
-IF &HIDDEN EQ 'Y' THEN GOTO TheEnd;

-Wait

-HTMLFORM IBFS:/WFC/Repository/.../PlzWaitForFilters.htm

-TheEnd

Thanks to everyone that is willing to help with this! Really appreciate the help!

Nevermind! Figured it out. I accidentally inserted one too many WHERE clauses for &WEEKENDING. Got rid of the culprit and now am getting the correct data as well as the top 5.

Thank you Francis for your help earlier and to those that tried.

Until next time!