Report Design
 Crystal Reports Forum : Crystal Reports 9 through 2020 : Report Design
Message Icon Topic: How to keep fields on report when changing SP? Post Reply Post New Topic
Page  of 3 Next >>
Author Message
hdjim69
Newbie
Newbie


Joined: 29 Dec 2008
Location: United States
Online Status: Offline
Posts: 24
Quote hdjim69 Replybullet Topic: How to keep fields on report when changing SP?
    Posted: 15 Jan 2009 at 5:41am
IP IP Logged
lockwelle
Moderator
Moderator


Joined: 21 Dec 2007
Online Status: Offline
Posts: 4372
Quote lockwelle Replybullet Posted: 15 Jan 2009 at 6:10am
This isn't too hard, but you will need to reposition the fields.  I have done this before, but it is better than starting from scratch.
 
1) add the new stored proc to the report...Do not remove the old stored proc (just like adding a table to the report)
2) take the fields from the new sp and place them one at time on the report.  I know that CR XI has the 'painter' function (right click on the old field, select format painter, click on the new field with the paint brush) which transfers font types/colors/suppression logic. Now click the new field , hold the CTRL key down and click the old field, make them the same size, then align left.  at this point, I usually can delete the old field and use another field in the same section to align tops with.
 
Voila, the 'new' field has replaced the old field, in exactly the same place and style as the old field.  Repeat until all the fields on the report have been replaced (I check by running the cursor over them and making sure that the datasource is different.) 
 
Some of the fields may still be checked, Look at the groupings and the formulae.  Altering a formula is easy, and the group is just right clicking on the section and selecting 'Change Group'.
 
When all the check marks are gone, go back to data expert and remove the old stored proc.
 
Yes it takes time, but it is way better than starting from scratch.
 
Hope this helps.
IP IP Logged
hdjim69
Newbie
Newbie


Joined: 29 Dec 2008
Location: United States
Online Status: Offline
Posts: 24
Quote hdjim69 Replybullet Posted: 15 Jan 2009 at 7:15am
Hi, thanks for your effort but I don't have that functionality. I don't see the paintbrush.  I'm using VS2005 and not sure what version CR is as under the Crystal Reports menu there is nothing that tells me the version.

I was hoping, after I added the second SP that I could just change the datasource to use the 2nd SP since all the fields are the same but CR doesn't let me.  I'm a novice with CR but maybe whenever a new report is to be created don't directly add the fields from the SP but rather create a formula for each field?  then maybe they won't get deleted if you remove the SP?

hd
IP IP Logged
AntDC
Groupie
Groupie
Avatar

Joined: 23 Dec 2008
Online Status: Offline
Posts: 76
Quote AntDC Replybullet Posted: 15 Jan 2009 at 7:25am
How about calling the stored proc from within a command?

I'm guessing that the fields are tied to the actual Command object and thus, if the SP returns the same fields, all should be good.

Regards
DC


Edited by AntDC - 15 Jan 2009 at 8:20am
IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet Posted: 15 Jan 2009 at 7:30am

Not sure if your CR version will let you but try this:

Go to Database menu and select Set Data Source location.
Highlight the SP name in the "Current Data Source" window.
Go under your Current connections or History or wherever your getting your new SP from in the "Replace with" window.
Select / highlight the new Store Procedure name there.
The Update button will become available.
Click on it.
Expand the properties in teh Curent Data Source and you will see the new SP identified in the Overridden Qualified Table Name


Edited by DBlank - 15 Jan 2009 at 7:31am
IP IP Logged
hdjim69
Newbie
Newbie


Joined: 29 Dec 2008
Location: United States
Online Status: Offline
Posts: 24
Quote hdjim69 Replybullet Posted: 15 Jan 2009 at 7:53am
that's exactly what I was trying to do but the fields from the old sp still have the check marks next to them.  I was hoping the check marks would be assigned to the new sp fields but that's not the case so  when I remove the old sp, all the fields on the report still go away. 

Looks like when you drag a field from a datasource it's locked to that datasource and there is no way to reassign the fields to a new datasource.  bummer. 






IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet Posted: 15 Jan 2009 at 7:56am

I am not sure I understand.

The process I describe will replace 1 SP with the other.
The naming gets really weird as it is basically aliasing. You will still see the original SP name but it is not using it anymore.
Check the data to see if it is gettting the dat from the new SP or the old one.
IP IP Logged
hdjim69
Newbie
Newbie


Joined: 29 Dec 2008
Location: United States
Online Status: Offline
Posts: 24
Quote hdjim69 Replybullet Posted: 15 Jan 2009 at 9:51am
again, thanks for the effort but nothing seems to change the fact that once you drag a field from a datasource onto the report and that field gets check-marked from that datasource it seems like its locked to that datasource.  Nothing moves the check-mark to the new sp.  So if you have fields on a report from a datasource and you remove that datasouce, all the fields are removed along with that datasouce.  Thumbs%20Down
Thumbs%20Down
IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet Posted: 15 Jan 2009 at 10:04am
Ermm Hmmmm, sorry but I just want to make sure you followed my suggestion exactly because you still talking about removing a SP in your last post.
Do not remove the data source.
Under DATABASE select SET DATASOURCE LOCATION, not Database Expert where you remove a database.
From the Set Datasource Location dialogue you can update an existing source with a new source (e.g. swap one existing SP in the report for another SP not in the report) without removing anything. Since all your columns are named the same it should work fine.
I have done this numerous times where I have created a standard report that I needed to swap datasources out and have had no problems.
IP IP Logged
hdjim69
Newbie
Newbie


Joined: 29 Dec 2008
Location: United States
Online Status: Offline
Posts: 24
Quote hdjim69 Replybullet Posted: 15 Jan 2009 at 10:46am
so just so I understand, you have two data sources in you report, one is used the other is just there so we don't have to go thru this problem and is not used?  did the check-marks move to the new datasource columns?
IP IP Logged
Page  of 3 Next >>
Post Reply Post New Topic
Printable version Printable version

Forum Jump
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot delete your posts in this forum
You cannot edit your posts in this forum
You cannot create polls in this forum
You cannot vote in polls in this forum



This page was generated in 0.031 seconds.