extract raw SQL query from a Crystal Report .rpt file

sql crystal-reports crystal-reports-2008
Greg P picture Greg P · Jul 14, 2010 · Viewed 52.2k times · Source

I've got an .rpt file that I did not write and can find no documentation about. I want to be able to review the SQL that is generated from this report so that I can figure out, well, what data it was pulling and what WHERE clause parameters were used.

I can open it up and see the report layout. But when I select Database|Show SQL Query... the report tries to connect to the data source. The problem is, the data source being used is unknown to me, probably an ODBC connection used by whoever wrote the query. All I can do at that stage is 'Cancel' and I'm back to looking at the report designer.

Am I missing something? Can I get to the SQL query without connecting to the datasource? It seems like viewing the selection criteria shouldn't be dependent on a data connection.

Thanks.

version: Crystal Reports 2008

Answer

Scott Barron picture Scott Barron · Mar 23, 2011

I know that this is an old thread, but I encountered this same problem. Effectively we used to have a database/application that has since been aquired by an external agency.

Although they now have the database/application they don't have access to crystal reports, so we can't just send them the old report that we used to run. Likewise we can't run it as we don't even have the database set up anywhere.... So instead our plan was just to extract the SQL code generated by the report and forward that on.

We experienced the same problem, but the solution is actually pretty simple. If you don't have access to the original data source, just create a new 'blank' datasource (such as an ODBC connection). As long as the connection to the datasource works (i.e. it is some kind of valid datasource this it works fine). When running the 'Show SQL' option point the report to this datasource. As long as you don't try to actually run the report (and only show the SQL) the operation wont fail. This worked for our situation anyway. (Crystal Reports 2008)

(I can give more details if it helps in any way.)

Related questions

I would like to extract the SQL queries from Crystal Report .rpt files, is there a way to do this?

I would like to extract the SQL queries from Crystal Report .rpt files, is there a way to do this? I don't have any of the Crystal Reports products, just the .rpt files.

Read More
How do I UPDATE from a SELECT in SQL Server?

In SQL Server, it is possible to insert rows into a table with an INSERT.. SELECT statement: INSERT INTO Table (col1, col2, col3) SELECT col1, col2, col3 FROM other_table WHERE sql = 'cool' Is it also possible to update a …

Read More
How do I perform an IF...THEN in an SQL SELECT?

How do I perform an IF...THEN in an SQL SELECT statement? For example: SELECT IF(Obsolete = 'N' OR InStock = 'Y' ? 1 : 0) AS Saleable, * FROM Product

Read More