Edit SQL in Crystal

[AlanDI1]

I am doing some maintenance for a client who has an Access database and some Crystal reports. They have an annual sales program and each year the views in Access and the Crystal reports are updated for the new year. The when viewing the 2007 Sales report in Crystal, it has an alias on the from tables so the report is basically the same each year and you just create a new Access Query/View and point Crystal to it. They change the Access Query/View to select the items for the particular year.

The "Show SQL Query" in Crystal looks like:

Select * FROM 2007_dataview dataview, RegMaster RegMaster
Where <blah blah blah>
Order by <blah blah blah>

So the data fields on the report reference dataview.<whatever> but it points to the annual Access Query/View.

I cannot find a way to edit the referenced the Access Query/View to make it 2008_dataview instead of 2007_dataview.

If I go into Database Expert and delete the 2007_dataview it deletes all the fields from the report.

Any good ideas on how to trick it into letting me do that (or maybe an older version of Crystal that isn't that helpful?).

I have no idea when or in what version this was originally created.

All ideas are appreciated. Thanks

 

[hilfy]

In the Database menu, select Set Datasource Location and change the report to point to the new view. This will keep your alias.

-Dell

A computer only does what you actually told it to do - not what you thought you told it to do.

 

[jdemmi]

Not sure what version you have or how far this feature goes back but....

Database --> Set Datasource location

This should let you map the old view to the new view.

Long term -- consider creating a generic view, for example, called "dataview", based on the dataview in question (2007, 2008, etc)


Tie this to the report. You then only need to change the generic view whenever the dataset needs to be changed.

oracle syntax of what I am suggesting.

assume 2007_dataview exists

create view dataview as select * from 2007_dataview

-- Jason
"It's Just Ones and Zeros

 

[AlanDI1]

You guys are geniuses. Thanks

You have no idea how long I've looked at that.

Long term I would never do it that way but you know how it is to maintain someone elses code.

Thanks to all

 

[jdemmi]

LOL -- i didn't even see hilfy's(dells) response...i had this open for several minutes and never submitted.

great minds.....

-- Jason
"It's Just Ones and Zeros