从 Crystal Report .rpt 文件中提取原始 SQL 查询

extract raw SQL query from a Crystal Report .rpt file

提问by Greg P

I've got an .rpt file that I did not write and can find no documentation about. I want to be able to review the SQL that is generated from this report so that I can figure out, well, what data it was pulling and what WHERE clause parameters were used.

我有一个 .rpt 文件，它不是我写的，也找不到相关文档。我希望能够查看从该报告生成的 SQL，以便我可以弄清楚它提取了哪些数据以及使用了哪些 WHERE 子句参数。

I can open it up and see the report layout. But when I select Database|Show SQL Query...the report tries to connect to the data source. The problem is, the data source being used is unknown to me, probably an ODBC connection used by whoever wrote the query. All I can do at that stage is 'Cancel' and I'm back to looking at the report designer.

我可以打开它并查看报告布局。但是当我选择Database|Show SQL Query...报告尝试连接到数据源。问题是，我不知道正在使用的数据源，可能是编写查询的人使用的 ODBC 连接。在那个阶段我所能做的就是“取消”，然后我又回到了报表设计器上。

Am I missing something? Can I get to the SQL query without connecting to the datasource? It seems like viewing the selection criteria shouldn't be dependent on a data connection.

我错过了什么吗？我可以在不连接数据源的情况下访问 SQL 查询吗？似乎查看选择标准不应该依赖于数据连接。

Thanks.

谢谢。

version: Crystal Reports 2008

版本：水晶报表2008

回答by Scott Barron

I know that this is an old thread, but I encountered this same problem. Effectively we used to have a database/application that has since been aquired by an external agency.

我知道这是一个旧线程，但我遇到了同样的问题。实际上，我们曾经拥有一个数据库/应用程序，该数据库/应用程序已被外部机构收购。

Although they now have the database/application they don't have access to crystal reports, so we can't just send them the old report that we used to run. Likewise we can't run it as we don't even have the database set up anywhere.... So instead our plan was just to extract the SQL code generated by the report and forward that on.

尽管他们现在拥有数据库/应用程序，但他们无权访问水晶报表，因此我们不能只向他们发送过去运行的旧报表。同样，我们无法运行它，因为我们甚至没有在任何地方设置数据库......所以我们的计划只是提取报告生成的 SQL 代码并将其转发。

We experienced the same problem, but the solution is actually pretty simple. If you don't have access to the original data source, just create a new 'blank' datasource (such as an ODBC connection). As long as the connection to the datasource works (i.e. it is some kind of valid datasource this it works fine). When running the 'Show SQL' option point the report to this datasource. As long as you don't try to actually run the report (and only show the SQL) the operation wont fail. This worked for our situation anyway. (Crystal Reports 2008)

我们遇到了同样的问题，但解决方案实际上非常简单。如果您无权访问原始数据源，只需创建一个新的“空白”数据源（例如 ODBC 连接）。只要与数据源的连接有效（即它是某种有效的数据源，它就可以正常工作）。运行“显示 SQL”选项时，将报告指向此数据源。只要您不尝试实际运行报告（并且只显示 SQL），操作就不会失败。无论如何，这对我们的情况有效。（水晶报告 2008）

(I can give more details if it helps in any way.)

（如果有任何帮助，我可以提供更多详细信息。）

回答by Greg P

It should be possible to find out some details about the existing datasource, by selecting Database > Set Datasource Location... .

通过选择 Database > Set Datasource Location... ，应该可以找到有关现有数据源的一些详细信息。

As well as enabling you to change the datasource location, this should show you some information about the current datasource, such as which type of datasource is being used, and possibly (dependant on the type of driver) the name of the database. It is likely to be less helpful if (as you surmise) the datasource is ODBC, but if it uses a native driver there may be something useful.

除了使您能够更改数据源位置之外，这还应该显示有关当前数据源的一些信息，例如正在使用哪种类型的数据源，以及可能（取决于驱动程序的类型）数据库的名称。如果（如您所猜测的）数据源是 ODBC，它可能不太有用，但如果它使用本机驱动程序，可能会有一些有用的东西。

回答by LittleBobbyTables - Au Revtheitroad

Without the password, I'm not sure how much you can do. It seems "Show SQL Query" requires to report to run first, then generate the SQL plan.

没有密码，我不确定你能做多少。似乎“显示 SQL 查询”需要先报告运行，然后生成 SQL 计划。

It's not ideal, but you could go to Database > Visual Linking Expert to at least see the tables and how they are joined, and the go to the Record Selection Formula Editor and see what the custom WHERE statements are.

这并不理想，但您可以转到“数据库”>“可视化链接专家”至少查看表及其连接方式，然后转到“记录选择公式编辑器”并查看自定义 WHERE 语句是什么。

回答by Phil B

