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

Execute Crosstab Query in Xbasic - How Do I Display Results?

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

    Execute Crosstab Query in Xbasic - How Do I Display Results?

    I am testing writing an Xbasic function that executes a crosstab query on my Access database. This means there can be a different number of columns in the result set.
    The query is successfully executing without an error and is returning a result set. I know this because right now I have the function just giving me a message that shows the result in the first column on the first row. My question is whether there is some Alpha function that will display all of the results?

    Here is the end of my xBasic code (after opening connection, etc.) which IS successfully giving me a message with the result in column one, so I know the query produced results and did not object to the TRANSFORM, etc..

    Code:
    sql = "TRANSFORM Max(STAGE_CODE) AS MaxOfSTAGE_CODE SELECT JOB_CODE FROM QryJobStagesPastSixMonths GROUP BY JOB_CODE PIVOT [WEEK_DATE];"
    cn.Execute(sql)
    
    dim rs as sql::resultset
    rs = cn.ResultSet
    
    dim columnOneResult
    columnOneResult = rs.data(1)
    
    cn.Close()
    
    dim msg as c 
    msg = "Column 1 result is: " + columnOneResult
    msg = js_escape(msg)
    dim jscmd as c 
    jscmd = "alert('" + msg + "');"
    CrosstabTheResults = jscmd
    Carol King
    Developer of Custom Homebuilders' Solutions (CHS)
    http://www.CHSBuilderSoftware.com

    #2
    Re: Execute Crosstab Query in Xbasic - How Do I Display Results?

    BTW, I also tried copying and pasting that query into a report using the Custom SQL feature but it doesn't like finding TRANSFORM at the beginning of the sql statement. I saw in some other thread that someone said they had successfully produced a report using the TRANSFORM query, but they didn't show their query in that thread. I'm not sure how the report would end up knowing how to display a different number of columns each time, anyway, so gave up. I thought that if I can get the crosstab result set to display on a page, then people can just print the page... thus my post above.
    Carol King
    Developer of Custom Homebuilders' Solutions (CHS)
    http://www.CHSBuilderSoftware.com

    Comment


      #3
      Re: Execute Crosstab Query in Xbasic - How Do I Display Results?

      I think nobody has an answer to this. I just want to display the results of my crosstab query. I found stuff to get the results to XML, which I did because I can see the XML using debug, but don't have a clue what to do with those XML results. I also tried the wiki example for sending sql query results to Excel so I could display them. Here is link to that example:
      http://wiki.alphasoftware.com/Office...+in+Version+11

      I copied that example exactly and it did create an Excel file, but it was full of gibberish. AND it produced this error when I ran it:
      Error executing Ajax callback function 'TryExcelExample' Error reported was: command: sys_open(DocumentFile) Not supported
      Carol King
      Developer of Custom Homebuilders' Solutions (CHS)
      http://www.CHSBuilderSoftware.com

      Comment


        #4
        Re: Execute Crosstab Query in Xbasic - How Do I Display Results?

        Carol

        I will be dealing with getting xtabbed (pivotted) data from my SQL database to A5 in the next week or so.

        Not sure if this will help you, but I remember seeing some thread on this board where it seemed as if the poster was using 1 grid, to bring in any dataset from their datasource (I think it was SQL) - without regard to the specificity of the columns in the dataset.

        If I decide to procrastinate on my work, I'll try to look for it later....

        I think they may have been using Stored Procedures from SQL.... I'm not saying that their solution will totally address your needs, but I think their solution may have some tidbits that may help you.
        MSQL since 2010
        A5V11 since Feb 2012

        Comment


          #5
          Re: Execute Crosstab Query in Xbasic - How Do I Display Results?

          Thank you, Marion. I'll be interested to see that. I think I'm now in pursuit of getting the results to an Excel worksheet and opening it. I think that just displaying them in a grid will still give me problems getting it into a report. If I can get the results to Excel, then users can manipulate as desired and print it. It's a list of their jobs in progress and they assign a stage (or phase) number to each job each Monday (date). My pivot is to list the jobs down first column and spread the Monday dates across top as column headings. The stage numbers are in the job rows in each date column and it shows at a glance how jobs are progressing.
          Carol King
          Developer of Custom Homebuilders' Solutions (CHS)
          http://www.CHSBuilderSoftware.com

          Comment


            #6
            Re: Execute Crosstab Query in Xbasic - How Do I Display Results?

            great - report back if you can on the success/process of getting the result to an Excel spreadsheet.
            MSQL since 2010
            A5V11 since Feb 2012

            Comment


              #7
              Re: Execute Crosstab Query in Xbasic - How Do I Display Results?

              Hi Carol,

              I am using PIVOT (wich is the SQL command for crosstab) and I display the data directly in a grid.

              The number of columns is fixed. In fact I force it to be fixed (using SQL).

              My columns are weeks, like in your case. Getting a list of year weeks in SQL is far from easy, but can be done. In Access....


              As you will be displaying the next yy weeks, and maybe a few past zz weeks, the number of columns is fixed.

              But all this (moving forward as the year advances) need advanced SQL.

              Regards,

              Comment


                #8
                Re: Execute Crosstab Query in Xbasic - How Do I Display Results?

                Roger, in my CHS Access application, I have done exactly as you describe, with fixed columns, and producing a report, etc. And, yes, I did a bunch of advanced SQL to get the Monday weeks. Upon moving my app to the web, I decided that I don't want those columns to be fixed (probably feeling too tired), but I don't really need them to be. I am just querying the past 6 months to date and just want the crosstab results to diplay, whatever the number of columns. But, who knows, I may have to go get my queries from the CHS Access app, and recreate them for the web app with fixed columns.
                Carol King
                Developer of Custom Homebuilders' Solutions (CHS)
                http://www.CHSBuilderSoftware.com

                Comment


                  #9
                  Re: Execute Crosstab Query in Xbasic - How Do I Display Results?

                  Why not simply re-using your existing SQL code to feed the grid?

                  Comment


                    #10
                    Re: Execute Crosstab Query in Xbasic - How Do I Display Results?

                    Would take a long explanation, Roger. Am changing some things. Still just trying to figure out how to the SQL results to an Excel spreadsheet as suggested here: http://wiki.alphasoftware.com/Office...+in+Version+11
                    Carol King
                    Developer of Custom Homebuilders' Solutions (CHS)
                    http://www.CHSBuilderSoftware.com

                    Comment


                      #11
                      Re: Execute Crosstab Query in Xbasic - How Do I Display Results?

                      I agree this is the way to go, if you are really decided for the Excel export.

                      There are other ways to export to Excel, but you would loose the format every time you export...

                      Still I see a lot of advantages in using a grid, you can make the whole thing a lot more interactive.

                      You can link the crosstab (PIVOT) grid to the planning data (with a linked grid that shows as a pop up window) and then change the planning from the grid, or gather more info from a particular project, etc.

                      Regards,

                      Comment


                        #12
                        Re: Execute Crosstab Query in Xbasic - How Do I Display Results?

                        Yep, Roger, I think I'm going to have to give up on figuring out how to display whatever number of columns my crosstab query returns. I have found that I can paste the transform/pivot (crosstab query) into a report as a Stored procedure and the report works, which will be great when I get to wanting to display financials in a fixed 12 month spread. I guess, in this case, I'll probably have to go back to my queries and force a fixed number of columns like I did in my Access app before. Once, I've done that, I'm sure I'll be able to open the data in a grid as well.
                        Carol King
                        Developer of Custom Homebuilders' Solutions (CHS)
                        http://www.CHSBuilderSoftware.com

                        Comment


                          #13
                          Re: Execute Crosstab Query in Xbasic - How Do I Display Results?

                          Hello to all,

                          I followed this thread with high attention, but unfortunately I did not get the full picture.

                          Where can I get information about "feeding the grid" from an XBasic SQL query. (Not from a "grid based on a query component) or
                          as suggested feeding it from a stored procedure.


                          My maybe amateurish approach - not knowing anything better - was to run the XBasic query and fill a result table, then open the result table with a grid component.
                          That works fine as long as there is only one user, but not if multiple users might use the Query simultaneously with different input values it will not.


                          I'm still at a loss how to display the result from a multi-table SQL query immediately in my grid component.

                          Any pointer is appreciated.

                          Eriks

                          Comment


                            #14
                            Re: Execute Crosstab Query in Xbasic - How Do I Display Results?

                            Eriks, I wish I had come up with an answer that would help you, but I gave up and just did a report using a fixed set of columns instead of displaying a grid.
                            Carol King
                            Developer of Custom Homebuilders' Solutions (CHS)
                            http://www.CHSBuilderSoftware.com

                            Comment


                              #15
                              Re: Execute Crosstab Query in Xbasic - How Do I Display Results?

                              Carol

                              just to be exact, my problem was not the "variable set of columns", which was your initial question, but rather how to change the query on the fly to show different results (in the same set of columns).
                              In other words how do I display "rs = cn.ResultSet" inside of a grid, without (re)-loading it first into a table.

                              Thanks

                              Comment

                              Working...
                              X