Alpha Video Training
Results 1 to 14 of 14

Thread: From and To report filter problem

  1. #1
    Member
    Real Name
    George Corder
    Join Date
    May 2002
    Location
    Kentucky
    Posts
    519

    Default From and To report filter problem

    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".

    Code:
    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:

  2. #2
    Member
    Real Name
    George Corder
    Join Date
    May 2002
    Location
    Kentucky
    Posts
    519

    Default

    Can anyone assist with this please?

  3. #3
    "Certified" Alphaholic
    Real Name
    Tom Cone Jr
    Join Date
    Apr 2000
    Location
    Florida
    Posts
    23,314

    Default

    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
    Last edited by Tom Cone Jr; 02-27-2006 at 10:38 PM.

  4. #4
    "Certified" Alphaholic
    Real Name
    Tom Cone Jr
    Join Date
    Apr 2000
    Location
    Florida
    Posts
    23,314

  5. #5
    Member
    Real Name
    George Corder
    Join Date
    May 2002
    Location
    Kentucky
    Posts
    519

    Default

    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.

    Code:
    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. . .
    Last edited by George Corder; 02-28-2006 at 09:42 AM.

  6. #6
    "Certified" Alphaholic
    Real Name
    Tom Cone Jr
    Join Date
    Apr 2000
    Location
    Florida
    Posts
    23,314

    Default

    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

  7. #7
    Member
    Real Name
    George Corder
    Join Date
    May 2002
    Location
    Kentucky
    Posts
    519

    Default

    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.

    Last edited by George Corder; 02-28-2006 at 10:41 AM.

  8. #8
    Alpha Software Employee Lenny Forziati's Avatar
    Real Name
    Lenny Forziati
    Join Date
    Nov 2001
    Location
    Alpha Software
    Posts
    4,701

    Default

    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.

    Quote Originally Posted by 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 and will ask for help on this.

  9. #9
    "Certified" Alphaholic
    Real Name
    Tom Cone Jr
    Join Date
    Apr 2000
    Location
    Florida
    Posts
    23,314

    Default

    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

  10. #10
    Volunteer Moderator Steve Wood's Avatar
    Real Name
    Steve Wood
    Join Date
    Nov 2003
    Location
    Bay Area, California
    Posts
    8,843

    Default

    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):

    Code:
    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.
    Steve Wood
    Join the ALPHA DEVELOPERS NETWORK
    There is no Cloud. It's just someone else's computer.
    Web - Mobile - Hosting - Products - Frameworks - Developer Resources
    AlphaToGo | IADN (100% Alpha Anywhere Websites)

  11. #11
    Member
    Real Name
    George Corder
    Join Date
    May 2002
    Location
    Kentucky
    Posts
    519

    Default

    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?

    Code:
    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:

  12. #12
    Volunteer Moderator Steve Wood's Avatar
    Real Name
    Steve Wood
    Join Date
    Nov 2003
    Location
    Bay Area, California
    Posts
    8,843

    Default

    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.
    Steve Wood
    Join the ALPHA DEVELOPERS NETWORK
    There is no Cloud. It's just someone else's computer.
    Web - Mobile - Hosting - Products - Frameworks - Developer Resources
    AlphaToGo | IADN (100% Alpha Anywhere Websites)

  13. #13
    Member
    Real Name
    George Corder
    Join Date
    May 2002
    Location
    Kentucky
    Posts
    519

    Default

    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.

    Code:
    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:

  14. #14
    Volunteer Moderator Steve Wood's Avatar
    Real Name
    Steve Wood
    Join Date
    Nov 2003
    Location
    Bay Area, California
    Posts
    8,843

    Default

    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.
    Steve Wood
    Join the ALPHA DEVELOPERS NETWORK
    There is no Cloud. It's just someone else's computer.
    Web - Mobile - Hosting - Products - Frameworks - Developer Resources
    AlphaToGo | IADN (100% Alpha Anywhere Websites)

Similar Threads

  1. Filter in report.saveas problem
    By Pat Bremkamp in forum Web Application Server v7
    Replies: 6
    Last Post: 11-14-2005, 12:53 PM
  2. Report Filter problem
    By Bill Walker in forum Alpha Five Version 6
    Replies: 5
    Last Post: 02-07-2005, 08:56 AM
  3. Report filter problem
    By Fred Daniel in forum Alpha Five Version 5
    Replies: 5
    Last Post: 01-27-2003, 06:20 AM
  4. Problem with filter in a report
    By cadmen in forum Alpha Five Version 4
    Replies: 4
    Last Post: 06-25-2000, 04:53 PM
  5. Sub-report filter problem
    By BRochford in forum Alpha Five Version 4
    Replies: 4
    Last Post: 04-12-2000, 07:09 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •