I am using an existing Crystal Report in an ERP system, which works fine within their application. Which means, I don't want to change the report definition because somehow the application is able to get past this issue. I have written a .NET application, using the SDK, to run the Crystal Report and create a PDF.
This report has several SQL Expression Fields defined in the Crystal Report. Within these expressions, the fully quantified name is used in the SQL. For Example, the expression below has "TEST" as the database to reference Table1. This is what is in the report file.
({fn ifnull((select Count(*) from TEST.dbo.Table1 Inner Join Table2 On Table2.ID = Table1.ID Where Table2.ID = MainSQL.ID and Table2.ItemID = MainSQL.ItemID),0)})
However, when I get the SQL from the profiler, that "TEST" database is changed to "LIVE". And the report works. Yes, when I edit the Crystal Report SQL Expressions and change "TEST" to "LIVE" in the report file - my application works perfectly. But, I don't want to have to remember to change the source report if it gets updated. And I found that the Crystal Report SQL Expressions Fields are READ-ONLY.
Is there a way to get the SQL in Crystal Reports before it is run? I know CR has a "Show SQL Query" in the menu, but I am not sure if that SQL can be updated.
Any help would be greatly appreciated!
I tried updating the SQL Expression Fields but they are READ-ONLY. And I could not find a way to get the SQL for the report and update.
