Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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.

like image 440
Steve Stedman Avatar asked Apr 01 '09 15:04

Steve Stedman


People also ask

Is it possible to edit SQL made by Crystal Reports?

Editing a Query Once the Crystal Report is created using a query, to make changes to objects you have to go to edit data source option. When you click on the option, it will open an Edit Query panel where you can add/delete objects, apply filters, etc.

How do I read a Crystal Reports RPT file?

How to Open an RPT File. Crystal Reports files that end with RPT are used with Crystal Reports. Opening the RPT file for free on Windows or macOS is possible with SAP's Crystal Reports Viewer tool. AccountEdge Report files are created by and opened with AccountEdge Pro; it works on Windows and macOS.

Where are .RPT files stored?

RPT files and the RepWrap. mdb file will be located in the \The Raisers Edge 7\Custom folder on the workstation.

How do I open a .RPT file in browser?

Go to http://www.crystalreports.com/crystal-viewer/ in a web browser. This is the download site for SAP Crystal Reports Viewer, a free applications for Windows and macOS that can open . rpt files. If you're using a Mac, you'll need to install Legacy Java Runtime 6 before you can install the Crystal Reports viewer.


2 Answers

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.

like image 161
JoshL Avatar answered Sep 16 '22 13:09

JoshL


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

like image 36
craig Avatar answered Sep 16 '22 13:09

craig