From and To report filter problem

[George Corder]

I read the existing post but cannot use the recommended between argument to build a successful filter. I want to filter the report for dates between fromdate and todate and let the user choose which report they wish to view. The radiobutton of report selection works fine if tested with filter = ""
I would appreciate any help on this. Submitting the code below opens Adobe but it displays a single page in acrobat stating "Report does not contain any records".

IF eval_valid("fromdate") = .f. .or. eval_valid("todate") = .f. Then
goto skipreport
end if 
dim filter as c 
dim order as c 
Filter = "between(dtoc(transdate)," + s_quote(fromdate) + "," + s_quote(todate) + ")"
Order = ""

Dim filename as c 
select
    case radiobutton3="1"
    	 filename = session.session_folder + chr(92) + "tempreport.pdf"
   		 filename = report.saveas("MonthlyTellerActivity@<ADB_Path>\tellertotals.dbf","pdf",filter,order,filename,.f.)
		 if file.exists(filename) 
			currentform.RedirectTarget = session.session_url + "tempreport.pdf?" + rand_string(5)
		end if
	case radiobutton3="2"
		 filename = session.session_folder + chr(92) + "tempreport.pdf"
		 filename = report.saveas("MonthlyTellerReport@<ADB_Path>\tellertotals.dbf","pdf",filter,order,filename,.f.)
		 if file.exists(filename) 
		 	currentform.RedirectTarget = session.session_url + "tempreport.pdf?" + rand_string(5)
		end if

end select
skipreport:

[George Corder]

Can anyone assist with this please?

[Tom Cone Jr]

This explains why your filter is broken:

dim transdate as d; transdate = {01/15/2006} 'US format
dim fromdate as d; fromdate = {01/01/2006}
dim todate as d; todate = {01/31/2006}

?dtoc(transdate)
= "01/15/2006"

?s_quote(fromdate)
= "{01/01/2006}"

Notice that s_quote() returns a date type value, while dtoc() returns a character type value. You cannot compare one to the other.

------------------------------------------------------------------------

Using the Invoice report in AlphaSports, the following seems to work ok,
and could be adapted to your situation:

:report.preview("Invoice","between(date,"+s_quote(fromdate)+","+s_quote(todate)+")")
where date is a date type field;
fromdate is a date type variable; and
todate is a date type variable.

Hope this helps.

-- tom

[Tom Cone Jr]

George, see this thread: http://msgboard.alphasoftware.com/al...ad.php?t=59900

[George Corder]

Tom, thank you for the reply. I am still getting an error on this of:
Errors in: event.AfterValidate code:
Variable is of different type

I have altered the code to that shown below. Also, how can I do your '?dtoc(transdate)' on the web projects control panel to see what certain variables are going to return from a a5w page? Like an interactive screen on the desktop side.

IF eval_valid("fromdate") = .f. .or. eval_valid("todate") = .f. Then
goto skipreport
end if 
dim filter as c 
dim order as c 
dim transdate as d
dim fromdate as d
dim todate as d
Filter = "between(transdate," + s_quote(fromdate) + "," + s_quote(todate) + ")"
Order = ""

Dim filename as c 
select
    case radiobutton3="1"
    	 filename = session.session_folder + chr(92) + "tempreport.pdf"
   		 filename = report.saveas("MonthlyTellerActivity@<ADB_Path>\tellertotals.dbf","pdf",filter,order,filename,.f.)
		 if file.exists(filename) 
			currentform.RedirectTarget = session.session_url + "tempreport.pdf?" + rand_string(5)
		end if  . . .snipped. . .

[Tom Cone Jr]

George,

I don't see where fromdate and todate receive their values.
I don't see the error message you're getting.
When you dim fromdate and todate like this you're creating local variables. Is this what you want, or are you trying to use values assigned in earlier sequences?

I have little experience with the web app server. Can't tell you how to trouble shoot this in that context.

Does your script work in the desktop?

-- tom

[George Corder]

On the a5w page I have created a dialog component that ask's the user to input the fromdate and todate along with which of 2 reports they want to view. This is supposed to hold the date choices in the fromdate and todate variables.

I am going to attend the PACE 2 hr tech support webinar tomorrow at 2:00p and will ask for help on this.

[Lenny Forziati]

Like in the Alha Five desktop, reports run in their own session and do not have access to local variables. You must dim and use a global variable in order for your report to see it.

On the a5w page I have created a dialog component that ask's the user to input the fromdate and todate along with which of 2 reports they want to view. This is supposed to hold the date choices in the fromdate and todate variables.

