Hi all,

So, I've got a report request that gives me the top 5 performers in a certain metric. I want to get the values from this request's results and store them in their own &variables. I've done something similar before as follows:

TABLE FILE [filename]
SUM
CSALES PSALES
COMPUTE PCTDIFF/D12.1=((CSALES-PSALES)/PSALES)*100;
COMPUTE AMTDIFF/D15.2  =(CSALES-PSALES);
WHERE (FISCALYEAR EQ '&FISCALYEAR');
WHERE (&VAR1);
WHERE (&VAR2);
WHERE DSDIVISION EQ &DSDIVISION;
WHERE DSDISTRICT EQ &DSDISTRICT;
WHERE DSBANNERGL EQ &DSBANNERGL;
WHERE DSACCTNO   EQ &DSACCTNO;
WHERE DDDEPTCODE EQ &DDDEPTCODE;
WHERE (CSALES NE 0);
ON TABLE SAVE
END
-RUN
-READ SAVE &CSALES.I15. &PSALES.I15. &PCTDIFF.I12. &AMTDIFF.I15.

The above request stores/reads a single record of values into their own &variables for later use in a custom made HTML document embedded below the request.

The top 5 request I've created displays the top 5 cashiers by name (in A35V format) with their corresponding numeric metric (currently in P10.2M format). The trouble I've ran into is I need to store 5 records of fields instead of just one like the above example. I also am not sure how to store a variable length alpha without getting back extra character length due to the -READ statement only supporting A or I as possible storage formats if I'm not mistaken.

Has anyone here sought to do something like this before and had success?
Any help would be greatly appreciated.

Thanks in advance!

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

Try something like this (not tested) -

-SET &Cntr = 1;
-READ SAVE &CSALES&Cntr.EVAL.I15. &PSALES&Cntr.EVAL.I15. &PCTDIFF&Cntr.EVAL.I12. &AMTDIFF&Cntr.EVAL.I15.
-REPEAT :Loop WHILE &IORETURN EQ 0;
-SET &Cntr = &Cntr + 1;
-READ SAVE &CSALES&Cntr.EVAL.I15. &PSALES&Cntr.EVAL.I15. &PCTDIFF&Cntr.EVAL.I12. &AMTDIFF&Cntr.EVAL.I15.
-:Loop

T

You're talking about how to capture the 5 cashiers' IDs to use in the next report called, right?

I capture the IDs in a DEFINE statement, gathering them up as I go.

DEFINE TABLE_X
Cashier_List/A200V = LAST Cashier_List | ', ' | Cashier_ID;
END

TABLE FILE TABLE_X
.
.
.

The result is the last record having the value "000001, 000002, 000003, 000004, 000005," in it which is more or less what you want, and can use in a WHERE CASHIER_ID IN (&CASHIER_LIST) command. There's an extra comma at the end that you need to deal with but you get the idea.

In this example they're not in their own variables, but you may not need them to be. You can run a simple Hold file in the next routine if you need to (may not) and use it as the top of a join to pull the materials you need.

We use that to pass huge lengths of IDs between routines for bulk mailings.

On the target side, we'll run a simple Table File command to store all the IDs in a hold file, then loop through for each letter.

Loop:
1. -READ an ID
2. Print Letters for that ID
End Loop

Each READ starts on a new line, reuses the &ID variable for the letter writing process.

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

Tony A,

Thanks for the insights. I built something similar and have it working, but values are still wonky after getting read and stored in the &vars. See below.

John_Edwards,

Thanks for your insights as well, but I do actually need values to be stored in their own &vars. I am not actually looking for a list of IDs. I want the actual record values from the request, so I can !IBI.AMP.xxxxx reference them in my own HTML below the request enabling me to format them and make my content look good. I don't want the basic report that WebFocus produces. I want to take the values from the records, field by field, and be able to reference them where ever I need to in my HTML and Js. Thanks though.


So, here is my &ECHOed code with its resolved output thus far:

