Alpha Software Mobile Development Tools:   Alpha Anywhere    |   Alpha TransForm subscribe to our YouTube Channel  Follow Us on LinkedIn  Follow Us on Twitter  Follow Us on Facebook

Announcement

Collapse

The Alpha Software Forum Participation Guidelines

The Alpha Software Forum is a free forum created for Alpha Software Developer Community to ask for help, exchange ideas, and share solutions. Alpha Software strives to create an environment where all members of the community can feel safe to participate. In order to ensure the Alpha Software Forum is a place where all feel welcome, forum participants are expected to behave as follows:
  • Be professional in your conduct
  • Be kind to others
  • Be constructive when giving feedback
  • Be open to new ideas and suggestions
  • Stay on topic


Be sure all comments and threads you post are respectful. Posts that contain any of the following content will be considered a violation of your agreement as a member of the Alpha Software Forum Community and will be moderated:
  • Spam.
  • Vulgar language.
  • Quotes from private conversations without permission, including pricing and other sales related discussions.
  • Personal attacks, insults, or subtle put-downs.
  • Harassment, bullying, threatening, mocking, shaming, or deriding anyone.
  • Sexist, racist, homophobic, transphobic, ableist, or otherwise discriminatory jokes and language.
  • Sexually explicit or violent material, links, or language.
  • Pirated, hacked, or copyright-infringing material.
  • Encouraging of others to engage in the above behaviors.


If a thread or post is found to contain any of the content outlined above, a moderator may choose to take one of the following actions:
  • Remove the Post or Thread - the content is removed from the forum.
  • Place the User in Moderation - all posts and new threads must be approved by a moderator before they are posted.
  • Temporarily Ban the User - user is banned from forum for a period of time.
  • Permanently Ban the User - user is permanently banned from the forum.


Moderators may also rename posts and threads if they are too generic or do not property reflect the content.

Moderators may move threads if they have been posted in the incorrect forum.

Threads/Posts questioning specific moderator decisions or actions (such as "why was a user banned?") are not allowed and will be removed.

The owners of Alpha Software Corporation (Forum Owner) reserve the right to remove, edit, move, or close any thread for any reason; or ban any forum member without notice, reason, or explanation.

Community members are encouraged to click the "Report Post" icon in the lower left of a given post if they feel the post is in violation of the rules. This will alert the Moderators to take a look.

Alpha Software Corporation may amend the guidelines from time to time and may also vary the procedures it sets out where appropriate in a particular case. Your agreement to comply with the guidelines will be deemed agreement to any changes to it.



Bonus TIPS for Successful Posting

Try a Search First
It is highly recommended that a Search be done on your topic before posting, as many questions have been answered in prior posts. As with any search engine, the shorter the search term, the more "hits" will be returned, but the more specific the search term is, the greater the relevance of those "hits". Searching for "table" might well return every message on the board while "tablesum" would greatly restrict the number of messages returned.

When you do post
First, make sure you are posting your question in the correct forum. For example, if you post an issue regarding Desktop applications on the Mobile & Browser Applications board , not only will your question not be seen by the appropriate audience, it may also be removed or relocated.

The more detail you provide about your problem or question, the more likely someone is to understand your request and be able to help. A sample database with a minimum of records (and its support files, zipped together) will make it much easier to diagnose issues with your application. Screen shots of error messages are especially helpful.

When explaining how to reproduce your problem, please be as detailed as possible. Describe every step, click-by-click and keypress-by-keypress. Otherwise when others try to duplicate your problem, they may do something slightly different and end up with different results.

A note about attachments
You may only attach one file to each message. Attachment file size is limited to 2MB. If you need to include several files, you may do so by zipping them into a single archive.

If you forgot to attach your files to your post, please do NOT create a new thread. Instead, reply to your original message and attach the file there.

When attaching screen shots, it is best to attach an image file (.BMP, .JPG, .GIF, .PNG, etc.) or a zip file of several images, as opposed to a Word document containing the screen shots. Because Word documents are prone to viruses, many message board users will not open your Word file, therefore limiting their ability to help you.

Similarly, if you are uploading a zipped archive, you should simply create a .ZIP file and not a self-extracting .EXE as many users will not run your EXE file.
See more
See less

Retrieving and printing passed variables on the report

Collapse
X
 
  • Filter
  • Time
  • Show
Clear All
new posts

    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
    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.
    "Make it as simple as possible, but not simpler."
    Albert Einstein

    http://www.iadn.com/images/media/iadn_member.png

    Comment


      #3
      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, 03:49 PM. Reason: Moved to v11 WAS forum

      Comment


        #4
        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, 07:02 PM.

        Comment


          #5
          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

          Comment


            #6
            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.

            Comment


              #7
              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

              Comment


                #8
                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")

                Comment


                  #9
                  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

                  Comment


                    #10
                    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

                    Comment


                      #11
                      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

                      Comment

                      Working...
                      X