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

Question

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.

Uptotheminute

Answer 1

My experience is with older versions of Crystal (8,9) - I've no idea what the file formats look like for more recent versions. However, it's worth opening the files up in a text editor just in case, but for the file formats I've seen, the query text is not accessible this way.

If I remember correctly, some versions of Visual Studio 2003 came with tools for manipulating Crystal .rpt files (but I guess this isn't of much use to you, since if you had this already, you wouldn't be asking!).

It's not a very imaginative suggestion, but perhaps your quickest route would be to download the 30-day trial version of the current Crystal Reports, and see if that will open the files for you.

Antabuse

Answer 2

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.

Tattletale

You sir are a ninja. Just to point out I was trying to do this for subreports aswell. Well, it's not supported. Your only option is to save the subreports outside the main report and open them as ReportDocuments, this way you can access the InterOp classes. Oh, and don't forget to add CrystalDecisions.ReportAppServer.ClientDoc, CrystalDecisions.ReportAppServer.Controllers and CrystalDecisions.ReportAppServer.DataDefModel, else it just won't compile. – Evelyn

Answer 3

In "Crystal Reports ActiveX Designer Design and Runtime Library" (craxddrt.dll), the Report.SQLQueryString property will do what you want.

I can't seem to find an equivalent property in the .Net SDK, and believe me, I've been looking.

** edit **

It appears that one can make use of the In-Process RAS Server to get this information:

CrystalDecisions.ReportAppServer.DataDefModel.CommandTableClass.CommandText

Irrespirable

Answer 4

The other way around this is if you can run the reports, you can hook up SQL Profiler to your DB and capture the incoming SQL on the database side.

Chapiter

Answer 5

JoshL's answer worked for several of my reports, but not all of them. The following method, using ReportClientDocument.RowsetController.GetSQLStatement, was able to extract some of the queries that the other method missed.

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);

    var controller = doc.ReportClientDocument.RowsetController;

    var groupPath = new CrystalDecisions.ReportAppServer.DataDefModel.GroupPath();
    string temp = String.Empty;
    string commandSql = controller.GetSQLStatement(groupPath, out temp);
    //TODO: do something with commandSql
}

Vulgarism

Is the CrystalDecisions namespace available only from the SDK, or can I find it somewhere in the Crystal Reports 2016 application? Is there any documentation for these tools? – Vespid

Answer 6

In the Visual studio, select the .rpt file and Go to field explorer, right click on DatabaseFields. Click on SQL query option to view the query.

Bacterium