Alpha Video Training
Page 1 of 2 12 LastLast
Results 1 to 30 of 32

Thread: Reports: Active Tables, Pasive Tables, SQL Joins

  1. #1
    Member
    Real Name
    Michael Scholin
    Join Date
    May 2007
    Location
    Madison, WI
    Posts
    321

    Default Reports: Active Tables, Pasive Tables, SQL Joins

    I have move my v9 web app to v10 with SQL as the backend. I am trying to stay SQL "pure" as possible by avoiding using passive or active link tables for reports. But I am finding this difficult to create reports where I need to use more than one sub-reports of linked tables with one-many relationships.

    When using dbf, Sets were so easy to work with and define. Lookups and calculations using other tables were a breeze. However I am not having much luck using various SQL Views and/or joins to achieve the same functionality. I am sure it is due to my inexperience with SQL and using SQL with Alpha.

    My question is this: I have a report that needs to displays data from several linked child tables but it seems like no matter what type of SQL join I use in a report with more than one sub-report it always displays repeating child data.

    What is the best SQL way to achieve this?

    If I have to use link tables, how much of a performance hit does linked tables cause in a large application?

    Lastly, it seems like passive link tables give a lot of flexibility for reports but how would you refresh these from a web page prior to each time a report is called?

    Any help would be appreciated! Thanks.

  2. #2
    Member FrankR's Avatar
    Real Name
    Frank R
    Join Date
    Mar 2006
    Posts
    210

    Default Re: Reports: Active Tables, Pasive Tables, SQL Joins

    I need to know more about what you're trying to achieve to help, but, as far as your repeating rows, look up the DISTINCT keyword of the SELECT statement.
    Frank R

  3. #3
    Member
    Real Name
    Michael Scholin
    Join Date
    May 2007
    Location
    Madison, WI
    Posts
    321

    Default Re: Reports: Active Tables, Pasive Tables, SQL Joins

    Frank, thank you for responding.

    So for example,I have a patient table {patient}. Each patient "can" have multiple and different tests done { such as, test A, test B, test C} with a series of results for each test. All test data need to be either active linked or updated just prior to running a report.

    In my report, I need to generate a sub report of any test and a table of results for each.

    With Alpha in the past, I would just create a set and it would be no problem. I can create a passive link of the patient table and each test, then create a set which works fine but I have not been able to figure out how to update all the tables prior to each printing from a web.

    If I create a SQL view then I am basically creating a big flat file of all this data and trying to create sub reports on a flat file which I believe is giving me repeating rows in my sub reports.

    If I create an active link of each table and create a set, it is very slow.

    So, that is my initial problem. What is the best way to get the SQL data to the Alpha report generator without costing too much performance loss?

  4. #4
    Member FrankR's Avatar
    Real Name
    Frank R
    Join Date
    Mar 2006
    Posts
    210

    Default Re: Reports: Active Tables, Pasive Tables, SQL Joins

    What database are you using?
    Frank R

  5. #5
    Member
    Real Name
    Michael Scholin
    Join Date
    May 2007
    Location
    Madison, WI
    Posts
    321

    Default Re: Reports: Active Tables, Pasive Tables, SQL Joins

    I am using MS SQL Server 2008 express with Management Studio as my interface.

  6. #6
    Member
    Real Name
    Joe A
    Join Date
    Feb 2009
    Posts
    96

    Default Very slow report performance using (active link) sets

    I too am experiencing very slow performance when using a set of active link tables from my backend SQL database for my reporting (as noted above by a previous user). Can anyone offer a solution to this problem?

    Please note, I am limited to using active links (instead of using SQL tables directly) because of multiple subreports and child tables.

    Any help would be greatly appreciated!
    Joe

  7. #7
    Volunteer Moderator
    Real Name
    Alan Buchholz
    Join Date
    Oct 2000
    Location
    Delavan, Wisconsin
    Posts
    9,644

    Default Re: Reports: Active Tables, Pasive Tables, SQL Joins

    Joe

    If you are reporting only for this data, use passive linked tables and then use a set in Alpha, or use a more complex select statement with a join in it.

    My guess is that the passive linked tables would be the fastest.

    Do as much work as you can in the SQL select statement. Bring across only the columns and rows that are needed.
    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.

  8. #8
    Member
    Real Name
    Joe A
    Join Date
    Feb 2009
    Posts
    96

    Default Re: Reports: Active Tables, Pasive Tables, SQL Joins

    I am working on a web application. If I use passive links for report data, is there a way to add a function that would automatically refresh the data for my users prior to generating output?
    Thanks again,
    Joe

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

    Default Re: Reports: Active Tables, Pasive Tables, SQL Joins

    Quote Originally Posted by pja67 View Post
    I am working on a web application. If I use passive links for report data, is there a way to add a function that would automatically refresh the data for my users prior to generating output?
    Thanks again,
    Joe
    Not that I know of, but I'm working on finding out.

    Maybe you can find out faster than I can. I'll let you know what I find.
    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.

  10. #10
    Member
    Real Name
    Michael Scholin
    Join Date
    May 2007
    Location
    Madison, WI
    Posts
    321

    Default Re: Reports: Active Tables, Pasive Tables, SQL Joins

    Joe,

    If you call your print report from an a5w page, you can use the desktop a5_RefreshPassiveLinkTable(table_i,.f.,.f.) to update your passive link via a web site. For example, here is the code I use for one of my reports. I place this before the print code.
    Code:
    <%a5 
    ' ****** This following code will update the passive link tables - must have full path to work ***
    dim tableList as C 
    dim count as N
    
    dim i as N
    dim pr as P
    dim table_i as C
    
    tableList = <<%txt% 
    C:\USMONSQL\USMON_SQL\Passive Link Tables\pas_iom_tests
    C:\USMONSQL\USMON_SQL\Passive Link Tables\pas_pedicle
    C:\USMONSQL\USMON_SQL\Passive Link Tables\pas_to4
    C:\USMONSQL\USMON_SQL\Passive Link Tables\pas_patient
    C:\USMONSQL\USMON_SQL\Passive Link Tables\pas_client
    %txt% 
    
    count = line_count(tableList)
    for i = 1 to (count)
        table_i = word(tableList,i,crlf())
        pr =a5_RefreshPassiveLinkTable(table_i,.f.,.f.)
    next i
    ' ============== END Passive Update ===========================================
    %>
    If your reports require a lot of subsets then this is the only reliable way (that I have found) to create dbf sets with passive links that can then be used in your reports. Performance wise, it takes about 2-3 seconds to open a report now with SQL tables, where before it took about 1 second when I was using V9 and DBF tables. So not too bad. I have not released my V10/SQL version to the real world yet but so far in the testing stage, it all seems to work.

  11. #11
    "Certified" Alphaholic
    Real Name
    Peter Wayne
    Join Date
    Apr 2000
    Posts
    1,728

    Default Re: Reports: Active Tables, Pasive Tables, SQL Joins

    Quote Originally Posted by Scholin View Post
    I am using MS SQL Server 2008 express with Management Studio as my interface.
    You can get a single large flat file result from SQL, but then you can create groups in the report to display only the tests and the test results. That works the same as subreports and it's actually faster.
    For example, if your table shows:
    Date Test Result
    3/1/2010 Glucose 124
    3/2/2010 Glucose 89
    3/3/2010 Glucose 96
    3/1/2010 Na 140
    3/2/2010 Na 138
    3/3/2010 Na 140

    You can run your report, ordered by Test, then by Date, with a group break by Test. The report would look like this:

    Glucose <- in group header
    3/1/2010 124
    3/2/2010 89
    3/3/2010 96
    Na
    3/1/2010 140

    etc.

  12. #12
    Member
    Real Name
    Joe A
    Join Date
    Feb 2009
    Posts
    96

    Default Re: Reports: Active Tables, Pasive Tables, SQL Joins

    Michael - the code worked great, thanks!

  13. #13
    Member
    Real Name
    Michael Scholin
    Join Date
    May 2007
    Location
    Madison, WI
    Posts
    321

    Default Re: Reports: Active Tables, Pasive Tables, SQL Joins

    Glad to hear that.

    I have not had the time to play around with this but in the newest beta Alpha version 10.5 just released, it "looks" like we may be able to do this with the new optimization of using active links for better performance.

    I don't have any idea if it would be faster or better for reports - but it would seem like they are heading down that path. I believe it is good news for sure.

  14. #14
    "Certified" Alphaholic
    Real Name
    Frank Bicknell
    Join Date
    Feb 2010
    Location
    39.1915° N, 84.4348° W
    Posts
    2,467

    Default Re: Reports: Active Tables, Pasive Tables, SQL Joins

    tableList = <<%txt%
    C:\USMONSQL\USMON_SQL\Passive Link Tables\pas_iom_tests
    C:\USMONSQL\USMON_SQL\Passive Link Tables\pas_pedicle
    C:\USMONSQL\USMON_SQL\Passive Link Tables\pas_to4
    C:\USMONSQL\USMON_SQL\Passive Link Tables\pas_patient
    C:\USMONSQL\USMON_SQL\Passive Link Tables\pas_client
    %txt%
    Would these need to exist on the desktop or are they on the server?

  15. #15
    Member
    Real Name
    Michael Scholin
    Join Date
    May 2007
    Location
    Madison, WI
    Posts
    321

    Default Re: Reports: Active Tables, Pasive Tables, SQL Joins

    Frank,

    It is on the remote server itself. However, I do have the full development software running on that server as well (don't know if that makes a difference or not). But this for a web site only.

  16. #16
    "Certified" Alphaholic
    Real Name
    Frank Bicknell
    Join Date
    Feb 2010
    Location
    39.1915° N, 84.4348° W
    Posts
    2,467

    Default Re: Reports: Active Tables, Pasive Tables, SQL Joins

    Thank you. I am finding it difficult to create a report the way I want by sql views alone so I nay give this a try.

  17. #17
    "Certified" Alphaholic mmaisterrena's Avatar
    Real Name
    Mauricio Maisterrena
    Join Date
    Nov 2006
    Location
    Torreon, Mexico
    Posts
    1,435

    Default Re: Reports: Active Tables, Pasive Tables, SQL Joins

    you can try "[PathAlias.ADB_Path]\Passive Link Tables\pas_client" for better portability
    Cheers
    Mauricio


  18. #18
    Member
    Real Name
    Jon Warner
    Join Date
    Oct 2009
    Location
    South Central Ohio
    Posts
    32

    Default Re: Reports: Active Tables, Pasive Tables, SQL Joins

    Thank you. I am finding it difficult to create a report the way I want by sql views alone so I nay give this a try.
    Frank,

    I had the same problem. Using the passive-link tables and refreshing on demand works pretty well. Just a tip: providing you define a filter on the passive-link table, you can pass filter arguments in a5_RefreshPassiveLinkTable ex:a5_RefreshPassiveLinkTable(table_i,.f.,.f.,args) to get only the data you need.

    Jon

  19. #19
    Member NicholasWieland's Avatar
    Real Name
    Nicholas Wieland
    Join Date
    Apr 2008
    Location
    Huntington, NY
    Posts
    546

    Default Re: Reports: Active Tables, Pasive Tables, SQL Joins

    Sometimes you may have an issue with the database e.g. Quickbooks
    and 2 others connected in an app where there are 1,200,000 records. And to update them takes time 7-10 minutes. So I use a short cut and runs on a schedule I choose.

    1-You need to have the Developers edition on the server where your app is running.
    2- you can create a short cut. Tools > Create ShortCut
    3- It runs a script.
    4- To ZAP the table, and run the query on a schedule at nite to pull those 1,200,000 records and no one even knows it is happening.

    Shortcut-runs and runs without knowledge opens and closes too.
    -TITLE="Please wait while we update Quick Books Data"
    -NOSPLASH
    -MAXIMIZE
    -NOMINMAX
    -NOSYSMENU
    -COMMAND="SCRIPT_PLAY(\"testrunzap\");a5.close()"
    Nicholas Wieland
    LedgerSuite.com Corp
    nnw@edfi.net
    http://www.ledgersuite.com


  20. #20
    Member
    Real Name
    Joe A
    Join Date
    Feb 2009
    Posts
    96

    Default Re: Reports: Active Tables, Pasive Tables, SQL Joins

    I tried the above a5_RefreshPassiveLinkTable code in a report as a Report Event using OnPrintInit, but was unsuccessful. I was successful using the code in an a5w page, is this possible in a report?
    Thanks,
    Joe

  21. #21
    Member
    Real Name
    Michael Scholin
    Join Date
    May 2007
    Location
    Madison, WI
    Posts
    321

    Default Re: Reports: Active Tables, Pasive Tables, SQL Joins

    I use this function for a lot of my reports but they are ALL called from an A5w "print" page for each report running on the server. I am pretty sure that is the only way to do this from the web.

  22. #22
    Former Alpha Employee JerryBrightbill's Avatar
    Real Name
    Jerry Brightbill
    Join Date
    Apr 2000
    Posts
    5,173

    Default Re: Reports: Active Tables, Pasive Tables, SQL Joins

    If you are building reports based on SQL, use SQL datasources. These are saved as part of the database dictionary along with the reports. The database dictionary is then published to the server.

    One advantage is that you can use a named connection just like in a component. A second advantage is the ability to saved the named datasource and use it for any SQL report. A third advantage it that the report will build a temporary passive link table with just the fields in the datasource and then clean up the temp files after the report is run. Since the table is built on demand, the data is always current.

  23. #23
    "Certified" Alphaholic mmaisterrena's Avatar
    Real Name
    Mauricio Maisterrena
    Join Date
    Nov 2006
    Location
    Torreon, Mexico
    Posts
    1,435

    Default Re: Reports: Active Tables, Pasive Tables, SQL Joins

    In terms of performance, which would be the best option?
    Cheers
    Mauricio


  24. #24
    VAR Pat Bremkamp's Avatar
    Real Name
    Pat Bremkamp
    Join Date
    Apr 2000
    Location
    Oregon, USA
    Posts
    2,635

    Default Re: Reports: Active Tables, Pasive Tables, SQL Joins

    I have a client using MySQL who has a situation that is similar to the original question, which is the parent, then about a dozen child tables, all one to many.

    We started out with having all the tables joined, but couldn't figure out how to get around the repeating record problem for sub reports. I tried DISTINCT and GROUP BY, but couldn't get either to work, and scanning() and uniue_key_value() don't seem to work with an SQL data source.

    I started working on the idea of using a union for the child tables and then a join to the parent table. That would have required the same number of columns in each child table to put them together end to end, but they were pretty similar to start with. I think that would have worked and may work in your situation if the test results tables have a similar structure. Never finished that, but I think it would work.

    What we actually ended up with was amazing. My client created a dozen grids, one for each table, lined them up one after the other in a page and printed the page with the javascript command window.print(). I didn't expect it to work, but it does! I expected Alpha to lock up, but it just chugs away.

    I added a little test so, if there are no records, the component doesn't get processed in the header and doesn't get output in the body, but even in the worst case it only takes about 8 seconds to generate the page with both of us clicking submit on the same record at the same time. And the client likes being able to see the results before they print.

    Pat
    Pat Bremkamp
    MindKicks Consulting

  25. #25
    "Certified" Alphaholic
    Real Name
    Frank Bicknell
    Join Date
    Feb 2010
    Location
    39.1915° N, 84.4348° W
    Posts
    2,467

    Default Re: Reports: Active Tables, Pasive Tables, SQL Joins

    Thank you Pat. I will give the javascript window.print() a try. I am currently using snagit but your idea is much more professional and easier.

  26. #26
    "Certified" Alphaholic
    Real Name
    Frank Bicknell
    Join Date
    Feb 2010
    Location
    39.1915° N, 84.4348° W
    Posts
    2,467

    Default Re: Reports: Active Tables, Pasive Tables, SQL Joins

    I am trying to just print a snapshot of the detail view window. I there a way to refine window.print() to do that? If I call this code from the detail view window I get the parent window too.

  27. #27
    VAR Pat Bremkamp's Avatar
    Real Name
    Pat Bremkamp
    Join Date
    Apr 2000
    Location
    Oregon, USA
    Posts
    2,635

    Default Re: Reports: Active Tables, Pasive Tables, SQL Joins

    Frank,

    I believe window.print() prints the current active window. So, if you pop up your detail section in a separate window, you can print just that section. If you open in on the page, you'll get the background.

    Pat
    Pat Bremkamp
    MindKicks Consulting

  28. #28
    "Certified" Alphaholic
    Real Name
    Frank Bicknell
    Join Date
    Feb 2010
    Location
    39.1915° N, 84.4348° W
    Posts
    2,467

    Default Re: Reports: Active Tables, Pasive Tables, SQL Joins

    Thanks for your reply, I will have to experiment a litte more.

  29. #29
    "Certified" Alphaholic mmaisterrena's Avatar
    Real Name
    Mauricio Maisterrena
    Join Date
    Nov 2006
    Location
    Torreon, Mexico
    Posts
    1,435

    Default Re: Reports: Active Tables, Pasive Tables, SQL Joins

    Add
    window.print() + Grouping on Grid + Fussion Charts

    and you get some very cool interactive reports
    Cheers
    Mauricio


  30. #30
    "Certified" Alphaholic
    Real Name
    Frank Bicknell
    Join Date
    Feb 2010
    Location
    39.1915° N, 84.4348° W
    Posts
    2,467

    Default Re: Reports: Active Tables, Pasive Tables, SQL Joins

    Thanks Mauricio, that is a very interesting idea. I will be tackling charts in the near future. Right now I am just trying to print the contents of my popup detail view window. When I call the window.print() function from a button on the grid in the detail view I get the parent grid too. Any ideas regarding that issue?

Similar Threads

  1. Active Link Tables
    By steve745 in forum Alpha Five Version 9 - Desktop Applications
    Replies: 0
    Last Post: 08-03-2009, 11:14 AM
  2. Active Link Tables
    By chadbrown in forum Application Server Version 9 - Web/Browser Applications
    Replies: 4
    Last Post: 08-02-2009, 07:23 PM
  3. Active link Tables
    By Florian in forum Application Server Version 9 - Web/Browser Applications
    Replies: 3
    Last Post: 06-29-2009, 04:52 AM
  4. data persistance to backend active SQL tables issue
    By saljamea in forum Application Server Version 9 - Web/Browser Applications
    Replies: 0
    Last Post: 04-27-2009, 10:34 AM
  5. active link tables and reports (web)
    By moiola in forum Application Server Version 9 - Web/Browser Applications
    Replies: 10
    Last Post: 04-06-2009, 05:18 PM

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
  •