I have a report requirment that will allow the user to key in company, customer and customer name and all of them are not required field. When the user selected a company then all customers under the selected company will be included in the report and when the user select or key in a customer number then that particular customer will be in the report and when the use keys in a part of the customer name(ex. WEB) then all customer with "WEB" in the customer name will be included in the report.
I tried the solution below that I found here in focal point but it doesnt work.
In addition to what Prarie said, what do you get when you -TYPE your variables after using the launch page to run your fex?
For the partial search, you are going to have to append a wildcard to the string selected by the user in order to find everything that starts with WEB.
Are you wanting to allow the user to limit the output by choosing all some or none of the selection criteria? If so, here is one way I do it.
-SET &COUNTRY = 'ALL';
-* ALL
-* ENGLAND
-* FRANCE
-* ITALY
-* JAPAN
-* W GERMANY
-SET &CAR = 'ALL';
-* ALL
-* ALFA ROMEO
-* AUDI
-* BMW
-* DATSUN
-* JAGUAR
-* JENSEN
-* MASERATI
-* PEUGEOT
-* TOYOTA
-* TRIUMPH
-SET &BODYTYPE = 'CONVERTIBLE';
-* ALL
-* CONVERTIBLE
-* COUPE
-* HARDTOP
-* ROADSTER
-* SEDAN
-SET &COST = 10000;
-* 0 => ALL
-* VARIABLE
-*
-SET &CNTRY = IF &COUNTRY = ALL THEN 'A'' OR ''E'' OR ''I'' OR ''O'' OR ''U' ELSE &COUNTRY;
-SET &CCAR = IF &CAR = ALL THEN 'A'' OR ''E'' OR ''I'' OR ''O'' OR ''U' ELSE &CAR;
-SET &CBODYTYPE = IF &BODYTYPE = ALL THEN 'A'' OR ''E'' OR ''I'' OR ''O'' OR ''U' ELSE &BODYTYPE;
-SET &CCOST = IF &COST = 0 THEN 999999999 ELSE &COST;
-*
SET BYDISPLAY = ON
-*
TABLE FILE CAR
PRINT RETAIL_COST
BY COUNTRY
BY CAR
BY BODYTYPE
WHERE COUNTRY CONTAINS '&CNTRY'
WHERE CAR CONTAINS '&CCAR'
WHERE BODYTYPE CONTAINS '&CBODYTYPE'
WHERE RETAIL_COST LE &CCOST
END
Glenda
In FOCUS Since 1990 Production 8.2 Windows
April 25, 2008, 12:43 PM
Tom Flynn
quote:
I have a report requirment that will allow the user to key in company, customer and customer name and all of them are not required field. When the user selected a company then all customers under the selected company will be included in the report and when the user select or key in a customer number then that particular customer will be in the report and when the use keys in a part of the customer name(ex. WEB) then all customer with "WEB" in the customer name will be included in the report.
This may help:
-DEFAULT &COMPANY = ' ', &CUST_NAME = ' ', &CUST_NUMB = ' '
-SET &CUST_NAME = IF &CUST_NAME EQ ' ' THEN ' ' ELSE 'WHERE CUSTOMER_NAME LIKE %' || '&CUST_NAME.EVAL' || '%;';
-SET &COMPANY = IF &COMPANY EQ ' ' THEN ' ' ELSE 'WHERE COMPANY EQ ' | '&COMPANY.EVAL' || ';';
-SET &CUST_NUMB = IF &CUST_NUMB EQ ' ' THEN ' ' ELSE 'WHERE CUSTOMER_NO EQ ' | '&CUST_NUMB.EVAL' || ';';
TABLE FILE WHATEVER
SUM
COLUMN_NAMES
BY COLUMN_NAMES
&CUST_NAME
&COMPANY
&CUST_NUMB
ETC...
If the customer number is numeric, remove the quotes.
-DEFAULT &CNTRY = FOC_NONE;
-DEFAULT &CAR = FOC_NONE;
-DEFAULT &BODYTYPE = FOC_NONE;
-* SET BYDISPLAY = ON
-*
TABLE FILE CAR
PRINT RETAIL_COST
BY COUNTRY
BY CAR
BY BODYTYPE
WHERE COUNTRY EQ '&CNTRY'
WHERE CAR EQ '&CAR'
WHERE BODYTYPE EQ '&BODYTYPE'
END
Now the user is prompted for the country, car and bodytype. If he leaves the FOC_NONE the WHERE statement is omitted and the result will show all the countries.
Frank
prod: WF 7.6.10 platform Windows, databases: msSQL2000, msSQL2005, RMS, Oracle, Sybase,IE7 test: WF 7.6.10 on the same platform and databases,IE7
April 25, 2008, 05:20 PM
Darin Lee
quote:
once you add Dialogue Manager outside of the GUI, it balks
You can add DM code OUTSIDE of a table request and DevStudio will handle it OK (for the most part.) However, by placing DM command somwhere between the TABLE FILE and END, DevStudio will no longer parse your request. You're on your own, which is where you were to start with, so I would forget about the DevStudio from this point forward.
Frank's suggestion works great AS LONG AS each WHERE statement it on its OWN line. Otherwise things get ignored that you don't want. Additionally, it would not handle the LIKE functionality requested by Noel.
I frequently use Tom's technique of pre-manufacturing all of my WHERE statements based on parameters used and then use the &parmname.EVAL in place of the WHERE.
In the original question of the posted code "not working" remember that any DM -IF statements need a ; at the end.
Regards,
Darin
In FOCUS since 1991 WF Server: 7.7.04 on Linux and Z/OS, ReportCaster, Self-Service, MRE, Java, Flex Data: DB2/UDB, Adabas, SQL Server Output: HTML,PDF,EXL2K/07, PS, AHTML, Flex WF Client: 77 on Linux w/Tomcat
April 25, 2008, 09:53 PM
Tom Flynn
All,
FOC_NONE is NOT the end-all of WHERE; there are many, many nuances/caveats where it is not a functional option.
Additionally, I try to guide people with under 30 posts the benefits of the core language and Dialogue Manager. I am of the opinion that Dialogue Manager is the "MOST" powerful/efficient piece of the core language.
Additionally, if the code is ever tightened, -DEFAULT "NEVER" ends with a semi-colon; WHERE statements "ALWAYS" end with a semi-colon.
It's documented...
One more thang!: the AUTOPROMPTER is not used as much as HTML forms/JavaScript, which is much more efficient, IMO...
TomThis message has been edited. Last edited by: Tom Flynn,
Thanks Tom, but your solution does not work with the GUI and that is what many beginning users prefer and Information Builders predicts.
There is no course where users learn the coding. My code with FOC_NONE works great and without the ";" I sometimes get error warnings.
Frank
prod: WF 7.6.10 platform Windows, databases: msSQL2000, msSQL2005, RMS, Oracle, Sybase,IE7 test: WF 7.6.10 on the same platform and databases,IE7
April 28, 2008, 09:10 AM
GinnyJakes
Frank,
There are courses where developers can learn the coding syntax, Basic Reporting (102), Dialogue Manager (125), Intermediate Reporting (126), MATCH File (213), Advanced Reporting (210).
Maybe these courses are not available in the Netherlands but they are available and listed in the course catalog from IBI.
-SET &STATE = IF &INPS EQ 'ALL' THEN '' ELSE 'WHERE AGSTE EQ ' | ''&INPS.EVAL''';';
FYI, If you are using amphere variables in a computed WHERE statement, it is not necessary to concatenate the pieces together. The above can become:
-SET &STATE = IF &INPS EQ 'ALL' THEN '' ELSE 'WHERE AGSTE EQ ''&INPS.EVAL'';';
It is a whole lot easier to code than trying to match all of the quotes especially if you are comparing to multiple values.
Pat WF 7.6.8, AIX, AS400, NT AS400 FOCUS, AIX FOCUS, Oracle, DB2, JDE, Lotus Notes
April 28, 2008, 11:24 AM
Glenda
Thanks Pat. Every time I shorten the code, I reduce my error percentage. Every little bit of knowledge helps.
Glenda
In FOCUS Since 1990 Production 8.2 Windows
April 28, 2008, 11:26 AM
cburtt
This thread reminds me of an issue we had with the AutoPrompt facility: i.e. control of the sequence of the prompts. By default the fields are prompted for in the sequence in which the parsing routines determine that no value is supplied. This is usually the top-to-bottom sequence in which &vars are encountered in the procedure.
We found a 'neat' way around this by mentioning them in "dummy" -SET statements at the start of the procedure. An extension of this practice leads to a natural inclusion of the old prompting syntax that defines the content of an AutoPrompt select list.
The only problem sometimes found with this approach is that a long selection list can exceed the allowable line length of older WF releases.
Combining this with the capability of 'FOC_NONE' we get a method by which one can control the order of Autoprompting, the selectable values in the prompts, and still allow for 'ALL':
(I know the use of lower/mixed case &vars is frowned upon, but it makes the presentation look nice.)
APP PATH IBISAMP
-SET &X = '&Country.(ALL,ENGLAND,FRANCE,ITALY).';
-SET &X = '&Car.(ALL,JAGUAR,TOYOTA,DATSUN).';
-SET &X = '&BodyType.(ALL,SEDAN,COUPE).';
-SET &Country =IF &Country EQ 'ALL' THEN 'FOC_NONE' ELSE &Country;
-SET &Car =IF &Car EQ 'ALL' THEN 'FOC_NONE' ELSE &Car;
-SET &BodyType=IF &BodyType EQ 'ALL' THEN 'FOC_NONE' ELSE &BodyType;
TABLE FILE CAR
PRINT RETAIL_COST
BY COUNTRY
BY CAR
BY BODYTYPE
WHERE COUNTRY EQ '&Country'
WHERE CAR EQ '&Car'
WHERE BODYTYPE EQ '&BodyType'
END
WIN/2K running WF 7.6.4 Development via DevStudio 7.6.4, MRE, TextEditor. Data is Oracle, MS-SQL.
April 28, 2008, 08:51 PM
Piipster
..and back to your original post...
quote:
-IF &COLOR EQ '' GOTO :NO_COLOR WHERE COLOR EQ &COLOR :NO_COLOR
...should be ...
-IF &COLOR EQ '' GOTO :NO_COLOR;
WHERE COLOR EQ &COLOR
-:NO_COLOR
Dialog Manager labels need a dash in front of them, as well as the semi-colon after the -IF.
ttfn, kp
Access to most releases from R52x, on multiple platforms.
April 29, 2008, 02:30 AM
Tony A
quote:
the use of lower/mixed case &vars is frowned upon
Really? Depends upon whose standards you follow. Myself, I'd prefer readable, maintainable code and always use mixed case variable names for my own code, and whatever my "Client at the time" prefers for their code
And Noel, take note of what Karen "the Piipster" has mentioned regarding DM syntax.
T
In FOCUS since 1986
WebFOCUS Server 8.2.01M, thru 8.2.07 on Windows Svr 2008 R2
WebFOCUS App Studio 8.2.06 standalone on Windows 10