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

Question

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

I conducted many tests to optimize my program to make exporting as fast as possible. From what I saw, having a query for a data application, linking a result set to Crystal Report is by far the fastest method. My problem is that I cannot hard code the queries in the program, they need to be removed from Crystal Report itself.

In Crystal Reports 2008, the "Database" menu has the option "Show SQL query." This brings up a window with the SQL query used for this report. This is exactly what I need to get my hands out of my application. I downloaded the crystal report and, during debugging, passed the ReportDocument object, trying to find the query, but no luck.

So my question is: is there any method available that will allow me to pull out the query used by this Crystal Report?

Answer 1

Ok, so dotjoe gave me all the hints I needed for this. The following code can be used to display 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 little dirty, but it makes some sense when you start to wade through it. Basically, it uses reflection to get the value of the CommandText property, with little dynamics to get the dynamic properties in the ReportDocument.

This pulls the command text for me, but I did not have time to do any code tests. I am sure that I will make some adjustments as soon as I have time to work with this. I do not know what happens with reports that do not use "SQL Commands". I will post a comment after I have verified this further.

• Scott

PS This requires that you reference the standard reflection / dynamic display libraries, as well as the following Crystal Report libraries:

crystaldecisions.reportappserver.datadefmodel

crystaldecisions.crystalreports.engine

crystaldecisions.shared

Answer 2

I understand that this is a very old question, but I thought that I was offering an alternative to those who came across this, but he needs a target frame of 3.5 (the speaker is not available in 3.5).

For this solution, you will need the following links.

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

Then simply go into the ClientDoc interface with the following and return the list of command line 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(); }