Reports: Active Tables, Pasive Tables, SQL Joins

[Scholin]

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.

[FrankR]

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.

[Scholin]

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?

[FrankR]

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

What database are you using?

[Scholin]

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

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

[pja67]

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

[Al Buchholz]

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.

[pja67]

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

[Al Buchholz]

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

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.

[Scholin]

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.

<%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.

[Peter.Wayne]

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

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.

[pja67]

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

Michael - the code worked great, thanks!

[Scholin]

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.

[frankbicknell]

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?

[Scholin]

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.