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

Question

so I have a small database with four columns, similar to this one below:

a b c d
x x x x
x x x x 
x x x x

I want to add a new column between B and C. I thought about doing this:

df$new_column <- c(y,y,y,y) 

And then using subset and select to reorder, like:

df <- df %>%
      subset(select = c(a, b, new_column, c, d)

But this doesn't seem to be very practical when dealing with lots of columns and complicated names. Any suggestions to make this process more efficient? Thanks in advance!

Solution 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.

Solution 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.

Solution 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

Solution 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.

Solution 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
}

View more solutions

Comments

[tfrascaroli]

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.