Hello,

I have a report where most of the parameters are chosen by the user by means of a dropdown list populated from the master file. Two of these parameters, Invoice Number and PO Number, are too numerous to be populated into dropdown lists, so I have made them as info boxes where the user will have to supply the number for which they wish to query.

I'm using the .EVAL suffix on these variables in the WHERE statements:

 
WHERE SALESTAX.PO_NUMBER EQ '&PO_NUMBER.EVAL';
WHERE SALESTAX.INVOICE_NO EQ '&INVOICE_NO.EVAL';

The problem lies when trying to have both statements function at once. When I comment out either WHERE statement, the other works fine. When I have them both active and run the report, entering a valid number into either box (one at a time...never a number in each box) produces a blank report.

The full contents of my .fex can be seen below. I apologize for pasting it right into the post, but pastebin.com seems to be having issues.

 -* File MonthlySalesTax.fex
-* ====================================================================
-*
-* FOCEXEC         : MonthlySalesTax
-*
-* DESCRIPTION    : Compares taxes owed on purchases within a given period
-*				to the taxes paid within the same date range in order
-*				to determine what taxes are outstanding. Ultimately, 
-*				the report should reflect what was purchased, the 
-*				value, and whether taxes were paid on it per invoice.
-*
-* WRITTEN BY        : me
-*
-* WRITTEN ON      : 03/11/2009
-*
-* INPUTS        	      : salestax.mas
-*
-* PARAMETERS     : Account:	&ACCOUNT		(One or More)
-*                	      : Year:		&YEAR			(One or More)
-*                           : Period:		&PERIOD			(One or More)
-*                           : CostCenter:	&COST_CENTER	(One or More)
-*                           : PO_Number:	&PO_NUMBER  	(One)
-*                           : Invoice_No:	&INVOICE_NO 	(One)
-*                           : Format: 	        &WFFMT      		(One)
-*                
-* NOTES          :
-* 
-* ====================================================================
-*
-*                  M A I N T E N A N C E
-*
-* CHG DATE     CHG BY          DESCRIPTION
-* ----------   -------------   -----------
-* 03/11/2009   me    Initial version
-*
-*
-* ====================================================================
-SET &ECHO=ALL;
SET NODATA = '-no data-'

TABLE FILE SALESTAX

HEADING
    	"Monthly Sales Tax by Invoice </1"
    	"RUN DATE:&DATEMDYY RUN TIME:&TOD"
    	"PGM: MonthlySalesTax"
    	"For Account(s): &ACCOUNT"
    	"For Cost Center(s): &COST_CENTER"
   	"For PO Number: &PO_NUMBER"
   	"For Invoice Number: &INVOICE_NO"
    	"Year: &YEAR Period: &PERIOD </1"

PRINT
	ACCOUNT AS 'Account'
    	INVOICE_DATE AS 'Supplier,Invoice Date' 
	VOUCHER_DATE AS 'Book,Date'
	ITEM_DESC AS 'Item,Description'
    	POSTING_TYPE AS 'Posting,Type'
	AMOUNT AS 'Total'
	SHIPPING_AMOUNT AS 'Shipping'
	SALES_TAX AS 'Sales,Tax'

BY COST_CENTER AS 'Cost,Center'
BY SUPPLIER AS 'Supplier'
BY PO_NUMBER AS 'Purchase,Order'
BY INVOICE_NO AS 'Invoice,Number'

-*--------------------------------------------------
-* Need to find a way to always query for 80274 regardless of what other accounts are chosen.
-* WHERE ACCOUNT = '80274'
-*-------------------------------------------------- 
WHERE SALESTAX.ACCOUNT EQ &ACCOUNT.(OR(FIND ACCOUNT IN SALESTAX)).Please select an Account.;
WHERE SALESTAX.YEAR EQ &YEAR.(OR(FIND YEAR IN SALESTAX)).Please select a Year.;
WHERE SALESTAX.PERIOD EQ &PERIOD.(OR(FIND PERIOD IN SALESTAX)).Please select a Period.;
WHERE SALESTAX.COST_CENTER EQ &COST_CENTER.(OR(FIND COST_CENTER IN SALESTAX)).Please select a Cost Center.;
WHERE SALESTAX.PO_NUMBER EQ '&PO_NUMBER.EVAL';
WHERE SALESTAX.INVOICE_NO EQ '&INVOICE_NO.EVAL';

ON TABLE SET PAGE-NUM OFF
ON TABLE SET BYDISPLAY ON
ON TABLE SET EMPTYREPORT ON
ON TABLE SET HTMLCSS ON
ON TABLE PCHOLD FORMAT &WFFMT.(<HTML,HTML>,<Excel 2000,EXL2K>,).Please select report format.

ON TABLE SET STYLE *
    UNITS=IN,
    SQUEEZE=ON,
    ORIENTATION=PORTRAIT,
$
TYPE=REPORT,
    GRID=OFF,
    FONT='CENTURY GOTHIC',
    SIZE=10,
$
ENDSTYLE

END

I should also add that I am still a novice in writing FOCUS code, and this is my first attempt at creating a report from scratch entirely in FOCUS. If you notice any other errors, omissions, etc. please feel free to point them out!

Thanks,


sleepy

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

quote:

info box

What is that?

You don't need the .EVAL for &PO_NUMBER and &INVOICE_NO.

You do realize that the two WHERE statements act like AND, meaning the data retrieved has to satisfy both filters.

Run an HTML version of the report and then "View Source" from the web browser. Copy the WebFOCUS code that's displayed and then paste that into a posting here so we can see &PO_NUMBER and &INVOICE_NO translated into actual values.

quote:

Originally posted by Francis Mariani:

quote:

info box

What is that?

The blank box into which a user types their criteria for a parameter, or the box that appears on the default WF parameter selection screen if you just use a &VARIABLE in a fex. I just happened to call it an info box.

quote:

Originally posted by Francis Mariani:
You don't need the .EVAL for &PO_NUMBER and &INVOICE_NO.

I didn't think so either, but I added it after searching the forums as to why the WHERE statements weren't working. That was before you reminded me of the below point...

quote:

Originally posted by Francis Mariani:
You do realize that the two WHERE statements act like AND, meaning the data retrieved has to satisfy both filters.

Umm...I do now! Thanks for the reminder.

When I use a compound WHERE statement with an OR clause:

WHERE (PO_NUMBER EQ '&PO_NUMBER')
    OR (INVOICE_NO EQ '&INVOICE_NO');

the parameters function perfectly.

Thank you for your quick response, and also for your patience when dealing with such an obvious mistake. I'm glad there isn't a [NOOB] tag for forum threads!


sleepy

Sleepy,

Concerning .EVAL, this should only be used in a Dialog Manager command when you want an &variable to be evaluated first. In a TABLE element, it has no meaning and is ignored.