-DEFAULTH &DSACCTNO = '12312';
 -DEFAULTH &WEEKENDING = '20150725';
 -SET &THREEWKSPREV = AYMD('20150725', -21, 'YYMD');
 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.SRPDGROSSSALES
 BY TOTAL HIGHEST 5 SRPROD.SRPROD.SRPDGROSSSALES NOPRINT
 BY SRPROD.SRPROD.SRPDCASHIERNAME
 WHERE ( SRPROD.DIMSTORE.DSACCTNO EQ 12312 );
 WHERE ( SRPROD.DIMDATE.WEEKENDING GE '20150704');
 WHERE ( SRPROD.DIMDATE.WEEKENDING LE '20150725');
 WHERE SRPDCASHIERNO NE STORENO;
 WHERE SRPDCASHIERNO NE '800';
 WHERE SRPDCASHIERNO NE '997';
 ON TABLE SAVE
 END
 -RUN
 ALPHANUMERIC RECORD NAMED  SAVE
 0 FIELDNAME                         ALIAS         FORMAT        LENGTH
 SRPDCASHIERNAME                   srpdCashiern> A35V           41
 SRPDGROSSSALES                    srpdGrosssal> P10.2          10
 SRPDGROSSSALES                    srpdGrosssal> P10.2          10
 TOTAL                                                          61
 1
 0 NUMBER OF RECORDS IN TABLE=       48  LINES=      5
 -SET &I = 0;
 -LOOP
 -SET &I = 0 + 1;
 -IF 1 GT 5 GOTO OUT;
 -READ SAVE &SRPDGROSSSALES_NOPRINT1.I10. &SRPDCASHIERNAME1.A35. &SRPDGROSSSALES1.I10.
 -SET &CNAME1 = TRUNCATE(n                                32);
 -TYPE 000005Robi n                                32 378.37  32
 000005Robi n                                32 378.37  32
 -GOTO LOOP
 -LOOP
 -SET &I = 1 + 1;
 -IF 2 GT 5 GOTO OUT;
 -READ SAVE &SRPDGROSSSALES_NOPRINT2.I10. &SRPDCASHIERNAME2.A35. &SRPDGROSSSALES2.I10.
 -SET &CNAME2 = TRUNCATE(y                                15);
 -TYPE 000005Holl y                                15 949.47  15
 000005Holl y                                15 949.47  15
 -GOTO LOOP
 -LOOP
 -SET &I = 2 + 1;
 -IF 3 GT 5 GOTO OUT;
 -READ SAVE &SRPDGROSSSALES_NOPRINT3.I10. &SRPDCASHIERNAME3.A35. &SRPDGROSSSALES3.I10.
 -SET &CNAME3 = TRUNCATE(a                                12);
 -TYPE 000005Dayn a                                12 719.50  12
 000005Dayn a                                12 719.50  12
 -GOTO LOOP
 -LOOP
 -SET &I = 3 + 1;
 -IF 4 GT 5 GOTO OUT;
 -READ SAVE &SRPDGROSSSALES_NOPRINT4.I10. &SRPDCASHIERNAME4.A35. &SRPDGROSSSALES4.I10.
 -SET &CNAME4 = TRUNCATE(i                                 8);
 -TYPE 000005Trud i                                 8 090.88   8
 000005Trud i                                 8 090.88   8
 -GOTO LOOP
 -LOOP
 -SET &I = 4 + 1;
 -IF 5 GT 5 GOTO OUT;
 -READ SAVE &SRPDGROSSSALES_NOPRINT5.I10. &SRPDCASHIERNAME5.A35. &SRPDGROSSSALES5.I10.
 -SET &CNAME5 = TRUNCATE(                                  7);
 -TYPE 000004Kyle                                   7 407.99   7
 000004Kyle                                   7 407.99   7
 -GOTO LOOP
 -LOOP
 -SET &I = 5 + 1;
 -IF 6 GT 5 GOTO OUT;
 -OUT

For some reason I'm getting goofball '000005' numbers in front of my cashier names in my output, spaces between #s in the 2nd field values displayed, and the last field values are being cut off. I checked sample data to make sure those didn't actually exist in the table and they don't. The table data has Robin, July, Holly, Gertie, and Trudi (in format A35V). How do I get rid of these extra random numbers that suddenly decided to appear in front of my values? Also, my gross sales values have a bunch of white space in front of the #s. How do I get rid of those? Am I reading the fields wrong in some way? Should I be reading the NOPRINT field first, then the cashiername field, and finally the gross sales field? I tried and I still get goofy output.

I really wish they had better docs on the limitations and nuances of reading records from requests and storing them in DM &vars. I spent an hour or so looking at what was available and it was sparse. Nothing on whether things should be read in a sequential order or not, or whether you should read the NOPRINT fields into variables or not.

Anyone know more on any of these things? Thanks so much for what help you've been thus far!

Upon adding some -TYPE lines to see what the values were below the rest of the code, I found that for some reason values that ought to be in other variables are bleeding into other variables.... Freaking trippy! Like the 'n' in 'Robin' is in the corresponding &CNAME.&I variable, while the rest of 'Robin' is in the &SRPDGROSSSALES_NOPRINT.&I variable along with the extra #s before it. And in the &SRPDGROSSSALES.&I variable it's got the latter part of what's supposed to be in it but some of it is cut off... All values in newly created &vars are for some reason shifted... WHAT IS GOING ON?! lol

