Changing Data Tables Easily?

[rockman20]

It's been a while since I have been here. Good to see the helpful information is still flowing!

Here is my setup. I am running CR 8.5 against SQL2000 databases through ODBC connections and I have around 24 SQL databases.

For each database I have approximately 13 reports setup as of right now. That is about 312 individual reports that I have created. Here is my problem.

Every one of these reports contains "year qualified" tables in them. Meaning the table name looks something like this ASTU3006. ASTU is the student table, the 3 is the year (2003) and the 006 is the school number.

I will give an example of a report I am working on. I have the data table ASTU3006 and then I have a subreport that is pulling data from data table APRN3006. And refreshing the report shows me each child and their contact information for the year 2003.

Now that we have moved to 2004 I will need to go into each report and change the data tables. So in the example above, I would need to change my primary report to ASTU4006 and the subreport to APRN4006. No big deal, but to do this on over 300 reports will take a while. Not to mention what happens if someone wants to run a report against last years data. I could create a new report for each year, but that seems like a lot of reports hanging out on our file server.

So, is there a way to setup something like a parameter field. When you refresh the report, it would come up and ask you what year you would like to pull from. If you typed in "03" or "2003" the report would know that it should pull everything from ASTU3006 and APRN3006. If you refresh the report again and this time choose "04" or "2004" the report would adjust and now pull the information from ASTU4006 and APRN4006.

Or is my only option to create a report for each year and save them as seperate reports?

Hopefully I didn't make this too confusing!
Thanks!
Del

 

[crystalreporting]

Given that you're using SQL - would a stored procedure or view make more sense - then, you'd only need to change the stored procedure each year.

Peter Shirley

http://www.linkcorp.com

 

[rockman20]

Here is where my experience is very low. SQL. Here is what I do know. We have SQL databases that were created as just blank, empty, default, databases. Then we have a student information system (called SASI) that use to be ran in dbase format. With a program called XPODBC, it went through and converted the dbase over into SQL and pushed it to the correct SQL database with the use of the ODBC connection supplied in an INI file.

The SASI program is the program that creates what they call year qualified files. SASI is the program that takes care of creating and deleting anything on SQL so really we are just using SQL as a storage facility. No one actually "connects" to the database, SASI just uses a default limited user to look at a AUSR view on SQL to see if the user exists in there or not. If they do, then they get access to the SQL data.

So, with this kind of setup, if it is possible to do what you suggested, I would be more then willing to check into it. If it is too much to chat about on here or someone may not be willing to give that information out, a helpful push in the right direction would be greatly appreciated! Thanks for the reply!