Change of Location 1

[Riteshm]

I want to change the loaction of datasource from a table to stored procedure without loosing any grouping, formulae or fields place on the existing report.

Is there a solution for this?

Thanks in advance for your help.

 

[lyanch]

Some one may have a solution for this, but I have never found one.

I just add the SP to my report and change things one at a time. I frequently check back to see if I have any fields checked in the "old" tables so I know if I am missing something. I can switch a moderately complex report in about a hour, so it really isn't an onerous task.

Lisa

 

[Riteshm]

Thanks Lisa for your quick response.

Only thing I'm worried about in using this approach is grouping.

Is there a way to get the list of grouping used in the report. So that I can cross check when I have report ready linked to Stored Procedure.

 

[diamond6]

I have always found that using an Alias gets around these problems. But it must be set when you start the report.
If you set an Alias then you can change the location of the table without any formula, grouping, chart or anything else getting messed up.

 

[chelseatech]

I had a site where we decided to change all the reports from tables/joins to Stored Procs.

I used "Show SQL query" to see which table had the most fields - then set the location of that table to the Stored proc.

Then carefully removed each field from the other tables. and repalced them with the equivalent field in the stored proc. Once all the fields from a table were removed, i could remvoe the table without getting an error.

Groups were "Change Group" ed to the equivalent field in the stored proc.

A reasonable amount of work, but the performance improvements made it worthwhile.

Good luck Editor and Publisher of Crystal Clear

http://www.chelseatech.co.nz/pubs.htm

 

[kenhamady]

Riteshm,

When you said from 'a' table did you mean one table? If so
then it is simply 'set location'. chelseatech's technique is the only way to go from multiple tables to an SP. Ken Hamady, On-site Custom Crystal Reports Training & Consulting
Public classes and individual training.
Guide to using Crystal in VB

http://www.kenhamady.com/

tek@kenhamady.com

 

[Riteshm]

Thank you all for spending time on this in order to help me.

Let me explain the problem in detail.

I have designed the reports using Aliases and report uses only one table. Project schedule is such that we want to convert these the conversion data source from existing table to a stored procedure (having simple query from same table with some where clause) in minimal time.

I tried to change the location of source using "Set Location" command under Database menu. Set Location dialog box shows Alias name under Database menu. Using Set Location button I changed the location to a stored procedure. The message I get when I click on Done is "Error opening file. There are no fields in the file: Proc(proc_name;1)".
When the database was set to Table the "Show SQL Query" was showing "Select Alias.Field1, Alias.Field2,... From Table_Name Alias"
After converting to stored proc it shows "Select Alias.field1, Alias.Field2,... From Proc(Proc_Name;1) Alias"

My question here is, can we change the query stored with Crystal Report to {call Proc(proc_name;1)}. This is the query crystal report stores when a report is originally designed with stored procedure. But if fields on the report are coupled with source type (Table or stored proc) then I think this is not possible. Curious to know about any workaround that is available to trick the crystal reports.

Your time and help is appreciated.

Thanks,
Ritesh