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.
Here's a .Net example of code that grabs the Command Sql from all Crystal Reports in a given directory. It requires the Crystal 2008 .Net SDK to be installed (you can download a trial from SAP):
foreach (string file in Directory.GetFiles("c:\\projects\\Reports", "*.rpt"))
{
Console.WriteLine(String.Format("Processing {0}...", file));
var doc = new CrystalDecisions.CrystalReports.Engine.ReportDocument();
doc.Load(file);
foreach (dynamic table in doc.ReportClientDocument.DatabaseController.Database.Tables)
{
if (table.ClassName == "CrystalReports.CommandTable")
{
string commandSql = table.CommandText;
//TODO: do something with commandSql
}
}
}
To get the SQL as Crystal would build it when running a report, see this link: SAP Note 1280515 - How to extract SQL query from Crystal reports using RAS sdk.
I believe to do this, you need to supply the report parameter values so that Crystal can connect to the database in order to build the SQL. In the example, since a Report Viewer control is used, Crystal can prompt the user for the parameters.