Multiple Databases - How to select

[greg52]

I have about 20 company databases on a single MS SQL2000 server which all have identical tables. I would like the report to prompt the user to select which company to report against. There isn't any problem with the prompt, but how do I get the company selected to be part of the SQL command? For example, if the user wants a report for Company1, how would I get the query to run against Company1:

Use company1
Select table1.fielda, table1.fieldb...etc

or

Select company1.dbo.table1.fielda, company1.dbo.table1.fieldb...etc

The SQL would be executed from CR v8.5. Is what I'm asking even possible? Thanks for any advice.

 

[lbass]

One approach might be to use a UNION ALL statement that merges the tables, but adding a field that distinguishes the original table name. Then you could set up a parameter that would pull only the records that met that criterion. In 8.5 I don't think you can add a "fake" table field to the initial select in the "Show SQL Query", but you could add an unused field, e.g., {table.name} from the first table, and then for subsequent tables add a corresponding field like:

'1' as 'Name'

...which you could then use in your record selection formula.

-LB

 

[vidru]

If you were using CR 9, you might be able to pull this off with Crystal alone, but since you aren't, your options are:

1) Use stored procedures that will return the results for the selected company's database.
2) In a central database, create Views for each table using a UNION query to link all of the databases, then control the results by using a CompanyName parameter in Crystal. For example, one of the Views might look like:
SELECT CompanyName = 'CompanyA', Field1, Field2, Field3
FROM CompanyA.dbo.Table1
UNION ALL
SELECT CompanyName = 'CompanyB', Field1, Field2, Field3
FROM CompanyB.dbo.Table1
etc.......

In Crystal, you'd create a {?CompanyName} parameter, and restrict the records returned for the correct database by using a record selection formula like:

{ViewName.CompanyName} = {?CompanyName}

-dave

 

[vidru]

Sorry, LB. Didn't see your post...

-dave

 

[greg52]

Thanks all for your help.

Dave - Your view suggestion is what I figured would have to be done if CR couldn't do it

-Greg