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!

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

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?