How to replace SQL in an existing report?
Printed From: Crystal Reports Book
Category: Crystal Reports 9 through 2020
Forum Name: Report Design
Forum Discription: The best way to design a report and problems you have encountered
URL: http://www.crystalreportsbook.com/forum/forum_posts.asp?TID=5736
Printed Date: 29 Apr 2024 at 12:44pm
Topic: How to replace SQL in an existing report?
Posted By: jzurbo77
Subject: How to replace SQL in an existing report?
Date Posted: 10 Mar 2009 at 9:56am
Hi, I need to help a user who created his CR report using wizards without knowledge of SQL or other underlying components. Now he is stuck with a problem. I managed to come up with a solution but in SQL. I got SQL from Database-Show SQL Query and modified it to do what user needs. Now I need to replace SQL in the working report with my version. It selects exactly the same fields, so I expect it to be 100% transparent to the report. It is my very first exposure to CR so I know nothing about it. But with my modest experience with SSRS I took a look at the report file (.prt) - alas, it is not XML and I can not simply change SQL using Notepad. I assume that should be a simple task, to replace SQL without touching the report itself - maybe someone could walk me through few clicks?
If I would know how to switch original report to use stored procedure I should be able to easily change its code, but it is another area where guidance would be appreciated...
Thanks!
-------------
John
|
Replies:
Posted By: ahaiken
Date Posted: 10 Mar 2009 at 10:33am
Hi John,
I hope that I understood what you were looking for. If you go Database > Database Expert > Create New Connection > ODBC > Add Command use the arrow key to copy 'Add Command' to the 'Selected Tables' area. You should get a new window that is titled 'Add Command To Report'. You can copy and paste your SQL in the area entitled 'Enter SQL query in the box below'.
I hope that was what you were looking for.
Best Regards,
Amy
|
Posted By: jzurbo77
Date Posted: 10 Mar 2009 at 3:03pm
Thank you for getting back to me. What am looking for is a way to either
1) Change SQL in an existing report without creating it from scratch. All I did I changed one inner join to a left join, so it will have all records from one of participating tables. Considering that resulting selection of "new" SQL matches existing structure (same fields, just more rows) I expect the change to be transparent for resulting report - it should continue to work...
2) Switch that existing report from using embedded SQL to same SQL placed into a stored procedure.
I tried to recreate the report, started to go through steps you outlined and looks like somewhat succeeded.
Below you can see how original report looks like in design mode - some fields are supressed (crossed). I was able to supress only Details and Report Footer (see another image below), but not the fields, like the one I circled and pointed to on "my report". How do I supress these fields?
old report
http://clip2net.com/page/m0/703182
my report:
http://clip2net.com/page/m0/703208
-------------
John
|
Posted By: ahaiken
Date Posted: 11 Mar 2009 at 6:47am
Hi John,
I haven't worked with cross-tabs at all, but when I poked around, I found that there's a 'Cross-Tab Expert' where there are check boxes to check if you want certain things to be suppressed. Right-click on the cross tab > Cross Tab Expert > Customize Style tab.
Hope that this helps.
Amy
|
Posted By: jzurbo77
Date Posted: 11 Mar 2009 at 8:04am
Yes, Amy, I see how to suppress Totals, thank you.
What I still do not see how to do is how to edit SQL in existing report. Database > Show SQL Query shows me the query but does not allow to edit it. Is there way to do that?
-------------
John
|
Posted By: ahaiken
Date Posted: 11 Mar 2009 at 8:07am
Database > Database Expert > Under Select Tables right click on Command > Edit Command
You can only do this if you used the 'Add Command' for the Data source.
|
|