Focal Point
[SOLVED] RECORDLIMIT...only displaying 1 biz week of sales vs 4 wks of sales:

This topic can be found at:
https://forums.informationbuilders.com/eve/forums/a/tpc/f/7971057331/m/4557073776

July 21, 2015, 04:21 PM
CoolGuy
[SOLVED] RECORDLIMIT...only displaying 1 biz week of sales vs 4 wks of sales:
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,


8.2.02M (production), 8.2.02M (test), Windows 10, all outputs.
July 21, 2015, 04:37 PM
Francis Mariani
Perhaps BY HIGHEST n will work:
BY TOTAL HIGHEST 5 SRPROD.SRPROD.SRPDGROSSSALES NOPRINT

Remove the WHERE RECORDLIMIT


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
July 21, 2015, 05:02 PM
CoolGuy
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.


8.2.02M (production), 8.2.02M (test), Windows 10, all outputs.
July 21, 2015, 05:29 PM
CoolGuy
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!


8.2.02M (production), 8.2.02M (test), Windows 10, all outputs.
July 21, 2015, 05:59 PM
CoolGuy
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!


8.2.02M (production), 8.2.02M (test), Windows 10, all outputs.