Seeing SQL Source
Printed From: Crystal Reports Book
Category: Crystal Reports 9 through 2020
Forum Name: Technical Questions
Forum Discription: Formulas, charting data, Crystal syntax, etc.
URL: http://www.crystalreportsbook.com/forum/forum_posts.asp?TID=9579
Printed Date: 03 May 2024 at 10:35pm
Topic: Seeing SQL Source
Posted By: Brian
Subject: Seeing SQL Source
Date Posted: 01 Apr 2010 at 10:04am
|
I am using Crystal Reports 11.5. We have quite a few reports created in earlier versions of Crystal Reports. When I open the reports I am not able to see the data source for the reports. Fortunately a co-worker has a much earlier version of Crystal. We opened the reports with that version and were able to see the stored procedure name to which the report was querying. How do I see the datasource of these older reports in Crystal 11.5? ------------- -Brian |
Replies:
Posted By: DBlank
Date Posted: 01 Apr 2010 at 10:22am
|
Database > Set Database Location
Shows the Source location and the source table (or view or SP) names. Make sure to expand the properties to see if there is an 'overridden qualified table name' hidden in it.
|
Posted By: Brian
Date Posted: 01 Apr 2010 at 11:23am
|
Thanks for the quick reply, but that doesn't work for these reports (I've tried two of them now). In both cases, Database->Set datasource location is not available until you log into the database with Database Expert and select a datasource. It is grayed out until I do so.
The problem is that I don't know which data source to select. It seems a bit silly that older versions of CR show this, but newer versions don't. ------------- -Brian |
Posted By: DBlank
Date Posted: 01 Apr 2010 at 11:26am
| So you are trying to upconvert these reports to v11.5 but are losing the DB connection so you are trying to see what they should be so you can re-attache the DB after you move it into the new version? |
Posted By: hilfy
Date Posted: 02 Apr 2010 at 4:44am
|
This issue stems from the fact that the internal structure of the .rpt file changed dramatically with version 9. Prior to Crystal 9 you could directly edit the SQL that was generated by Crystal, from 9 forward you can't. Instead you have to create a Command in the Database Explorer.
From experience I've found that Crystal XI is not able to correctly interpret the SQL that was manually modified in earlier versions on Crystal. If the stored procedure is the only datasource in the report, this is probably why you can't get to the connection information.
-Dell ------------- Proviti, Data & Analytics Practice http://www.protiviti.com/US-en/data-management-advanced-analytics - www.protiviti.com/US-en/data-management-advanced-analytics |
Posted By: Brian
Date Posted: 02 Apr 2010 at 5:09am
You are pretty close  . These reports are tied to an ESRI (GIS)[Graphical Information System] database that we are making changes to. We need to know what data objects they access to assess how the changes might affect them.
I haven't had a lot of experience with Crystal Reports, but it seems to me a fundamental issue with a report is knowing from where it is obtaining its data.
Thanks, ------------- -Brian |
Posted By: Brian
Date Posted: 02 Apr 2010 at 5:19am
|
Thanks Hilfy, that makes sense.
We will be careful to hold onto an old version of Crystal Reports so that we can get this information. This goes beyond what I am doing right now. We have quite a few reports, not just in my area, but across the company. What happens if any of our reports 'breaks' two years from now and we only have current versions of Crystal Reports with which to repair it? We will be spending a tremendous amount of time trying to reconstruct the data sources.
I spent several hours spelunking the web on this issue and haven't found a solution other than retain an old version of Crystal Reports (my solution). ------------- -Brian |
Posted By: hilfy
Date Posted: 02 Apr 2010 at 5:20am
|
If you have access to compile code in Visual Studio, I have a sample Crystal Reports Documenter application on my website at http://www.geocities.com/geekgurl7/sample_code.html - http://www.geocities.com/geekgurl7/sample_code.html . This might help you get the information you're looking for.
You could also try exporting the report to Report Definition to see if that will get you what you're looking for.
-Dell ------------- Proviti, Data & Analytics Practice http://www.protiviti.com/US-en/data-management-advanced-analytics - www.protiviti.com/US-en/data-management-advanced-analytics |
Posted By: Brian
Date Posted: 02 Apr 2010 at 5:42am
|
Once again thanks Hilfy. I just now tried exporting to report definition and some of the other formats, but the resultant files unfortunately don't include anything about data sources. I have bookmarked your code, but it seems my luck continues to run poor. I have VS2008 and BO XI, which you warn don't work in combination.
My thanks to you and DBlank for your help. ------------- -Brian |