Crystal Reports XI - Dynamic Table Assignment

Hi All,

I'm trying to find out where I need to begin looking...  I'm working with someone that is
Developing a CR report that takes parameters from a source application.  I'd like to pass a
discrete Table Name to the report rather than having CR binded to the table ahead of time.

Here's the situation...  My DB makes daily backups of certain tables and appends a date
i.e.
   MyTable
   MyTable_20101019
   MyTable_20101018
   MyTable_20101017
   MyTable_20101016
etc.

The structure of each of the tables is exactly the same.

In essence I want to say Run Report X and run it for 20101017....  How do I go about building
my report to dynamically allow for this?  My very limited knowledge or CR doesn't allow me to
say that it can or can't be done.

Thanks in advance.
-Rob
rkuhnleAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

nsonbatyManager IT Service DeskCommented:
the backup is files with what extensions, and where is it saved ?
rkuhnleAuthor Commented:
They are new tables within the same DB.  They are structurally identical.
nsonbatyManager IT Service DeskCommented:
you means that, each report will be for diffrent table data, and that will be changed according to your select at report refresh
Fundamentals of JavaScript

Learn the fundamentals of the popular programming language JavaScript so that you can explore the realm of web development.

rkuhnleAuthor Commented:
I believe that the answer is yes :-)  In essence I have an application that will provide a user with the ability to select a report to run.  As part of that selection the user will select for which historical day that they want to run the report for.  That date in essence will tell the report to point to a given DB Table i.e. MyTable_20101017 rather than any of the others.  Is that any more clear?
GJParkerCommented:
This type of thing is not possible using just the designer, you would need to develop an application and pass this information to the report in code at runtime.

HTH
msd1305Commented:
There is a way. You can use stored procedures with crystal reports to retrieve records.

In both Oracle and SQL, there is a way to write dynamic queries and return records.

So, in the dynamic query you can generate the table names with some logic (e.g. by date ).

This way it will be quite flexible and you wont have to change the crystal report evetytime.

If you need any further help on creating dynamic queries in stored procedures then feel free to let mw know and specify which DB are you using.

hope this helps :)
rkuhnleAuthor Commented:
OK, so in general within CR proper there is no way to perform it discretely.  I believe that that is a general agreement?

Actually the SP approach was the one approach that we were considering.  

In Oracle we would create a view or materialized view that somehow dynamically joined the data from each table to create a single summary view.  Then we would need to report off of the single view.  I think this is similar in nature to what msd1305 is suggesting?
LinInDenverCommented:
I think what msd is getting to is that you could have a parameter for tablename, passed into the stored proc, and then used in your WHERE clause.

However, you can also create a command in Crystal statement to prompt for table name, especially if the structure (and DB location) of each table is identical.

i.e.: Crystal Command:
1) Inside the Command editor, choose to Create a new parameter, called table.
2) Command that goes in box would be:
select dummy from {?table}

I tested this and it worked fine in CR2008. It should also work in XI...
LinInDenverCommented:
I'm attaching a sample report. You can see what I did by going to

Database| Database Expert

Right click on COMMAND and choose view or edit command. This is where the parameter was created.

If it prompts you for a DB/password, just pick one of your own - it doesn't care.
dynamic-table.rpt

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Mike McCrackenSenior ConsultantCommented:
How do you plan to run the report?

Unless the end iser has Crystal installed you will need an application and the application can change the datasource.

mlmcc
msd1305Commented:
@LinInDenver: No, I was not talking about passing the table name as a parameter. As the tables names are based on the dates, it can be generated dynamically inside the stored proc itself (thru some local variable).

Passing table name in command is possible but then it has to be passed as a parameter to the report which I am not sure is a good idea or not. Because if this is going to be an application report then it can be generated in the code and passed into the report but if this has to be deployed on the CR server and users are directly going to use it then it wud be a problem.
rkuhnleAuthor Commented:
Thanks All,  we believe that both approaches will work.  We need to evaluate our data in order to determine the final solution that we'll utilize in production.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Crystal Reports

From novice to tech pro — start learning today.