Swapping field names

[bigfoot]

I have a report that was done a while back.
If I want to substitute 1 field for another, do I have to delete the field on the report and replace it with another field?
Is there somewhere I can switch the underling field name?

On Active Reports I have the options to repoint the screen field to another place.

 

[synapsevampire]

Yes, that's the simplest way.

If you wanted to create reports from scratch using formulas as the holders for fields, which is akin to Active Reports and some other report writers, then you could replace what is in the formula.

-k

 

[bigfoot]

Thanks. I was afraid of that.

 

[bigfoot]

synapsevampire, what I wanted to do was change the data source.
I'm a little better equipped mentally to discuss this.
I learned a lot over the past 2 weeks working with this beast.

What I'd like to do is move the whole SQl query out to a stored procedure or a view. My table names are not changing, nor are my field names.

I'm running 8.5.

 

[synapsevampire]

Create the View or SP, then use the Database->Set Location to point to the SP/View.

-k

 

[bigfoot]

So I can just move this whole mess down to SQL Server?

You may have just made my whole month.
If I can pull this off, I'll give you 10 stars.

 

[synapsevampire]

You can probably copy and paste it into View creation method and with minimal or no tweaking (quotes, etc.) it should run.

In the future post your database, why you would think this inconsequential makes me nervous.

-k

 

[bigfoot]

Like I said. This was posted back when I had no idea what I was dealing with.
SQL Server 2000.

And thank you so much for your help and patience.

We have no plans of upgrading Crystal Reports, unless we have to.
It is without a doubt, the worst thing I have ever encountered in my 25 years of programming.
It's buggy, and it's tempermental. And for what it cost, it should work so much better.

 

[synapsevampire]

Well Crystal is best of breed, has the vast market share, and you are using a version that came out around the days of Windows 95, so you should expect it to be problematic.

In SQL Server you can right click Views and select New, and a visual tool will pop up. Paste in the SQL form here and it will probably allow it, and then you can save it and then repoint the report to it.

I have plenty of complaints about CR 8.5, but far less with CR XI. You can't state that you want to use old software and then complain that it feels primitive, you've elected to save pennies in a fashion that I suspect costs you much more.

-k

 

[bigfoot]

I'll need a bit to make these changes, synapsevampire, but tell me if you would please.
How much better is version 9? We have it here and we were going to upgrade to it, but if it's not that much better then I won't waste our time.
If XI works then I will lobby to have it installed, but they tell me it's expensive and I may not have a chance.

As for me? I always like to be current in any software I use, but my company writes the checks, and I have to abide by their wishes.

I didn't realize it was that old, as I had not used it until this job that I just started.

Thanks again for your help.

 

[bigfoot]

Set Location does not seem to do what I need.
I found that I have to set location on each of my 6 tables when what I needed was to replace the 6 tables with a view.

I can see what it's doing if I look in the SQL Query window. It's replacing the name of the database and not the table itself.

Any other ideas? At this point I'm tapped out.
This started with passing a simple parameter down to the database 2 weeks ago.

 

[synapsevampire]

I had no way of knowing that there were several tables involved, and yes, since the data source is completely different, pointing to a single source for what used to be multiple data sources means that you should use set lcoation for the most used table and then remove the other tables and replace the fields accordingly.

I have tried to prosylitize the use of Voews in design for years here for many reasons, and since you've decided to use the database as it should be used, which will allow for increased flexibility, resuability and simplified maintenance, you're going to have to take a hit to correct the original architecture. And it probably is replacing the name of the table with the view, you may be seeing some sort of aliasing.

The latest version of Crystal XI Release 2 does allow you to replace existing fields with other fields.

-k

 

[bigfoot]

Hey, thanks synapsevampire.

Just so you know, this is a report that the last guy who was here wrote. I would have written it using a stored procedure or a view.
I think I'm going to alleviate all the troubles and just recreate the report from scratch. This way it's my report and I'm not fixing his mess.

Sorry about the confusion, synapsevampire. This report is a real wreck.
It's got 6 tables. And I found out the query was replaced somehow, so when I did get the parameters to pass as you helped me in the other thread by creating a field formula, it would not update the SQL Query window unless I clicked on the Reset button on the same window.
This seems to turn the query over to Crystal, and not the "typed in" version.

Well, this caused a lot of headache because Crystal does not like the way he linked the tables although SQL Server takes it ok. The data types ate a bit different varchar linked with char.

I've learned a lot about Crystal this week including a few tricks and traps.

(Also to post my queries and database type when I ask a question.)