How can I retrieve the SQL SELECT statement used in an Crystal Report?

Question

I am currently working on a program in C# that allows our users to run, view and export a batch of Crystal Reports. The reports were made using the Crystal Reports 2008 GUI. One of the main reasons for doing this is to allow us to preserve hyperlinks when the Crystal Report is exported to PDF. My program does this by exporting to rtf, then converting the rtf to a pdf. If anyone knows of a less convoluted method of preserving hyperlinks when converting to PDf I'd love to hear it, but that's not my current question.

I have done a lot of tests on how to optimize my program to make the export take as little time as possible. From what I've seen, having the application query for the data, then binding the resultset to the Crystal Report is by far the fastest method. My problem is that I can't hard-code the queries into the program, they need to be retreived from the Crystal Report itself.

In Crystal Reports 2008, there is an option called "Show SQL Query" under the Database menu. This brings up a window with the SQL query used for the given report. This is exactly what I need to get my hands on from within my application. I've loaded a crystal report and, while debugging, traversed the ReportDocument object trying to find the query, but no luck.

So, my question is; is there any method available that would allow me to pull out the query used by a given Crystal Report?

Answer 1

I realize this is a very old question, but thought I would offer an alternative for those who stumble across this but need a framework target of 3.5 (dynamic is not available in 3.5).

You will need the following references for this solution to work.

using CrystalDecisions.ReportAppServer.DataDefModel;
using CrystalDecisions.CrystalReports.Engine;

Then just access the ClientDoc interface with the following and return a list of Command Text strings.

    private static List<string> GetCommandText(CrystalDecisions.CrystalReports.Engine.ReportDocument report)
    {
        var rptClientDoc = report.ReportClientDocument;
        return rptClientDoc.DatabaseController.Database.Tables.OfType<CommandTable>()
              .Select(cmdTbl => cmdTbl.CommandText).ToList();
    }

Answer 2

Alright, so dotjoe gave me all of the hints that I needed to work this out. The following code can be used to pull the command text from a crystal report.

public string getCommandText(ReportDocument rd)
{
    if (!rd.IsLoaded)
        throw new ArgumentException("Please ensure that the reportDocument has been loaded before being passed to getCommandText");
    PropertyInfo pi = rd.Database.Tables.GetType().GetProperty("RasTables", BindingFlags.NonPublic | BindingFlags.Instance);
    return ((dynamic)pi.GetValue(rd.Database.Tables, pi.GetIndexParameters()))[0].CommandText;
}

It looks a bit messy, but it makes some sort of sense when you start wading through it. Basically, it uses reflection to get the value of the CommandText property, with a little dynamics thrown in to get past the dynamic properties in the ReportDocument.

This is pulling the command text for me, but I have had no time to do any tests on the code. I'm sure I'll be making some tweaks once I've had the time to work with this. I have no clue what happens with reports that do not use "SQL Commands". I'll post a comment once I've tested this further.

• Scott

P.S. This requires that you reference the standard reflection/dynamic libraries, as well as the following Crystal Report libraries:

crystaldecisions.reportappserver.datadefmodel

crystaldecisions.crystalreports.engine

crystaldecisions.shared