Switching between tables at run time

[Pregius]

CR 9 to an Oracle db (the database belongs to an outside group so I have no ability to make changes/views at the db level).

Each of 6 agencies has a parallel set of tables differing only by the first 2 characters of the table name (i.e. all the same field names, only the table names change).

Ie.
A1_Master
Report_No
Report_Date
Etc.
A2_Master
Report_No
Report_Date
Etc.


I would like to write one report that prompts for agency and then only queries the correct set of tables. Is there a way to use variables to specify the table name?

As an alternative I tried using case select formulas to connect the display (and selection) data to the right table based on the selected agency but there are no real links between the tables for each agency so Crystal seems to be failing to return data due to multiple starting points. If I deselect all but one agency’s tables and rem out the case statements connecting to the other agencies’ tables it returns data fine.

I did find a way around using 6 subreports (each hard coded to point to one agency’s tables) in different report footer sections which are selectively suppressed but I was hoping to find a more elegant way that only connects to one agency’s tables and doesn’t involve maintaining 6 subreports.

 

[vidru]

Are you familiar with the 'Add Command' option in CR 9? If the table structure is the same for all 6 tables, you could use a Command with a parameter that represents the table name:

When choosing your data source, pick the 'Add Command' option instead of Tables/Views/Stored Procedures, and enter your SQL as needed in the 'Add Command to Report' window, without specifying a table name:

[tt]SELECT Field1, Field2, Field3
FROM [/tt]

Click the 'Create' button in the 'Parameter List' area, and setup the parameter like the following:
Parameter Name: Agency
Prompting Text: Choose the Agency
Value Type: String
Default Value: leave blank

Place your cursor one space after the 'FROM' in your SQL statement, and double click on your newly created {?Agency} parameter. The resulting SQL query should look like this:
[tt]SELECT Field1, Field2, Field3
FROM {?Agency}[/tt]

Click the 'OK' button, then enter a valid table name (e.g. 'A1_Master'). When you expand 'Command' once you're in the report designer, you should see your fields.

Under 'Parameter Fields' in the Field Explorer, you should see your 'Agency' parameter. To make the report more user-friendly:
Right click the Agency parameter > Edit > Click the 'Default Values' button > type 'A1_Master' in the 'Select or Enter value:' textbox, anc click the '>' button to add it as a default > repeat for all of your Agency tables > Once your six agencies are in, you can set the 'Description' of each default value to represent the Agency name as opposed to the table name, then choose the 'Description' option from the Display dropdown.

When you run the report, it shold prompt you for an Agency, and when you choose the Agency you want, it should get the data from the corresponding table.

The other option that comes to mind would be setting the table through code from within an application.

-dave

 

[Pregius]

Thanks! I had not used "Add Command" before but it sure sounds like what I need. However, I'm having some trouble getting it to like the table name I give it. It is giving me "Query Engine Error: 'ORA-00933: SQL command not properly ended '". Per other threads here I've tried variations on single and even double quotes but haven't struck on whatever it's wanting. I'm assuming the form should go something like this:

DSN.Databasename.Views.Tablename

I'm not really sure about the middle two parts (I'm guessing the 2nd is the database name) but I'm just listing everything I would normally pick via the tree to get to my table. Is there some syntax rule I'm missing?

Thanks again!

 

[vidru]

My experience with Oracle is very limited, so hopefully someone else will chime in for the syntax you need for the schema.

-dave

 

[synapsevampire]

What connectivity are you using? Try using the Crystal supplied connectivity rather than the Oracle supplied ODBC driver.

Just use the parameter.table as the FROM table name, it sounds like you're used to using some other tool than standard database access tools.

Why not post what you tried rather than saying it doesn't work?

Test the syntax first in SQL Plus or your preferred Oracle query utility, then copy and paste into the Add Command.

-k

 

[cfitzner]

The easiest way to find out, how your SQL-Statement has to look like is the following:
Use the "Show SQL-Query..." function, how Crystal is creating the SQL-Statement. (in Crystal 10 this function is in the Database Menu, I hope Crystal 9 has this function, too!!)
Copy that Statement in the Dialog of "Add Command"
and just replace the part which you want to make dynamically with a parameter.

It can look like this:
SELECT a.Field1, a.Field2, a.Field3
FROM SCHEMA.{?Agency} a