Creating A Report on Table whose TableName Changes dynamically 1

[iceicebaby]

I am using CR 8.5 and Database Oracle 8. My Problem is that one of the Tables in my Complex Report changes name for every month i.e the TableName is Dynamic e.g
Usage_01_A For Month Jan
Usage_01_B For Month Feb
How do I incorporate this in my Report?
One option is to base my Report on a Query in which I can form the TableName dynamically in the From Clause, but I don't want to do this. Has anyone gone thru a similar problem? Any Help is sincerely appreciated.

 

[jdemmi]

One option, which would still be a little work each month would be to create a synonym (on the db side) for the table.

Then each month you would only have to change the synonym.

 

[themikeaustin]

You are going to have to use dynamic SQL, either in your front-end application or in Oracle. I would use a stored procedure to calculate the table name and use dynamic SQL within the stored procedure to create and submit the SQL statement.

Mike

 

[themikeaustin]

Another option is to create a table - CurrentMonth - that is truncated and refreshed as needed with the current month's data and to use this in your report.

Another option is to have a single table and use Between to filter on the month-begin and month-end dates (which can be passed as parameters).

Mike

 

[IdoMillet]

Another option is to create a VIEW that uses a UNION ALL query to combine all these tables into a single result set. In each of the 12 SELECT statements, include a different static value for a "Month" computed column (to differntiate between the rows:

SELECT * FROM Usage_01_A, '01' AS Month
UNION ALL
SELECT * FROM Usage_01_B, '02' 
UNION ALL
SELECT * FROM Usage_01_C, '03' 
etc.

Then, a Crystal report can easily use a parameter to restrict the information to a specified month or month range.

hth,
- Ido

CUT, Visual CUT, and DataLink Viewer:
view, e-mail, export, burst, distribute, and schedule Crystal Reports.

http://www.MilletSoftware.com