Output of -TYPEs written after the loop from above:

-TYPE 000005Robi 000005Holl 000005Dayn 000005Trud 000004Kyle
 000005Robi 000005Holl 000005Dayn 000005Trud 000004Kyle
 -TYPE n                                32 y                                15 a                                12 i                                 8                                   7
 n                                32 y                                15 a                                12 i                                 8                                   7
 -TYPE 378.37  32 949.47  15 719.50  12 090.88   8 407.99   7
 378.37  32 949.47  15 719.50  12 090.88   8 407.99   7

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

'000005' is the length of the field that follows, something you can use in your READ work if you need to in order to read the correct length.

It's the result of creating a hold file with an A35V field format instead of an A35. If you change the format of your saving field to an A35 that number will go away.

For Hold files, you want to include the line ON TABLE SET HOLDLIST PRINTONLY in order to suppress the NOPRINT fields. ON TABLE SET ASNAMES ON is a good idea as well, although that doesn't seem to be a concern in your case.

Also ON TABLE SAVE FORMAT ALPHA isn't a bad idea either to make sure you get true text, since that's what you need for reading into amper variables.

As for documentation on this stuff, we're it. Come straight to the forums and do a search. Best way to figure out what you need to do.

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

John,

Thanks for the tip! After modifying the field in the request to the format A35 instead, rearranging -READ &vars, and adding the SET commands you suggested, the echoed output is now thus:

-DEFAULTH &DSACCTNO = '12312';
 -DEFAULTH &WEEKENDING = '20150725';
 -SET &THREEWKSPREV = AYMD('20150725', -21, 'YYMD');
 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.SRPDGROSSSALES
 BY TOTAL HIGHEST 5 SRPROD.SRPROD.SRPDGROSSSALES NOPRINT
 BY SRPROD.SRPROD.SRPDCASHIERNAME/A35
 WHERE ( SRPROD.DIMSTORE.DSACCTNO EQ 12312 );
 WHERE ( SRPROD.DIMDATE.WEEKENDING GE '20150704');
 WHERE ( SRPROD.DIMDATE.WEEKENDING LE '20150725');
 WHERE SRPDCASHIERNO NE STORENO;
 WHERE SRPDCASHIERNO NE '800';
 WHERE SRPDCASHIERNO NE '997';
 ON TABLE SET HOLDLIST PRINTONLY
 ON TABLE SET ASNAMES ON
 ON TABLE SAVE FORMAT ALPHA
 END
 -RUN
 ALPHANUMERIC RECORD NAMED  SAVE
 0 FIELDNAME                         ALIAS         FORMAT        LENGTH
 KEY$$$RF01                                      A35            35
 SRPDGROSSSALES                    srpdGrosssal> P10.2          10
 TOTAL                                                          45
 1
 0 NUMBER OF RECORDS IN TABLE=       87  LINES=      5
 -SET &I = 0;
 -LOOP
 -SET &I = 0 + 1;
 -IF 1 GT 5 GOTO OUT;
 -READ SAVE &SRPDCASHIERNAME1.A35. &SRPDGROSSSALES1.I10.
 -SET &CNAME1 = TRUNCATE(Robin                              );
 -TYPE Robin   44537.73
 Robin   44537.73
 -GOTO LOOP
 -LOOP
 -SET &I = 1 + 1;
 -IF 2 GT 5 GOTO OUT;
 -READ SAVE &SRPDCASHIERNAME2.A35. &SRPDGROSSSALES2.I10.
 -SET &CNAME2 = TRUNCATE(Holly                              );
 -TYPE Holly   25307.71
 Holly   25307.71
 -GOTO LOOP
 -LOOP
 -SET &I = 2 + 1;
 -IF 3 GT 5 GOTO OUT;
 -READ SAVE &SRPDCASHIERNAME3.A35. &SRPDGROSSSALES3.I10.
 -SET &CNAME3 = TRUNCATE(Kyle                               );
 -TYPE Kyle   15022.35
 Kyle   15022.35
 -GOTO LOOP
 -LOOP
 -SET &I = 3 + 1;
 -IF 4 GT 5 GOTO OUT;
 -READ SAVE &SRPDCASHIERNAME4.A35. &SRPDGROSSSALES4.I10.
 -SET &CNAME4 = TRUNCATE(Dayna                              );
 -TYPE Dayna   14826.77
 Dayna   14826.77
 -GOTO LOOP
 -LOOP
 -SET &I = 4 + 1;
 -IF 5 GT 5 GOTO OUT;
 -READ SAVE &SRPDCASHIERNAME5.A35. &SRPDGROSSSALES5.I10.
 -SET &CNAME5 = TRUNCATE(Victoria                           );
 -TYPE Victoria   13858.16
 Victoria   13858.16
 -GOTO LOOP
 -LOOP
 -SET &I = 5 + 1;
 -IF 6 GT 5 GOTO OUT;
 -OUT
 -TYPE Robin Holly Kyle Dayna Victoria
 Robin Holly Kyle Dayna Victoria
 -TYPE   44537.73   25307.71   15022.35   14826.77   13858.16
 44537.73   25307.71   15022.35   14826.77   13858.16

