SQL Conversion of Client Report with standalone tables 1

[paulosinuk]

Hi,

I have a M/S Access based report that needs conversion to M/S SQL 7.0. I proceed through the convert driver routine but when I generate my report fields from a standalone table are not displayed. I'm sure the conversion is working correctly.

I am using a ODBC source I've configure becuase the SQL driver through up some spurious error message when I tried to convert the report.

The report contains references to a standalone table that does not link to any of the tables used to report detail. The purpose of the table is to store one record containing label names that are specified by the user in my application to give an element of tailorisation.

So for instance:

Label Data
Site Portsmouth,U.K

The purpose is to allow the user to specify a label, in this case site that sits next to the data field as a heading. It all works great in the original Access database.

I've tried passing the label to the report as a database field, converting the field to a 'totext' formula and establishing it as a variable. In all instances the field is not displayed, yet if I browse the field data the record is present.

I've verified the database, checked the source and confirmed that it's not linked to any other table. There are no record, group or suppression formulas in place.

If I create a new SQL report and display information from two fields from standalone tables, I only ever get the result of the first table displayed, regardless of placement on the page.

When I view the SQL source used in the query I can't find any reference to fields from this table. It seems like any table not linked to the primary gets ignored.

I am using Crystal 7.0

Help!

 

[kenhamady]

My experience is that Crystal doesn't display values from standalone tables, because the are not part of the results. I am more surprised that this field displayed in MS Access.

You could create a subreport to print this record. I have heard others say that if the table has one record it doesn't need to be linked, but I haven't been able to verify this.

How were you connecting to the MS access tables originally, via ODBC or directly to the MDB. Ken Hamady

http://www.kenhamady.com/

Crystal Reports Training and a
Quick Reference Guide to VB/Crystal

 

[paulosinuk]

I was originally connecting to the MDB directly and was able to utilise a standalone table to display the labels, since transferring the report to SQL the standalone table seems to be ignored.

I'll do some testing with utilising a subreport to print the labels but the problem is more their location, a label could be used in a formula or could be in any number of locations on the page.

Any suggestions you can make would be extremely useful

 

[paulosinuk]

Ken your a star!

I did a little reasearch using a sub report that sits in the header of the primary report and is used to read the values from the standalone table and pass them to variables. from there it's simply a matter of declaring the variable in place of the standalone table fields.

Thanks for the help.