Alpha DevCon 2018
Results 1 to 11 of 11

Thread: Retrieving and printing passed variables on the report

  1. #1
    Member
    Real Name
    Tim Barker
    Join Date
    May 2013
    Posts
    56

    Default Retrieving and printing passed variables on the report

    I am passing a From Date and a To Date from a Tabbed UI Button for a report that is used to filter the records displayed on the report. I would like to print the entered From Date and To Date on the report. The SQL filter on the Report Genie is LOGS.log_date>=:fromDate AND LOGS.log_date<=:toDate and the Argument bindings fromDate and toDate and the Argument Value is <Prompt at Run-Time>


    I have been looking into how to do this and the documentation is fuzzy. What I've found is:


    a5_getReportSQLFilter(L flagResolveArgument) - gets the SQL filter that was passed in to the print method. If the flagResolveArguments parameter is set to .T. (default value), the the arguments in the filter are resolved
    To print out the value of the argument that was passed in, you would create a calculated field that uses the A5_getArgumentValue() function

    In the Calculated Values, I've defined two values:
    FDate = a5_getargumentvalue(calc->fromDate)
    TDate = a5_getargumentvalue(calc->toDate)

    I'm trying to use the FDate and TDate to print the values on the report.


    So it looks like I might have the right pieces, I'm just not sure how and where to apply them. So my specific questions are:

    1. Where do I define and enter the a5_getReportSQLFilter(L flagResolveArgument) to retrieve the variables that were entered for the report filtering?
    2. How are the Calculated fields entered that will allow me to print the To and From dates?

    Thanks for the help

    Tim

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

    Default Re: Retrieving and printing passed variables on the report

    Tim

    QReportBuilder is an application written in Alpha to report from QuickBooks.

    I don't see that your questions are related to QuickBooks.

    You should put the posts in the proper forum for the version of Alpha that you are using.

    Let me know what that is and I'll move the posts.
    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
    Tim Barker
    Join Date
    May 2013
    Posts
    56

    Default Re: Retrieving and printing passed variables on the report

    My bad Al...I didn't realize this was a QuickBooks forum.

    My question is related to Alpha5 version 11 and passing variables from a Tabbed UI to the Report Editor and being able to print those passed variables on the report
    Last edited by Al Buchholz; 06-09-2013 at 03:49 PM. Reason: Moved to v11 WAS forum

  4. #4
    Member
    Real Name
    Dave
    Join Date
    Jul 2012
    Location
    Southern California
    Posts
    620

    Default Re: Retrieving and printing passed variables on the report

    Hi Tim,

    Lol! Of course you are on in the right forum. I have never used a5_getReportSQLFilter before, but I think you are going to want to extract the dates from the SQL filter using a character extraction function. You can do this by creating a calculated field. Something like "FromDate = extract_string(a5_getReportSQLFilter(.t.), ">= ", " AND")". Let me know if you have gotten any further since your post and I will see if I can be of more help.
    Last edited by DaveF; 06-09-2013 at 07:02 PM.

  5. #5
    Member
    Real Name
    Tim Barker
    Join Date
    May 2013
    Posts
    56

    Default Re: Retrieving and printing passed variables on the report

    Thanks Dave...

    So at run time, the user is prompted to enter two dates, From Date and To Date. The query then selects the records in the table that falls in between those two dates. So now I want to print the dates entered by the user at run time on the report itself. Sounds like I'm close but still have a couple questions.

    So how do I get the From Date that was entered by the user into the calculated field of FromDate in this definition "FromDate = extract_string(a5_getReportSQLFilter(.t.)" ? Don't I somehow have to reference the entered From Date to be able to assign it to the calculated field?
    Same question for the To Date...

    I'm assuming that once I have captured the entered values in these calculated fields, it's simply a matter of dragging and dropping those calculated fields onto the report.

    Thanks in advance for any help you can provide.

    Tim

  6. #6
    Member
    Real Name
    Dave
    Join Date
    Jul 2012
    Location
    Southern California
    Posts
    620

    Default Re: Retrieving and printing passed variables on the report

    First of all, have you already started on your report and do you have your data source definition set up? Are you using Action Javascript to open a report when the user clicks a button in your Tabbed UI? I'm also curious as to whether you are passing any arguments to your report. Report arguments are optional and are a common method for passing data to the report which can then be printed in the heading. You define your arguments in the report's data source definition and then bind them in the Action Javascript builder that opens a report.

    You would create a calculated field for each date. Go to the Report menu and select "Calculated fields...". Then you would enter an expression to extract the date from the SQL filter. For example:

    FromDate = extract_string(a5_getReportSQLFilter(.t.), ">= ", " AND")

    Again, I have never used a5_getReportSQLFilter() so I am just guessing as to whether or not this will work. You can use the Interactive Window to experiment with extract_string or any other function to make sure it is working correctly. You need to do the same think with the To Date and create a calculated field for it. You could get fancy and create one calculated field that extracts both dates and formats a string that you can print on the report.

    Once you have your calculated fields created for your dates, just drag them from the calculated field list on the right side of the report editor to your report heading or wherever you want them to print.

  7. #7
    Member
    Real Name
    Tim Barker
    Join Date
    May 2013
    Posts
    56

    Default Re: Retrieving and printing passed variables on the report

    That's exactly what I'm doing. I defined a button that when the user clicks the button on the Tabbed UI, they are prompted to put in a From Date and a To Date.

    On the Report Definition in the Tabbed UI Design, the SQL filter looks like this: LOGS.log_date>=:fromDate AND LOGS.log_date<=:toDate

    I also bound the arguments in the Argument bindings field like this: fromDate|Date|{<Prompt at Run-time>} toDate|Date|{<Prompt at Run-time>}

    All this works great and I only get the Log records back where the Log Date falls in between the two entered dates.

    So now if I define two calculated fields like this...

    fromDate = extract_string(a5_getReportSQLFilter(.t.), ">= ", " AND")
    toDate = extract_string(a5_getReportSQLFilter(.t.), ">= ", " AND")

    ...then fromDate and toDate should show up in the Calculated Fields section in the Report Editor? Then I just need to drag and drop onto the report and the values should print?

    I'm still a little hazy on the format of the extract_string command (above). Do I need ">= ", " AND"

    Tim

  8. #8
    Member
    Real Name
    Dave
    Join Date
    Jul 2012
    Location
    Southern California
    Posts
    620

    Default Re: Retrieving and printing passed variables on the report

    I'm assuming that a5_getReportSQLFilter will return a string similar to the following:

    log_date >= 6/01/2013 AND log_date <= 6/10/2013

    a5_getReportSQLFilter is just a function that should return a character string containing your SQL filter with the from and to dates inserted. Take a look at the extract_string function and the other alpha character extraction functions for more information on how to extract characters from a string. http://wiki.alphasoftware.com/EXTRACT_STRING+Function

    You can open an Interactive Window and test to make sure the extract_string function is extracting the string correctly. For example, you can type in the following:

    dim sqlFilter as c = "log_date >= 6/01/2013 AND log_date <= 6/10/2013"
    ?extract_string(sqlFilter, ">= ", " AND")

    It should display the from date in the test sqlFilter string. I tried doing the following to extract the to string but it didn't work. You may have to use another extraction function to pull it out:

    ?extract_string(sqlFilter, "<= ", "")

    I thought the "" would tell the function to terminate at a null but it didn't work for some reason. Once you know how to extract the date strings from the sqlFilter, you can create your calculated fields in the report editor. You might want to just create a calculated field that contains the entire sql filter so you can make sure you know exactly what's in there. In your report, create a calculated field as follows:

    sqlFilter = a5_getReportSQLFilter(.t.)

    Then drag the newly created sqlFilter calculated field on to your report. Preferably in the header region so it only prints once. When you know for sure what a5_getReportSQLFilter is returning, you can experiment using the Interactive Window to see what kind of expressions you need in order to extract both dates. Are you familiar with the Interactive Window? If not, it really comes in handy for testing your Xbasic code. I think the from date will be very similar to what I showed you. Look at one of the other character extraction functions to see how to extract the last part of a string. I know substr can do it, but you would have to tell it exactly what character to begin extracting from and how many characters to extract. I prefer to use a function like extract_string that searches for a beginning and ending string to extract from.

    Let me know what you find and we can go from there. An alternative to this method is just passing the from date and to date as arguments, but there are still a number of steps involved. You will use a5_GetArgumentValue in the calculated fields expression to pull the data out. For example:

    rptFromDate = a5_GetArgumentValue("fromDate")
    rptToDate = a5_GetArgumentValue("toDate")

  9. #9
    Member
    Real Name
    Tim Barker
    Join Date
    May 2013
    Posts
    56

    Default Re: Retrieving and printing passed variables on the report

    That did the trick. I just defined two calculated fields as...

    rptFromDate = a5_GetArgumentValue("fromDate")
    rptToDate = a5_GetArgumentValue("toDate")

    ...and it is returning the date values I entered.

    Thanks so much for your help. It's really appreciated !!

    Tim

  10. #10
    Member
    Real Name
    Garry Allen
    Join Date
    Oct 2011
    Location
    Kingston, ON, Canada
    Posts
    119

    Default Re: Retrieving and printing passed variables on the report

    Hi Tim
    You must be smarter than I am. I have the same situation. My report has 2 defined arguments - :dStart(D) and :dFinish(D)
    I tried defining a New Calculated Field called rptFromDate and put in a5_GetArgumentValue("dStart") as the definition but it says Invalid or incomplete expression.
    Is there something you didn't mention?
    Thanks

  11. #11
    Member
    Real Name
    Tim Barker
    Join Date
    May 2013
    Posts
    56

    Default Re: Retrieving and printing passed variables on the report

    Here are the calculated values I defined in the Report Builder by going to Report --> Calculated field...

    rptFromDate = a5_getargumentvalue("fromDate")
    rptToDate = a5_getargumentvalue("toDate")

    In the Tabbed UI Builder on the Tabbed UI Buttons tab, I inserted a Report component. Then under the Report Definition section of the Tabbed UI Button/Hyperlink Properties,
    I clicked on the three dots and then entered this in the SQL Filter field: LOGS.log_date>=:fromDate AND LOGS.log_date<=:toDate

    I then clicked the Define Arguments button and defined two arguments called fromDate and toDate. I then set both of them by clicking the Set Argument Value button and then entered <Prompt at Run-time> in the Argument value field.

    That was all I did...

    Hope this helps

    Tim

Similar Threads

  1. Tabbed UI a5_ajax_callback Javascript jQuery variables passed to Xbasic
    By Davidk in forum Application Server Version 11 - Web/Browser Applications
    Replies: 3
    Last Post: 01-28-2013, 06:00 PM
  2. Printing From/To Dates on Report (Global Variables)
    By Rich Hartnett in forum Application Server Version 11 - Web/Browser Applications
    Replies: 0
    Last Post: 10-06-2012, 03:03 AM
  3. Retrieving the page variables to Grid in Mozilla Firefox and Google chrome!!!!
    By jyothi28.g in forum Application Server Version 11 - Web/Browser Applications
    Replies: 0
    Last Post: 09-07-2012, 08:31 AM
  4. Retrieving the variables from an Xdialog
    By Mark Pearson in forum Alpha Five Version 9 - Desktop Applications
    Replies: 3
    Last Post: 06-29-2009, 04:35 PM
  5. form variables not printing?
    By L. McIntosh in forum Alpha Five Version 5
    Replies: 10
    Last Post: 11-28-2004, 11:24 AM

Posting Permissions

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