Alpha Video Training
Results 1 to 15 of 15

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

  1. #1
    "Certified" Alphaholic kingcarol's Avatar
    Real Name
    Carol King
    Join Date
    Jun 2010
    Posts
    1,527

    Default 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. #2
    "Certified" Alphaholic kingcarol's Avatar
    Real Name
    Carol King
    Join Date
    Jun 2010
    Posts
    1,527

    Default 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

  3. #3
    "Certified" Alphaholic kingcarol's Avatar
    Real Name
    Carol King
    Join Date
    Jun 2010
    Posts
    1,527

    Default 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

  4. #4
    Member
    Real Name
    Marion Taylor
    Join Date
    Jan 2012
    Location
    Hudson Valley, NY
    Posts
    481

    Default 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

  5. #5
    "Certified" Alphaholic kingcarol's Avatar
    Real Name
    Carol King
    Join Date
    Jun 2010
    Posts
    1,527

    Default 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

  6. #6
    Member
    Real Name
    Marion Taylor
    Join Date
    Jan 2012
    Location
    Hudson Valley, NY
    Posts
    481

    Default 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

  7. #7
    Member
    Real Name
    Roger Noguera
    Join Date
    Jul 2010
    Location
    Sant Cugat del Vallès
    Posts
    236

    Default 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,

  8. #8
    "Certified" Alphaholic kingcarol's Avatar
    Real Name
    Carol King
    Join Date
    Jun 2010
    Posts
    1,527

    Default 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

  9. #9
    Member
    Real Name
    Roger Noguera
    Join Date
    Jul 2010
    Location
    Sant Cugat del Vallès
    Posts
    236

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

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

  10. #10
    "Certified" Alphaholic kingcarol's Avatar
    Real Name
    Carol King
    Join Date
    Jun 2010
    Posts
    1,527

    Default 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

  11. #11
    Member
    Real Name
    Roger Noguera
    Join Date
    Jul 2010
    Location
    Sant Cugat del Vallès
    Posts
    236

    Default 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,

  12. #12
    "Certified" Alphaholic kingcarol's Avatar
    Real Name
    Carol King
    Join Date
    Jun 2010
    Posts
    1,527

    Default 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

  13. #13
    Member
    Real Name
    Eriks Blaschka
    Join Date
    Feb 2010
    Posts
    101

    Default 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

  14. #14
    "Certified" Alphaholic kingcarol's Avatar
    Real Name
    Carol King
    Join Date
    Jun 2010
    Posts
    1,527

    Default 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

  15. #15
    Member
    Real Name
    Eriks Blaschka
    Join Date
    Feb 2010
    Posts
    101

    Default 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

Similar Threads

  1. How to execute a ifelse condition in Xbasic in AlphaFive V11?
    By Suvarchala in forum Application Server Version 11 - Web/Browser Applications
    Replies: 4
    Last Post: 12-28-2011, 12:29 AM
  2. Execute Xbasic code from everywhere
    By Marcel Kollenaar in forum Code Archive
    Replies: 7
    Last Post: 04-15-2008, 05:51 PM
  3. Where to execute query from?
    By georgefurnel in forum Alpha Five Version 8
    Replies: 3
    Last Post: 03-17-2008, 12:11 PM
  4. How to display query results in a browse on form?
    By brett s in forum Alpha Five Version 7
    Replies: 0
    Last Post: 11-24-2006, 10:15 PM
  5. Execute report from xbasic
    By Tracy Bost in forum Alpha Five Version 4
    Replies: 7
    Last Post: 10-09-2001, 08:57 AM

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
  •