Do you or anyone know why the hold file read the srpdcashiername field before the NOPRINT field? I thought -READ did things sequentially. ???

Oh! Thanks for the edited comments above John! Updated my output above to show the aftemath. Now it's even better! Thanks!

Thanks a lot John, and you too Tony!!

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

We can't see your TYPE statement, so it's difficult to tell what you're asking for in what order.

Add those two SET lines to your request, and make it format alpha.

Also wouldn't hurt for you to query your hold file's metadata to see what fields it's holding, and what order it's holding them in.

John,

I've made the changes and updated my output in the post above.

My -TYPE statements read as follows:

-TYPE &CNAME1 &CNAME2 &CNAME3 &CNAME4 &CNAME5
-TYPE &SRPDGROSSSALES1 &SRPDGROSSSALES2 &SRPDGROSSSALES3 &SRPDGROSSSALES4 &SRPDGROSSSALES5

I'm kind of new to WebFocus, so how would I "query your hold file's metadata to see what fields it's holding"? Thanks for your help and patience. Also, for some reason, the auto-prompt facility launches asking for values for the &vars I reference in my -TYPE statements. Weird. I just type anything into the boxes for values and then it runs fine giving me correct values instead. I know I can just -DEFAULTH them though.

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

This page -- http://www.informationbuilders...etter/9-1/01_trommer

. . . talks about the auto-prompting setting in WebFOCUS. You can set defaults with a -DEFAULT command, which is a good general practice because it alerts you to uninitialized variables.

You're asking for an I10 for you Gross Sales numbers on the -READ, but pulling a dollars and cents value. Since this is dialogue manager commands and amper-vars none of that matters -- everything is text in amper variables. The length is the only thing that matters in this case. That said, I'd make it a text value so it isn't misleading. Or make it a P10.2 maybe, though I've never tried that.

quote:

You're asking for an I10 for you Gross Sales numbers on the -READ, ....... Or make it a P10.2 maybe

Just to remind that in a -READ, only alpha or integer formats are acceptable. Even though the raw data is a P10.2 you will still get the correct format - as seen within CGs -TYPE statements.

T

Thanks for the clarification. I've always used text fields for everything.

John,

Thanks for the link. I do understand DEFAULT and DEFAULTH, but do appreciate you bringing them up. -READ, as Tony stated, only allows for the dev to store the values in either Axx or Ixx formats, which is okay because my whole goal was to get the correct raw values from the request so I could manipulate them later in my Js below the request and DM constructs. I then format them and add them to my custom HTML found within -HTML BEGIN and -HTML END commands.

Examples of functions I've created for such formatting are:

function condFrmt() {
	if (!IBI.AMP.PCTDIFF; > 0) {
		document.getElementById("num1").style.color='#00FD00';
	}
	else if (!IBI.AMP.PCTDIFF; < 0) {
		document.getElementById("num1").style.color='#FF0000';
	}
	else {
		document.getElementById("num1").style.color='#FFFFFF';
	}
}

-* yellow hex: #FDFC00

function formatNum(num) {
	var suffixK = "K";
	var suffixM = "M";
	if ((num > 999 && num < 1000000)||(num < -999 && num > -1000000)) {
		num = num / 1000;
		num = num.toFixed(1).concat(suffixK);
		return "$" + num;
	}
	else if ((num > 999999)||(num < -999999)){
		num = num / 1000000;
		num = num.toFixed(1).concat(suffixM);
		return "$" + num;
	}
	else {
		return "$" + num.toString().replace(/\B(?=(\d{3})+(?!\d))/g, ",");
	}
}

This way I can retain full control of my HTML and not have to bother with IBI's hit and miss HTML Composer as much.