Using InfoAssist 8202M. I would like to display a dynamic p...

[Rob Fosnaugh]

At times I have used an IF in the define statement to pass through a value or assign it as FOC_NULL or MISSING as required by what you are doing.

8.2.03

[Manoj Chaurasia]

I suspect the value of &DESTINDEX has a single quote mark in it You can put

-TYPE &DESTINDEX

-EXIT

at the beginning of the fex and see what the value being passed in is

[Debra Waybright]

I think you need to add QUOTEDSTRING at the end of your WHERE:

 

 

 

todd.van.valkenburg:

 

WHERE J001.DESTINDEX_HOLD.DESTINDEX EQ &DESTINDEX.(OR(FIND ACMAL/ACMAL_HOLD/DESTINDEX_HOLD.DESTINDEX_HOLD.DESTINDEX IN ACMAL/ACMAL_HOLD/DESTINDEX_HOLD |FORMAT=A15V)).Destination Index:.QUOTEDSTRING;

[Todd Van Valkenburg]

Thanks for the suggestions. Still trying.

I added -TYPE &DESTINDEX at the beginning of the fex. I ran the fex and chose a couple of values. The output is:

D98024 OR D98026

So both values have single quotes around it. So my next thought was to use REPLACE to replace single quotes with null. So something like this:

DESTINDEX_PROMPT/A1000V=REPLACE(&DESTINDEX,,) ;

Again, not sure the proper syntax to replace a single quote since the above is one of many attempts at guessing. I did not see an example in the Function manual to replace a single quote.

Todd

[Manoj Chaurasia]

The values look fine based on your -TYPE. Did you try Debs suggestion of the QUOTEDSTRING

[Todd Van Valkenburg]

Yes, I tried the QUOTEDSTRING but I still do not have the right combination.

So I tried:

DEFINE FILE acmal/acmal_hold/allscopes_usage

START_DATE_PROMPT/MDYY='&START_DATE_PROMPT' ;

END_DATE_PROMPT/MDYY='&END_DATE_PROMPT' ;

DESTINDEX_PROMPT/A1000V='&DESTINDEX';

END

 

WHERE J001.DESTINDEX_HOLD.DESTINDEX EQ &DESTINDEX.(OR(FIND ACMAL/ACMAL_HOLD/DESTINDEX_HOLD.DESTINDEX_HOLD.DESTINDEX IN ACMAL/ACMAL_HOLD/DESTINDEX_HOLD |FORMAT=A1000V)).Destination Index:.QUOTEDSTRING;

 

Error is:

Detail:

ERROR AT OR NEAR LINE 14 IN PROCEDURE ADHOCRQ FOCEXEC *

(FOC230) MISSING ENDING QUOTE MARK: ;

END

ERROR AT OR NEAR LINE 15 IN PROCEDURE ADHOCRQ FOCEXEC *

(FOC1822) WARNING. INVALID SYMBOL:

(FOC32498) FOCEXEC PARSER ERROR. INVALID SYMBOL: DESTINDEX_PROMPT

ERROR AT OR NEAR LINE 41 IN PROCEDURE ADHOCRQ FOCEXEC *

(FOC015) THE TEST VALUE IS LONGER THAN THE FIELD FORMAT LENGTH: D98024 OR D98026

ERROR AT OR NEAR LINE 44 IN PROCEDURE ADHOCRQ FOCEXEC *

(FOC406) THE FIELDNAME IS NOT RECOGNIZED: START_DATE_PROMPT

(FOC009) INCOMPLETE REQUEST STATEMENT

BYPASSING TO END OF COMMAND

[Manoj Chaurasia]

Try this abbreviated WHERE

WHERE J001.DESTINDEX_HOLD.DESTINDEX EQ &DESTINDEX.QUOTEDSTRING

[Todd Van Valkenburg]

Using InfoAssist 8202M. I would like to display a dynamic parameter list in the report header that may have none or many values.

Relevant code to follow:

 

DEFINE FILE acmal/acmal_hold/allscopes_usage

