Creating reports with Dynamic Table names

[Mike555]

Hi. I need to report on different tables within a database based on the date a user inputs. For example if I input 20016523 it would look at the table named
Name_TABLE_Here_20016523. I was wondering how I would go about doing this. Thanks in Advance for any help anyone can give me.

-Mike

 

[IdoMillet]

AFAIK, there is no direct way of supporting this
type of functionality, but I can see one indirect way
of achieving the desired behavior.

How many alternative tables do you have and do
you know the names of all alternative tables in advance?

Cheers,
- Ido

 

[mike5555]

There could be any number of tables. Yes there is a Catalog table that lists all the different table names.

 

[IdoMillet]

Roughly, how many tables?

Do you know all possible table names at the
time you design your report?

- Ido

 

[mike5555]

No I don't know how many tables...cause the number is going to be a date YYYYDDMM. Now the Catalog table lists each date so maybe I can do a SQL QUERY on the field as a table for a different cell. Question is...will crystal let me do it.

-Mike

 

[IdoMillet]

Mike,

If you can redesign of the system so that all
daily tables are stored in one table with an added date
column, problem is solved.

If however, this is not an option, then create an Insert trigger
on the catalog table so that whenever a new daily table is created
you append the records of the new daily table to a physical COMBO
table or generate the necessary DDL to Drop & Recreate a UNION View
that establishes a virtual version of the physical COMBO table.

For example:
Select column1, 2, 3, Table_Name as 'Date' from Table1
UNION
Select column1, 2, 3, Table_Name as 'Date' from Table2
UNION
Select column1, 2, 3, Table_Name as 'Date' from Table3
etc...

As you can see from the example above, the physical or
virtual (View) table should have an added column
that is the name of the table being appended or UNIONed.

Now, in Crystal you run against this Table or View and you can
add a selection criterion that limits the rows to only those where
the 'date' column matches the daily date parameter.
You can then also generate other reports that constrain data to
a range of dates using the same approach.

hth,
- Ido

 

[mike5555]

Thanks a lot for your help