Avatar of kbit
kbit
 asked on

Crystal reports - Select table at runtime

I have a number of crystal reports that select data from a table based on the year.  The data is divided up into tables for each year and contain about 50,000 records each.  The system is setup to read the tables this way and concatting the tables into one is not an option.  The question is:  Is it possible to set up crystal reports so that the user can choose the table to read at runtime instead of having to amend each report to change the table ?  Obviously all columns have the same name within the table.
Crystal Reports

Avatar of undefined
Last Comment
Mike McCracken

8/22/2022 - Mon
peter57r

Not in any realistic way.
You can change the datsource using Set Database Location but if it's not done carefeully you will destroy the report, so I would not ever expect a user to do it.

peter57r

I have assumed you are not using a front-end program to run your reports.
kbit

ASKER
No front end just Crystal itself.  Setting the database location is something we do for different environment e.g test and live but all the tables we want the option to run are in just the one database.
Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!
James Murphy
kbit

ASKER
When you view the options from the file menu and click on the database tab there is an option to "reprompt user when connecting" and it allows you to put in a tablename LIKE comand there.  We've tried this but can't get it to prompts, does anyone know if this is the solution and if we are missing something to get this to work.  If not what is this option for ? Crystal reports version is 8.5
SOLUTION
wykabryan

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
ASKER CERTIFIED SOLUTION
Ido Millet

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
Mike McCracken

CR 8.5 doesn't have the COMMAND option.  That was introduced with CR9

mlmcc
Ido Millet

Didn't notice he was using Crystal 8.5.  In that case, either an SP or the UNION approach are probably your only option.  
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
Mike McCracken

The subreport option will also work but that may be a slow process when building the report.  Also any changes will have to be made several times.

mlmcc