Reporting on complex SQL database

[swright]

We have an administration system (Blackbaud) which uses a Sybase SQL Anywhere server for the backend. We also have ODBC access to the database, and complete docs on the table and linking structure. We have Crystal 7.0 to report with.

The problem is this: The database has about 30 "tables" defining values such as countries, states, relationship type, address type, phone type, etc., which are all customizable. However, the various tables which appear separate within the application are actually condensed into two tables called "code tables" and a field is used to determine which "table type" the record belongs to. ie:

So when building a report, I may need to select only those records of relationship type="x,y,z", and for those records, select address records of address type="q,r,s", along with phone type="t,u,v", where each of the select criteria all reside in a single table, with only a "table type" field to distinguish what table the record belongs to.

To put it another way, I need to do this:

Select PARENT where type = x,y,x and table_type=rel_type
select address where type = q,r,s and table_type=addr_type
select phone where type = t,u,v and table_type=ph_type

etc.
I have been able to accomplish *some* of the above by using a different subreport for each table_type, but the number of subreports is getting out of hand, and I still cannot get all the data I need. Also, the report is now taking an excessively long time to run due to the subreports. (~10 minutes for 45 pages).

Any ideas of pointers to SQL resources would be appreciated.

Thanks

 

[GJParker]

SWright

I'm not sure I fully understand your situation an example of you r table structures would help, but a couple of things come to mind.

1. You could create Views in the database for each table_type in your code tables and link these into your main report.

2. Link the Code Table into your report as a different alias and set your selection criteria accordingly.

HTH Gary Parker
Systems Support Analyst

 

[swright]

Thanks for the reply, Gary.

Option 1 is not available to us, as we have readonly access only via the ODBC connector, and attempting to add views to the database would probably cause us grief when attempting to get support on the software, since we often need to send the database to Blackbaud for troubleshooting. We are already on thin ice with support because we modify some of the built in crystal templates used by the applications.

Option 2 however sounds interesting - I will give it a try and report back.

On a different note, is it worth upgrading to Crystal v9 from v7? Are we likely to see any improved features wrt speed, especially with subreports? Currently our two largest reports take >30 minutes to run, which seems excessive for a 25-30 page report. The report is somewhat insane, as it has a 4x4 grid (using label layout), and within each box of the grid is a report containing 7 subreports.

 

[GJParker]

There are a number of neat new features in CR9 which may be useful to you, for instance the abililty to use SQL commands as formula fields and insert these directly into your report i.e. SQL select for Country, State, Address etc.

CR9 also allows you store these SQL commands in a repository database and reuse them across different reports.

Another couple of Ideas had about your situation are :

Link Your Sybase tables into and Access database and create a query in access to pull all of your information into a view and use this to report on.

Or

If you know anyone who has Programming skills get them to write something to extract the data from sybase and copy it into an single Access table (Create your own Datamart of customer info).

You could then redesign your reports to run off one table and speed up the reports significantly.

Regards Gary Parker
Systems Support Analyst

 

[lyanch]

This is actually much easier than you think.. i do it all the time. For each place you want to look up a code... (whether you need criteria against it or not) add another instance of the code table. Each instance of the code table will link to a different key field. When you try to add a table twice, crystal will automatically ask you for an alias.

ie

Parent.type = codetable.id
address.type = codetable2.id <= (this is the second instance of codetable aliased to codetable2)
phone.type = codetable3.id <= (ditto but third instance)


I usually alias them so I know which one I am working with for sure. For instance I would alias the code table to addresstype instance to addresstypecode

Hope this helps.

Lisa