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

Google Charts - Prompting For SQL Agruments

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

    Google Charts - Prompting For SQL Agruments

    I've been playing with the new Google Charts API as shown by Selwyn in the videos here .. http://www.ajaxvideotutorials.com/V1...tingData_1.swf
    This is a fantastic way to add charts to Alpha! I have them working just fine after following the videos.

    I want to take it a step further and filter the SQL query on a date range using SQL arguments which are prompted at runtime.

    When you build the SQL query with the genie and execute it, the date arguments are automatically prompted for like this:

    SQL Query Genie.jpg

    But when I add the arguments in xbasic and run the code, I don't get prompted for them at runtime:

    XBasic SQL Query.jpg

    Can anyone point to to where I'm going wrong?
    Alpha Anywhere v12.4.6.5.2 Build 8867-5691 IIS v10.0 on Windows Server 2019 Std in Hyper-V

    #2
    Re: Google Charts - Prompting For SQL Agruments

    I don't think this is the way it works. In the SQL Genie, the builder doesn't know anything about what you're doing... so it prompts you for arguments.

    In XBasic, you're passing arguments to the SQL server and these arguments should already be established and set. You don't get prompted. (I wouldn't want that SQL prompt anyway... just not pretty).

    I think what you'll need to here is re-arrange things a bit. I'd create a Container Window to capture the dates.

    Then, in onRenderComplete - pop up the Container Window... grab the dates, test for the dates, and then run an Ajax Callback to an XBasic function that contains the code you have in your onDialogInitialize.

    You can get the dates from the e object of the Xbasic function. Once you've got the dates from the e object, set up your arguments and you're done.

    Comment


      #3
      Re: Google Charts - Prompting For SQL Agruments

      David, I like your idea, makes sense. I'll have to play around with that and see how it works.
      Thanks very much for the input.
      Alpha Anywhere v12.4.6.5.2 Build 8867-5691 IIS v10.0 on Windows Server 2019 Std in Hyper-V

      Comment


        #4
        Re: Google Charts - Prompting For SQL Agruments

        Did you ever get this working? - I'm totally frustrated by this, the chart works fine if the SQL is in the on 0nDialogItialize, but as we know this is before the onRenderComplete so we can't access the arguments in the UX to filter the SQL statement. Any clues?
        Insanity: doing the same thing over and over again and expecting different results.
        Albert Einstein, (attributed)
        US (German-born) physicist (1879 - 1955)

        Comment


          #5
          Re: Google Charts - Prompting For SQL Agruments

          Did anyone ever get this figured out? I'm trying to do an ajax callback to a function NOT in the OnDialogInitialize and populate the chart from data that is filtered by a session variable. I'm not having much luck. Any suggestions?? Is it possible to use a function that is not located in the OnDialogInitialize?

          Comment


            #6
            Re: Google Charts - Prompting For SQL Agruments

            No - same boat i'm afraid. - its a real pity as there is so much potential. I went with the AA Charts.
            Insanity: doing the same thing over and over again and expecting different results.
            Albert Einstein, (attributed)
            US (German-born) physicist (1879 - 1955)

            Comment


              #7
              Re: Google Charts - Prompting For SQL Agruments

              I tried those first, but I ran into the issue of not being able to have a blank in the data. The charts I need are target vs actual over a time period to track progress. So the 'actual' data gets filled in weekly and moves down the timeline as we go. The AA charts, unless I did it wrong, would insert zeros for the blanks and make the line graph drop to zero which in this instance, marks a completed project.

              Comment


                #8
                Re: Google Charts - Prompting For SQL Agruments

                I am not an expert in this area, but it seems to me that IF you can set the sql args from a session variable, so as long as you set the arg up in the sql statement - you should be able to filter any data into the charts, even if it is the on dialog init. Put it into an a5w page and call it from a button.
                Then call the a5w page based on what you need. This seems to be no different than running any report.

                Maybe I am missing the whole idea here guys if so I apologize...I know one of the issues were using this outside of the ondialoginit.

                Code:
                function onDialogInitialize as v (e as p)
                dim pUser as p
                dim pUser.userid as c
                a5ws_logged_in_user_values(pUser) 
                'gets logged in user data to pointer 'pUser'.
                
                session.coid = sql_lookup("::name::Connection1","users","userid='"+pUser.userid+"'","company_id")
                dim sql as c
                sql=<<%txt%	
                SELECT DISTINCT base_species_type.Name AS species,  Count(company_phonelog.Species_Type)  AS speciestype,  Max(DISTINCT company_phonelog.Date)  AS maxdate 
                FROM base_species_type base_species_type
                	 INNER JOIN company_phonelog company_phonelog
                		 ON  base_species_type.Name = company_phonelog.Species_Type
                WHERE company_phonelog.company_id = :coid1
                GROUP BY base_species_type.Name 
                ORDER BY  Count(company_phonelog.Species_Type)  DESC
                %txt%
                
                dim sqlArgs as sql::Arguments
                sqlArgs.set("coid1",session.coid)
                dim flag as l
                flag = cn.Execute(sql,sqlArgs)
                dim rs as sql::ResultSet
                rs = cn.ResultSet
                dim template as c
                
                
                template = "['{js_escape(ds.data(\"species\"))}', {ds.data(\"speciestype\")}, {ds.data(\"maxdate\")}, {ds.data(\"speciestype\")}],"
                
                dim txt as c
                txt = a5_mergedataintotemplate(template,rs)
                txt = rtrim(txt,",")
                txt = "[['Species', 'Amount', 'Date', 'Amount']," + txt + "]"
                
                dim js as c
                js = "{dialog.object}._data = " + txt + ";"
                
                e.javascript = js
                
                end function
                So I know this is in the ondialog init - but it does call the args from a session variable.
                And the component is on an a5w page
                so to me the only thing left (for you) is to call it from a button click and as David pointed out set those args first - then run the aw5...


                Also, if you're getting a white page instead of the chart you're expecting you template is probably off. This was the number one booger I fought.
                I had to do a bit of testing, and debugging before I got it to work. I hope this helps in some way.
                NWCOPRO: Nuisance Wildlife Control Software My Application: http://www.nwcopro.com "Without forgetting, we would have no memory at all...now what was I saying?"

                Comment


                  #9
                  Re: Google Charts - Prompting For SQL Agruments

                  Hi Charles. I appreciate you taking the time to respond to my question. What I'm trying to accomplish is to have 1 embedded chart and the chart data be filtered based on a dropdown box on the UX. This works fine if you have an 'OnChange' event on the dropdown and open another UX in a pop up window or tabbed UI, etc. It filters the data with a session variable generated by the dropdown and the chart is rendered. However, what I want to show is a static text box with a chart in it and that chart refresh and update based on the selection in the dropdown box. The projects we work on may have over 50 graphs from this one table. So having pop ups or tabbed UI pages or any other pop up method is undesirable and starts to get cluttered fast.

                  The answer may be as easy as refreshing the chart after each 'OnChange' event from the dropdown, but I can't seem to find the refresh statement to make this happen.

                  Comment


                    #10
                    Re: Google Charts - Prompting For SQL Agruments

                    The only thing I can think of to change a text box close what your saying would be a calculated expression, and I think you could use an if or an iif statement there, possibly to run an xbasic command to pull the info.
                    The other thing you could do is have the text box display data from a specific url (a5w page) and THAT is where you chart would be rendered..
                    Again, just tossing ideas around - good luck! maybe someone else will see a brilliant answer!
                    NWCOPRO: Nuisance Wildlife Control Software My Application: http://www.nwcopro.com "Without forgetting, we would have no memory at all...now what was I saying?"

                    Comment


                      #11
                      Re: Google Charts - Prompting For SQL Agruments

                      I have created many Google visualization charts. Are you looking to select various filters (like date ranges) on a UX and then refresh the chart with a button or on-change event OR pass an argument to a UX and filter the data for the chart accordingly? Based on your response, I will send you an sample UX. I don't use prompts . I attached a sample chart that I created. i also use Google's table object to show the numerical representation of all the data under the graph.
                      Attached Files
                      Last edited by frankiez; 02-17-2016, 04:10 PM. Reason: upload new image

                      Comment


                        #12
                        Re: Google Charts - Prompting For SQL Agruments

                        Frank - that is awesome, are you willing to share the component or how to?
                        Insanity: doing the same thing over and over again and expecting different results.
                        Albert Einstein, (attributed)
                        US (German-born) physicist (1879 - 1955)

                        Comment


                          #13
                          Re: Google Charts - Prompting For SQL Agruments

                          Certainly. I will put Selwyn's Northwind UX example with filters and add the numeric table. i will attach source to show how to perform complex filters regarding my google_charts attachment. Give me a day or two to put it all together.

                          Comment


                            #14
                            Re: Google Charts - Prompting For SQL Agruments

                            Great - thanks - that's been a real pain.
                            Insanity: doing the same thing over and over again and expecting different results.
                            Albert Einstein, (attributed)
                            US (German-born) physicist (1879 - 1955)

                            Comment


                              #15
                              Re: Google Charts - Prompting For SQL Agruments

                              Hey Frank - Any update on this.Cheers Pete
                              Insanity: doing the same thing over and over again and expecting different results.
                              Albert Einstein, (attributed)
                              US (German-born) physicist (1879 - 1955)

                              Comment

                              Working...
                              X