START_DATE_PROMPT/MDYY=&START_DATE_PROMPT ;

END_DATE_PROMPT/MDYY=&END_DATE_PROMPT ;

DESTINDEX_PROMPT/A1000V=&DESTINDEX ;

END

WHERE J001.DESTINDEX_HOLD.DESTINDEX EQ &DESTINDEX.(OR(FIND ACMAL/ACMAL_HOLD/DESTINDEX_HOLD.DESTINDEX_HOLD.DESTINDEX IN ACMAL/ACMAL_HOLD/DESTINDEX_HOLD |FORMAT=A15V)).Destination Index:.;

ON TABLE SUBHEAD

ACMAL Usage Report

"<START_DATE_PROMPT<+0> - <END_DATE_PROMPT<+0> "

"<DESTINDEX_PROMPT "

 

Currently, I am getting this error:

 

ERROR AT OR NEAR LINE 9 IN PROCEDURE ADHOCRQ FOCEXEC

(FOC230) MISSING ENDING QUOTE MARK: ;

END

ERROR AT OR NEAR LINE 10 IN PROCEDURE ADHOCRQ FOCEXEC

(FOC1822) WARNING. INVALID SYMBOL:

(FOC32498) FOCEXEC PARSER ERROR. INVALID SYMBOL: DESTINDEX_PROMPT

ERROR AT OR NEAR LINE 39 IN PROCEDURE ADHOCRQ FOCEXEC

 

If I remove the define for DESTINDEX_PROMPT and remove the reference to this var in the report header, the report runs successfully. Must be missing something simple that I am missing with the define.

Thanks!

Todd

[Todd Van Valkenburg]

Chuck,

This

WHERE J001.DESTINDEX_HOLD.DESTINDEX EQ &DESTINDEX.QUOTEDSTRING

Works if you only select one value. But again, I would like the user to be able to select none-many values and have all values displayed in the report header.

Todd

[NYCBabak .]

-SET &ECHO=ALL;

 

-SET &PARM_1 = STRREP(&COUNTRY.LENGTH,&COUNTRY,4 , OR ,1,,,&COUNTRY.LENGTH,A&COUNTRY.LENGTH);

-SET &PARM_2 = IF &PARM_1.LENGTH EQ 3 THEN LJUST(2,CTRAN(&PARM_1.LENGTH,&PARM_1,39,32,A2),A1) ELSE STRREP(&PARM_1.LENGTH,&PARM_1,1,,0,,&PARM_1.LENGTH,A&PARM_1.LENGTH);

TABLE FILE CAR

SUM CAR.BODY.DEALER_COST

BY CAR.ORIGIN.COUNTRY

BY CAR.COMP.CAR

WHERE CAR.ORIGIN.COUNTRY EQ &COUNTRY.(OR(FIND CAR.ORIGIN.COUNTRY IN CAR )).COUNTRY:.;

ON TABLE SUBHEAD

&PARM_2

ON TABLE PCHOLD FORMAT HTML

ON TABLE SET STYLE *

$

ENDSTYLE

END

-RUN

[Manoj Chaurasia]

I understand. I have played with this on my machine with the wf retail data and your original syntax is exactly what works with that data. I really dont know why you are getting this error. Maybe someone else can see the issue but it is beyond me for now. You may need to open a case with techsupport

[NYCBabak .]

The code I posted, will allow multi-select and strip out the quotes and the OR so you can embed it in the heading.

[Todd Van Valkenburg]

Thank you. I have not had time to try this. I will need to research the STRREP function to see what that does. I was hoping the REPLACE would work but could not figure out how to escape a single quote.

[NYCBabak .]

The STRREP replaces all instances of a specified string within a source string. You could probably accomplish this with REPLACE as well which is a newer simplified function but I keep this code handy and have used it for a long time so I havent had a chance to rewrite it using REPLACE.

[NYCBabak .]

Heres the same effect using REPLACE.

-SET &ECHO=ALL;

 

-SET &REP_PARM_1= REPLACE(&COUNTRY, OR , ,);

