How to keep fields on report when changing SP?
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=5229
Printed Date: 28 Apr 2024 at 12:45am
Topic: How to keep fields on report when changing SP?
Posted By: hdjim69
Subject: How to keep fields on report when changing SP?
Date Posted: 15 Jan 2009 at 5:41am
|
< ="Content-" content="text/; charset=utf-8">< name="ProgId" content="Word.">< name="Generator" content="Microsoft Word 9">< name="Originator" content="Microsoft Word 9"><>
I have a report that uses a stored procedure. All the columns returned by the SP are being used in the report (they have check marks next to them). Problem is I need to use a different SP but it returns the same columns. If I remove the SP, all the hard work of laying out the fields nice and neat on the page will be lost. How to change to use a different SP but keep all the fields? It will really suck if I have to reposition everything... :( hd |
Replies:
Posted By: lockwelle
Date 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.
|
Posted By: hdjim69
Date 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 |
Posted By: AntDC
Date 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 |
Posted By: DBlank
Date 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
|
Posted By: hdjim69
Date 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. |
Posted By: DBlank
Date 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.
|
Posted By: hdjim69
Date 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. 
|
Posted By: DBlank
Date Posted: 15 Jan 2009 at 10:04am
 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.
|
Posted By: hdjim69
Date 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? |
Posted By: DBlank
Date Posted: 15 Jan 2009 at 11:00am
|
Kind of. And I could be wrong on this as I do not know exactly how it is doing what it is but...
You are not technically adding your SP#2, you are replacing your SP#1 with it. However, the names of your tables or vierws or SP in your report design won't change so it can get confusing.
To test the process make a copy of your report.
Go to Database menu and select Set Data Source location.
Highlight the SP name in the "Current Data Source" window.
It will look something like this
-Report
+Properties
+SPnamehere (select/highlight this)
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.
Now look back into your Current Data Source and Expand the "SPname here" field and expand the properties.
You will see that the new table name = your SP#2 and the data is actually pointing back to it.
In your report it will always look like it is pointing to SP#1 but it is not.
Does this make sense? |
Posted By: hdjim69
Date Posted: 15 Jan 2009 at 11:34am
|
>>How about calling the stored proc from within a command? I'm not sure how to do this. Can you explain? Thanks hd |
Posted By: hdjim69
Date Posted: 19 Jan 2009 at 4:44am
|
>> http://www.crystalreportsbook.com/Forum/member_profile.asp?PF=6162&FID=5 - DBlank yes, that makes sense. I will try it out today and let you know how it works. thanks. hd
|
Posted By: ahaiken
Date Posted: 11 Mar 2009 at 8:14am
|
Hi DBlank, Is there a way to change the datasource from the dev to prod box when using a SQL command as the data, rather than selecting tables? I would have to use the 'Add Command' under ODBC(RDO) host choice. When I copy and past the SQL, it eliminates all of the report fields that I used from the original SQL. Thanks, Amy |
Posted By: DBlank
Date Posted: 11 Mar 2009 at 8:29am
|
Hi Amy,
Sorry but I am not sure about that one as I have never tried it.
|
Posted By: ahaiken
Date Posted: 11 Mar 2009 at 11:04am
|
I'm afraid that I'll have to redo a bit of the report formatting after changing the database with the same SQL command. I googled it, and didn't seem to come up with a solution. I'm surprised that CR didn't come up with a way to do that. Most people using the SQL command would like to change just the database that it's pointing to when moving it from dev to prod. Thanks for answering me so fast. Amy |
Posted By: DBlank
Date Posted: 11 Mar 2009 at 11:14am
|
I may be off base here but if you have an existing Command under a ODBC connection try this: Select Set Data source Location.
In the Current Data source click on the highest level (dsn)
In the Replace with, find your new source (production box) dsn or create the connection to it
Highlight it by clciking on it
The UPDATE button should become active
Click on update and it should replace your source.
This is assuming that your SQL is just calling table names that are the same in the 2 differnt data locations.
IS this what you are trying to do?
|
Posted By: DBlank
Date Posted: 11 Mar 2009 at 11:17am
|
Might want to make a copy of your report first
just in case
 |
Posted By: ahaiken
Date Posted: 11 Mar 2009 at 11:24am
|
Yes, that's what I did with my copy and it deletes all of the references from the dev. box. Oh well, I guess that CR 9 didn't think that people would want it to work the way that I want it to. Thanks! Amy |
Posted By: Gremlin1708
Date Posted: 22 Jun 2009 at 2:23am
|
Hi There...
I came across this post for the same reason you posted it :-) Well after a long struggle i think i mite have the answer for us. If you have a "Dev" server and a "Active" server and you want to post your reports to the Server that's got the live data then to change the ODBC you :
1. right click on the Table Name / Sp in the Field Explorer
2. Select Set Datasource Location from the Pop up Menu.
3. Expand from Report >> ODBC Conn >> Properties
4. There you will see DSN
4.1. Right Click and select "Edit"
4.2. Rename the DSN and then select the ODBC that your active server is on.
This is what i did and it worked 100% it kept my Fields on the correct place and it showed me the "live" data.
Hope this is of help for you.
|
Posted By: lockwelle
Date Posted: 22 Jun 2009 at 6:28am
|
I would have suggested to change the database/location in the DSN. Crystal doesn't care where the data comes from, just that it looks like the 'report'. As long as the data can be retrieved from the same tables/stored proc/view Crystal will be fine.
for years, that is how I would develop reports.
|
Posted By: hill.kenneth.b
Date Posted: 06 Jul 2009 at 5:52pm
|
Originally posted by Gremlin1708
Does this work in CrystalReports9,X or XI? I have no problem with changing data source locations for stored-procedures, but tables and views don't seem to work for me.
Hi There... I came across this post for the same reason you posted it :-) Well after a long struggle i think i mite have the answer for us. If you have a "Dev" server and a "Active" server and you want to post your reports to the Server that's got the live data then to change the ODBC you :
1. right click on the Table Name / Sp in the Field Explorer
2. Select Set Datasource Location from the Pop up Menu.
3. Expand from Report >> ODBC Conn >> Properties
4. There you will see DSN
4.1. Right Click and select "Edit"
4.2. Rename the DSN and then select the ODBC that your active server is on.
This is what i did and it worked 100% it kept my Fields on the correct place and it showed me the "live" data.
Hope this is of help for you. |
Posted By: lockwelle
Date Posted: 07 Jul 2009 at 6:18am
|
It should. The basics of how Crystal connects, I don't think has changed for a long time. If you can change the source location for a stored proc, changing tables should be the same.
I change odbc connections but first updating the odbc connection for the report, and then for each table inside of the connection(for some reason, updating the connection doesn't change the tables...makes no sense to me). But I believe that you already tried that, so I don't know what else to add.
Sorry.
|
Posted By: Gremlin1708
Date Posted: 07 Jul 2009 at 6:24am
|
Originally posted by hill.kenneth.b
Originally posted by Gremlin1708
Does this work in CrystalReports9,X or XI? I have no problem with changing data source locations for stored-procedures, but tables and views don't seem to work for me.Hi There... I came across this post for the same reason you posted it :-) Well after a long struggle i think i mite have the answer for us. If you have a "Dev" server and a "Active" server and you want to post your reports to the Server that's got the live data then to change the ODBC you :
1. right click on the Table Name / Sp in the Field Explorer
2. Select Set Datasource Location from the Pop up Menu.
3. Expand from Report >> ODBC Conn >> Properties
4. There you will see DSN
4.1. Right Click and select "Edit"
4.2. Rename the DSN and then select the ODBC that your active server is on.
This is what i did and it worked 100% it kept my Fields on the correct place and it showed me the "live" data.
Hope this is of help for you. This post was posted when i was using Cyrstal Reports XI R2.
|
Posted By: hill.kenneth.b
Date Posted: 07 Jul 2009 at 8:32am
| I'm able to do this, but now when I try running the report through the browser I get prompted to enter a database login name and pw; I don't get this prompt when viewing the report in the CR preview. |
Posted By: Gremlin1708
Date Posted: 07 Jul 2009 at 10:41pm
| This has got something to do with the user rights on the Server and aslo then i installed the .Net framework 3.5 and then the IT Dept also did some changes. I will call them later today and then find out what exactly they did and how they fixed the problem. |
Posted By: ch_tech
Date Posted: 11 Nov 2010 at 4:02am
| Thank you so much for your post, it pointed in the right direction!! |