Can you rename or alias a column on a report? 1

[RexHeadlong]

Hi, all.

I am using Crystal Reports Developer 8.5, MS SQL Server 2000, and Visual C++ 6.0

Background: In my .rpt file, the underlying query joins a table to itself, and displays the same column twice in the report. The underlying query looks somewhat like this:

select m.part_number, a1.col1, a2.col1
from table1 m inner join table2 a1 on m.part_number = a1.part_number and a1.flag = 0
inner join table2 a2 on m.part_number = a2.part_number and
a2.flag = 1

If table1 has a row in it where part_number = "XXX"
and table2 has 2 rows in it:

part_number   col1   flag
XXX            13      0
XXX            24      1

then my query should return:

part_number   a1.col1  a2.col1
XXX            13        24

The problem: The above works and is fine in print-preview, print, and most exports. But, when I export this report to an Access database (presumably other databases as well), I get an error because Crystal is trying to create a table that has two columns with the same name.

Is there any way in the report designer to rename or alias one of the column names to a different name so that I can export this report to an Access database?

Thanks for any help, sorry this was longwinded.

 

[dgillz]

In the report design window, just edit the text field containing "Coll" which crystal put there. Right click, edit text object, and enter a different name. Then you should be able to export to Access. Software Support for Macola, Crystal Reports and Goldmine
dgilsdorf@mchsi.com

 

[RexHeadlong]

Thanks for the reply.
The text objects which serve as the column headers are already named differently. When exporting to Access, it is the name of the field object, not the text object, which is used to create the Access column name. And, a "DE_" is prepended to the name, so the final column name in the Access database would be DE_col1. So, what is happening is that Access is trying to create a table with two columns named DE_col1.

 

[Ngolem]

I have used table aliasing with mixed results.

For example: I created a subreport with aliased tables that worked 100% when launched directly from Crystal 8.0 itself. But when launched from a VB app using OLE Automation it failed to run...the problem after hours of debugging was that OLE Automation (it was an older VB app so I could not change the launching of the reports) did not support aliased tables in a subreport.

It would not surprise me if aliased tables were not supported in exporting....perhaps you can make the second data set come from a subreport instead.

Jim

JimBroadbent@Hotmail.com

 

[rogar]

Try putting the second instance of "col1" into your report as a formula and see how the export to access handles that.

 

[RexHeadlong]

rogar: Your suggestion works beautifully! I can name the formula anything I want.

Thanks for the assist!