Alpha Video Training
Results 1 to 9 of 9

Thread: About Date Range Filter

  1. #1
    Member
    Real Name
    Juliet
    Join Date
    Jul 2009
    Posts
    11

    Default About Date Range Filter

    Hi,
    I'm creating sample reports using QRB from QuickBooks. And about the date range, I specify the "DataFrom" and "DataTo" field when creating a new report from QuickBooks. But after I finished creating the report, I don't know how to modify the date range.
    In some reports, there is a "Date" field in them, so I can add a filter to modify the date range. But in other reports, there isn't any field about date. So how can I control the date range of the reports without date field?Thanks.


    Juliet

  2. #2
    Moderator
    Real Name
    Alan Buchholz
    Join Date
    Oct 2000
    Location
    Delavan, Wisconsin
    Posts
    9,560

    Default Re: About Date Range Filter

    Quote Originally Posted by Juliet View Post
    Hi,
    I'm creating sample reports using QRB from QuickBooks. And about the date range, I specify the "DataFrom" and "DataTo" field when creating a new report from QuickBooks. But after I finished creating the report, I don't know how to modify the date range.
    In some reports, there is a "Date" field in them, so I can add a filter to modify the date range. But in other reports, there isn't any field about date. So how can I control the date range of the reports without date field?Thanks.


    Juliet
    Juliet

    Good Monday morning and welcome to the QReportBuilder messageboard.

    Are you using a filter with a variable and which report do you need a date filter for and can't find the date field?

    Please post an example of the filter that you are now using.
    Al Buchholz
    Bookwood Systems, LTD
    Weekly QReportBuilder Webinars Thursday 1 pm CST

    Occam's Razor - KISS
    Normalize till it hurts - De-normalize till it works.
    Advice offered and questions asked in the spirit of learning how to fish is better than someone giving you a fish.
    When we triage a problem it is much easier to read sample systems than to read a mind.

  3. #3
    Member
    Real Name
    Juliet
    Join Date
    Jul 2009
    Posts
    11

    Default Re: About Date Range Filter

    Hi,
    I want to create report Income by Customer Summary, there are only four default fields in this report, they are: Amount_count, Amount_1,Label and Text. Indeed when I create this sample report, I can select columns that I need except the default ones but there is still no date field.
    I didn't create create the filter using variables, when I tried to create this report using qbreport_incomebycustomersummary_set.set, I can define connection,select columns and then specify report filter. I can specify the value of the "DateFrom" and the "DateTo" filter parameters and then press the button "Finish" to finish creating this report. But after that, I don't know how to modify the date range filter...
    Any ideas? Thanks.

  4. #4
    Moderator
    Real Name
    Alan Buchholz
    Join Date
    Oct 2000
    Location
    Delavan, Wisconsin
    Posts
    9,560

    Default Re: About Date Range Filter

    Quote Originally Posted by Juliet View Post
    Hi,
    I want to create report Income by Customer Summary, there are only four default fields in this report, they are: Amount_count, Amount_1,Label and Text. Indeed when I create this sample report, I can select columns that I need except the default ones but there is still no date field.
    The date is not listed as an output item, because the report is a summary by customer, so there is not a detail report line to display the date with.
    Quote Originally Posted by Juliet View Post
    I didn't create create the filter using variables, when I tried to create this report using qbreport_incomebycustomersummary_set.set, I can define connection,select columns and then specify report filter. I can specify the value of the "DateFrom" and the "DateTo" filter parameters and then press the button "Finish" to finish creating this report. But after that, I don't know how to modify the date range filter...
    Any ideas? Thanks.
    The DateFrom and DateTo are available in the parameter area as a filter, but it is a little tricker to get the date to be easily modified....

    The easy way is to always want the yeartodate value and then use this SQL:
    Code:
    sp_report IncomeByCustomerSummary show Amount_Title, Text, Label, Amount parameters DateMacro = 'ThisYearToDate', SummarizeColumnsBy = 'TotalOnly'
    OR... the harder way...
    Since the system doesn't allow for a variable/argument to be linked to the filter in the sp_report statement, we have to take one step back and use the DAO Import routine to set the SQL statement with variables and then insert the variables into the SQL statement..

    More advanced coding..

    Code:
    vcQBCompanyFile= "<YourCompanyFileName>"
    
    dim connectionString as c 
    connectionString = "{A5API=QuickBooks,FileName='"+alltrim(vcQBCompanyFile)+"',UserName='QRB'}" 
    
    delete options 
    dim options as p 
    options.ConsolidateArguments= .f.
    options.AddTablesToDatabase= .t.
    options.ShowProgress= .f.
    options.AllowCancel= .f.
    
    
    delete a_import
    dim a_import[0] as p 
    SQLSelectStatement = <<%sql%
    sp_report IncomeByCustomerSummary show Amount_Title, Text, Label, 
    Amount parameters DateTo = :argDateTo, DateFrom = :argDateFrom, SummarizeColumnsBy = 'TotalOnly'
    %sql%
    
    dim vdDateFrom as D
    vdDateFrom={01/01/2009}
    dim vdDateTo as D
    vdDateTo={08/17/2009}
    
    dim args as sql::arguments
    
    args.add("argDateTo",vdDateTo)
    args.add("argDateFrom",vdDateFrom)
    
    a_import[].ObjectName = "SPReports"
    a_import[..].SQLSelectStatement = replace_arguments_in_string(SQLSelectStatement,args)
    a_import[..].SQLType = "Stored Procedure"
    a_import[..].Arguments = ""
    a_import[..].ImportType = "Create passive-link table"
    a_import[..].LocalTableName = a5.get_path() + chr(92) + "Income_CustSummary" +".dbf"
    a_import[..].LocalTableFieldDef = ""
    a_import[..].OverwriteOption = "Overwrite without prompting"
    
    dim flagSilent as l
    flagSilent = .t.
    delete p 
    dim p as p 
    p = a5_AlphaDAO_Import(connectionString,a_import,options,flagSilent)
    Al Buchholz
    Bookwood Systems, LTD
    Weekly QReportBuilder Webinars Thursday 1 pm CST

    Occam's Razor - KISS
    Normalize till it hurts - De-normalize till it works.
    Advice offered and questions asked in the spirit of learning how to fish is better than someone giving you a fish.
    When we triage a problem it is much easier to read sample systems than to read a mind.

  5. #5
    Member
    Real Name
    Bill Murray
    Join Date
    Jun 2009
    Location
    Great Falls, VA
    Posts
    42

    Default Re: About Date Range Filter

    Al - Would you mind taking a look at the "more advanced" coding you posted?

    I copied/pasted it into QRB 9, with only these minor changes:

    Code:
    line 1: vcQBCompanyFile= "."  
    line 4: connectionString = "{A5API=QuickBooks,FileName='"+alltrim(vcQBCompanyFile)+"',UserName='Admin'}"
    No table was created, and I received this error message:

    11015 - '[QODBC] Expected lexical element not found: {08/17/2009}
    SQL State is: 42000'

    Looking at it in the debugger, it doesn't seem like the function replace_arguments_in_string() is producing the desired result. Removing your argument substitution technique and all parameters produces a table just fine.

    Thanks.

  6. #6
    Moderator
    Real Name
    Alan Buchholz
    Join Date
    Oct 2000
    Location
    Delavan, Wisconsin
    Posts
    9,560

    Default Re: About Date Range Filter

    Coulda swore that was working before... this is working now..
    Code:
    vcQBCompanyFile= "."
    
    dim connectionString as c 
    connectionString = "{A5API=QuickBooks,FileName='"+alltrim(vcQBCompanyFile)+"',UserName='QRB'}" 
    debug(1)
    delete options 
    dim options as p 
    options.ConsolidateArguments= .f.
    options.AddTablesToDatabase= .t.
    options.ShowProgress= .f.
    options.AllowCancel= .f.
    
    
    delete a_import
    dim a_import[0] as p 
    SQLSelectStatement = <<%sql%
    sp_report IncomeByCustomerSummary show Amount_Title, Text, Label, 
    Amount parameters DateTo = :argDateTo, DateFrom = :argDateFrom, SummarizeColumnsBy = 'TotalOnly'
    %sql%
    
    'sp_report SalesByCustomerSummary show Amount, Label, Text Parameters DateFrom = {d'2009-01-01'}, DateTo = {d'2009-12-31'}
    dim vdDateFrom as C
    vdDateFrom="{d'2009-01-01'}"
    dim vdDateTo as C
    vdDateTo="{d'2009-12-31'}"
    
    dim args as sql::arguments
    
    args.add("argDateTo",vdDateTo)
    args.add("argDateFrom",vdDateFrom)
    
    SQL_MOD=replace_arguments_in_string(SQLSelectStatement,args)
    'REMOVE THE DOUBLE QUOTES around the dates
    SQL_MOD=strtran(SQL_MOD,"\"","")
    
    TN=a5.get_path() + chr(92) + "Income_CustSummary" +".dbf"
    
    a_import[].ObjectName = "SPReports"
    a_import[..].SQLSelectStatement = SQL_MOD
    a_import[..].SQLType = "Stored Procedure"
    a_import[..].Arguments = ""
    a_import[..].ImportType = "Create passive-link table"
    a_import[..].LocalTableName = TN
    a_import[..].LocalTableFieldDef = ""
    a_import[..].OverwriteOption = "Overwrite without prompting"
    
    dim flagSilent as l
    flagSilent = .t.
    delete p 
    dim p as p 
    p = a5_AlphaDAO_Import(connectionString,a_import,options,flagSilent)
    If you use the debugger and watch some of the variables, you can see what is happening..

    Otherwise take out the debug(1) to run silently.
    Al Buchholz
    Bookwood Systems, LTD
    Weekly QReportBuilder Webinars Thursday 1 pm CST

    Occam's Razor - KISS
    Normalize till it hurts - De-normalize till it works.
    Advice offered and questions asked in the spirit of learning how to fish is better than someone giving you a fish.
    When we triage a problem it is much easier to read sample systems than to read a mind.

  7. #7
    Member
    Real Name
    Bill Murray
    Join Date
    Jun 2009
    Location
    Great Falls, VA
    Posts
    42

    Default Re: About Date Range Filter

    Thanks, Al.

    That works in both QRB 9 and Alpha 5 v 10.

  8. #8
    Member
    Real Name
    Barry J. Hill
    Join Date
    Feb 2011
    Posts
    4

    Default Re: About Date Range Filter

    I am using the QB report inventory valuation summary as a basis for a simple report. As with the original poster I can set the Date filter in the parameter area but it seems I have to recreate the report each time I want a different Dateto filter. You suggested a simple solution of YearToDate - Is there one for LastMonth?

  9. #9
    Moderator
    Real Name
    Alan Buchholz
    Join Date
    Oct 2000
    Location
    Delavan, Wisconsin
    Posts
    9,560

    Default Re: About Date Range Filter

    Quote Originally Posted by SEPANEL View Post
    I am using the QB report inventory valuation summary as a basis for a simple report. As with the original poster I can set the Date filter in the parameter area but it seems I have to recreate the report each time I want a different Dateto filter. You suggested a simple solution of YearToDate - Is there one for LastMonth?
    Yes there is.. You can also use the begin date and end date and click prompt when you run
    Al Buchholz
    Bookwood Systems, LTD
    Weekly QReportBuilder Webinars Thursday 1 pm CST

    Occam's Razor - KISS
    Normalize till it hurts - De-normalize till it works.
    Advice offered and questions asked in the spirit of learning how to fish is better than someone giving you a fish.
    When we triage a problem it is much easier to read sample systems than to read a mind.

Similar Threads

  1. Group Filter Date Range
    By TTI in forum Alpha Five Version 8
    Replies: 2
    Last Post: 06-01-2007, 09:30 PM
  2. print with date range
    By nhalyn in forum Web Application Server v6
    Replies: 1
    Last Post: 01-26-2006, 10:43 AM
  3. Date Range in Xbasic
    By Alan Lucas in forum Alpha Five Version 5
    Replies: 8
    Last Post: 03-10-2003, 06:25 PM
  4. Enter a date range
    By Scott Rusoff in forum Alpha Five Version 5
    Replies: 1
    Last Post: 02-10-2003, 08:12 AM
  5. Date Range Filter
    By Marc King - A5solutions in forum Alpha Five Version 5
    Replies: 8
    Last Post: 12-26-2002, 06:19 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
  •