Viewing the SQL of a Command in a Crystal Report File

查看水晶报表文件中命令的SQL

There are times you have just the report file, but not the associated database structure that the report uses. This is common when dealing with example reports of functionality you wish to mimic. This is a workaround ONLY to allow you to see the SQL of a Command that a Crystal Report is based on, when you don't have the underlying database connection that the report is based on. In essence, the dialog box has to be satisfied before it will show the SQL, so we fool it with a legitimate Data Source, just not one that would work with the SQL that is actually in the SQL Command.

有时您只有报告文件，但没有报告使用的关联数据库结构。这在处理您希望模仿的功能的示例报告时很常见。这是一种变通方法，仅当您没有报表所基于的基础数据库连接时，才允许您查看 Crystal 报表所基于的命令的 SQL。本质上，对话框在显示 SQL 之前必须得到满足，所以我们用合法的数据源欺骗它，而不是一个可以与 SQL 命令中的 SQL 一起工作的数据源。

Why does a report use a command? Doesn't Crystal Reports have the ability to link tables? When a Crystal Report is based on a record set that is too complex for the table linking functionality within Crystal Reports, the report can instead be based on a SQL Query, usually developed/tested in another editor tool and pasted into the command. This allows advanced SQL functions to be utilized.

为什么报表要使用命令？Crystal Reports 没有链接表的功能吗？当 Crystal Report 基于对 Crystal Reports 中的表链接功能来说太复杂的记录集时，该报表可以基于 SQL 查询，通常在另一个编辑器工具中开发/测试并粘贴到命令中。这允许使用高级 SQL 函数。

If you don't already have a Data Source on your computer set up that you can connect to, you will need to build one first.

如果您的计算机上还没有设置可以连接的数据源，则需要先构建一个。

A simple Microsoft Access .mdb file saved in a simple location will suffice. I placed mine with the path C:\A_test\test.mdb to make it easy to find. If you don't have one, google for a sample mdb file and download it, saving it with a name and location you can remember. (You won't ever actually open this file, but just connect to it.) Once you have the file saved, open the ODBC Administrator and create a New Data Source. (you can get to the ODBC Administrator quickly from Start > type ODBC in the Search) On the User DSN tab, click the Add button. Scroll down the driver list to Microsoft Access Driver (*.mdb), select it and click the Finish button. In the Data Source Name box, type a name (I used MyTest). Click the Select Button and select the mdb file you saved from a previous step, click OK. Click OK again. You will see your new Data Source listed by the name you gave it. Click OK. You now have the data source you will need for the next steps.

一个简单的 Microsoft Access .mdb 文件保存在一个简单的位置就足够了。我把我的放在路径 C:\A_test\test.mdb 以便于查找。如果您没有，请在谷歌上搜索一个示例 mdb 文件并下载它，并使用您能记住的名称和位置保存它。（您实际上永远不会打开此文件，而只是连接到它。）保存文件后，打开 ODBC 管理器并创建一个新数据源。（您可以从开始 > 在搜索中键入 ODBC 快速进入 ODBC 管理器）在用户 DSN 选项卡上，单击添加按钮。向下滚动驱动程序列表到 Microsoft Access Driver (*.mdb)，选择它并单击 Finish 按钮。在数据源名称框中，键入一个名称（我使用的是 MyTest）。单击“选择”按钮并选择您在上一步中保存的 mdb 文件，单击“确定”。再次单击确定。您将看到按您提供的名称列出的新数据源。单击确定。您现在拥有了后续步骤所需的数据源。

1. Open the Crystal Report you want to see the SQL command for, and click on Database Expert button or Database>Database Expert Menu.
2. Under Selected Tables, right click on the Command and choose View Command
3. The Data Source Selection Box appears. Select the Data Source you created (or one you already use) and click the Finish button. The View Command box should open with the SQL in the left pane. Copy the SQL into your favorite text editor.

1. 打开要查看其 SQL 命令的 Crystal Report，然后单击“数据库专家”按钮或“数据库”>“数据库专家”菜单。
2. 在选定表下，右键单击命令并选择查看命令
3. 出现数据源选择框。选择您创建的数据源（或您已经使用的数据源）并单击完成按钮。“查看命令”框应打开，并在左窗格中显示 SQL。将 SQL 复制到您喜欢的文本编辑器中。

回答by sqrepants

Whats happening is that the crystal reports needs a database to connect to regardless if its the original source DB or not.

发生的事情是水晶报告需要一个数据库来连接，无论它是否是原始源数据库。

Create a local database or use a database stored on a server, added it to your ODBC Datasources and use it when connecting. After a successful connection you should be able to view the SQL query without an error.

创建本地数据库或使用存储在服务器上的数据库，将其添加到您的 ODBC 数据源并在连接时使用它。成功连接后，您应该能够无错误地查看 SQL 查询。