-SET &REP_PARM_2=REPLACE(&REP_PARM_1, , ');

TABLE FILE CAR

SUM CAR.BODY.DEALER_COST

BY CAR.ORIGIN.COUNTRY

BY CAR.COMP.CAR

WHERE CAR.ORIGIN.COUNTRY EQ &COUNTRY.(OR(FIND CAR.ORIGIN.COUNTRY IN CAR )).COUNTRY:.;

ON TABLE SUBHEAD

&REP_PARM_2

ON TABLE PCHOLD FORMAT HTML

ON TABLE SET STYLE *

$

ENDSTYLE

END

-RUN

Note the code is being displayed with inverted marks. You have to replace them with the simple single and double quotes so the code works. I cant figure out how to stop this from happening. Heres an image of it.

image.png1077451 35.6 KB

[Todd Van Valkenburg]

Thank you. That worked. The only downside to this approach, due to the -SET, is that once it is saved it cannot be opened in InfoAssist. In this case, for this report, that is ok. Interesting to me that an identical DEFINE with the REPLACE does not work.

So to get it to work, I used:

-SET &DESTINDEX_PROMPT=REPLACE(&DESTINDEX,'''',' ');

 

ACMAL/ACMAL_HOLD/DESTINDEX_HOLD.DESTINDEX_HOLD.DESTINDEX IN ACMAL/ACMAL_HOLD/DESTINDEX_HOLD |FORMAT=A1000V)).Destination Index:.;

 

ON TABLE SUBHEAD

"ACMAL Usage Report"

"<START_DATE_PROMPT<+0> - <END_DATE_PROMPT<+0>"

"&DESTINDEX_PROMPT<+0>"

[NYCBabak .]

DEFINE FILE CAR

REP_PARM_1/A200=IF &COUNTRY.QUOTEDSTRING EQ _FOC_NULL THEN ALL COUNTRIES ELSE REPLACE(&COUNTRY.QUOTEDSTRING, ,);

REP_PARM_2/A200=REPLACE(REP_PARM_1, OR ', ', ');

END

TABLE FILE CAR

SUM CAR.BODY.DEALER_COST

BY CAR.ORIGIN.COUNTRY

BY CAR.COMP.CAR

WHERE CAR.ORIGIN.COUNTRY EQ &COUNTRY.(OR(FIND CAR.ORIGIN.COUNTRY IN CAR )).COUNTRY:.;

ON TABLE SUBHEAD

<REP_PARM_2

ON TABLE PCHOLD FORMAT HTML

ON TABLE SET STYLE *

$

ENDSTYLE

END

-RUN

 

image.png1026330 12 KB

[Todd Van Valkenburg]

Perfect! So the .QUOTEDSTRING is key and I needed it in the DEFINE.

This is my understanding:

So when I ran the report with three values, for example, and -TYPE the &DESTINDEX, I saw this:

D98122 OR D98123 OR D98133

Adding the QUOTEDSTRING in the DEFINE added two single quotes before/after each value.

So the REPLACE replaced two single quotes with one space. This results in a string with a beginning and an ending quote.

Thank you.

[David Briars]

Steve Hodos, in a recent post, shared new 82 attributes that will give syntax components of a report.

Sounds like you need to show the filters of a report request

Then maybe try <REQUEST.FILTERS

 

image.png1086370 35.3 KB

 

I havent used REQUEST variables, yet myself; they do look interesting.

[Todd Van Valkenburg]

Hi David,

Good to know. Thanks! Never heard of it but that is nothing new since this is all new to me I just tried it and REQUEST.FILTERS displays all filters in FOCUS code. So I can see that this may be good for debugging purposes especially if you have lots of WHERE statements.

Todd

[David Briars]

Oh, no problem Todd. REQUEST.FILTERS is new to me too. One of the nice things about WebFOCUS is the slew of new features/improvements they pack into the product in every release.

[Jan Vostr]

Hi David,

thank you very much for this! Any solution like this for 8105M Im just struggling with printing used parameters/filters in the deferred Excel so Im seeking for some options.

Thank you