I am going to attend the PACE 2 hr tech support webinar tomorrow and will ask for help on this.

[Tom Cone Jr]

George,

I think the values in your variables are not carrying forward into the script and the filter expression. I wish I know more about the Web App serve so I could tell you how to determine what the variable values are when the script is running. There's an entire chapter in the Web Publishing reference on using variables in a5w pages. My guess is that you have to pass on the values inputted by the user to the next page somehow. Otherwise the variables in your script are empty when the filter tries to evaluate them.

Perhaps more knowledgable folks will lend a hand here. I'll be watching, cause your script is failing even though the filter expression is (finally) ok.

-- tom

[Steve Wood]

George,

Thanks for the call, but I think I will put up a solution here rather than off-line.

This filter works (substituting your field names for mine, and assuming your table field is an Date type):

Filter = "received_date > {"+date1+"} .and. received_date < {"+date2+"}"

Everything else in your code and dialog is fine. I could not get the between() to work and admit that I don't work with dates enough to be quick on that.

[George Corder]

Thanks Steve. I have tried to add in the variables as mentioned by Tom. I cannot get the Filter to work still. I have tried you code above and narrowed it down to just trying to get the "Fromdate" to work as shown below. Do you have any other thoughts?

IF eval_valid("fromdate") = .f. .or. eval_valid("todate") = .f. Then
goto skipreport
end if 
dim filter as c 
dim order as c

dim Global date1 as d
dim Global date2 as d
date1 = "fromdate"
date2 = "todate"

Filter = "transdate >=" + "+s_quote(date1)+"
Order = "Branchid"

Dim filename as c 
select
    case radiobutton3="1"
    	 filename = session.session_folder + chr(92) + "tempreport.pdf"
   		 filename = report.saveas("MonthlyTellerActivity@<ADB_Path>\tellertotals.dbf","pdf",filter,order,filename,.f.)
		 if file.exists(filename) 
			currentform.RedirectTarget = session.session_url + "tempreport.pdf?" + rand_string(5)
		end if
	case radiobutton3="2"
		 filename = session.session_folder + chr(92) + "tempreport.pdf"
		 filename = report.saveas("MonthlyTellerReport@<ADB_Path>\tellertotals.dbf","pdf",filter,order,filename,.f.)
		 if file.exists(filename) 
		 	currentform.RedirectTarget = session.session_url + "tempreport.pdf?" + rand_string(5)
		end if

end select
skipreport:

[Steve Wood]

Did you try the filter I suggested? Using your variables, it would be:

Filter = "transdate >= {"+fromdate+"} .and. transdate <= {"+todate+"}"

exactly as shown, including all the curly brackets, etc.

Your Filter = "transdate >=" + "+s_quote(date1)+" is not properly formed at all.

You do not need to dim any variables as global, even though that was suggested below.

[George Corder]

Fantastic Steve. I have inserted the final working code below.

I would like to add the target="_blank" tag or equivelant code to instruct the pdf to open in a new window or tab of the browser, but I don't know where I should put this.

I appreciate your help.

IF eval_valid("fromdate") = .f. .or. eval_valid("todate") = .f. Then
goto skipreport
end if 
dim filter as c 
dim order as c

Filter = "transdate >= {"+fromdate+"} .and. transdate <= {"+todate+"}"
Order = "Branchid"

Dim filename as c 
select
    case radiobutton3="1"
    	 filename = session.session_folder + chr(92) + "tempreport.pdf"
   		 filename = report.saveas("MonthlyTellerActivity@<ADB_Path>\tellertotals.dbf","pdf",filter,order,filename,.f.)
		 if file.exists(filename) 
			currentform.RedirectTarget = session.session_url + "tempreport.pdf?" + rand_string(5)
		end if
	case radiobutton3="2"
		 filename = session.session_folder + chr(92) + "tempreport.pdf"
		 filename = report.saveas("MonthlyTellerReport@<ADB_Path>\tellertotals.dbf","pdf",filter,order,filename,.f.)
		 if file.exists(filename) 
		 	currentform.RedirectTarget = session.session_url + "tempreport.pdf?" + rand_string(5)
		end if

end select
skipreport:

[Steve Wood]

I asked that question once and was told it is not possible to cause the PDF to open in a new window. _target was incompatible with redirect. _target is normally associated with <a href...>.

You might search for that thread to see if there was an alternate suggestion; javascript perhaps. I haven't resolved this - the problem is that users close the PDF and therefore unintentionally close the WAS application.