Switching data sources at run time

[Mike Lewis]

I am using CR 8.5, reporting against a FoxPro database (DBF files) via ODBC.

My data directory holds several tables, all with the same structure, but with different names. Is there any way of letting the user choose which of these tables to use as the data source for the report? I would like the choice to be made at run time (that is, at report-viewing time), ideally via a parameter.

Does anyone have any ideas for how to do that?

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

My sites:
Visual FoxPro (www.ml-consult.demon.co.uk)
Crystal Reports (www.ml-crystal.com)

 

[lbass]

Mike,

There might be a technically more sophisticated approach to this, but 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 for in your record selection formula.

-LB

 

[Mike Lewis]

Hi LB,

Thanks for the reply. Your idea of using a UNION is ingenious, but unfortunately I don't think it would work in this case.

I should have mentioned that the user will have no control over the table names, nor will the report know in advance what those names are. It seems that the folder can contain an arbitrary number of tables, the names of which are based on the date range to which the data applies (believe me, I didn't design this).

If there was some way of constructing a SELECT on the fly, at run time, I could do it, but I don't think CR offers that facility.

Mike


__________________________________
Mike Lewis (Edinburgh, Scotland)

My sites:
Visual FoxPro (www.ml-consult.demon.co.uk)
Crystal Reports (www.ml-crystal.com)

 

[Mike Lewis]

For the benefit of anyone else who might have this problem, here's how I solved it.

I wrote a simple program that prompts the user for a filename, then copies the relevant file it to a new one, overwriting it if it already exists. The new file has a fixed name. The report is always based on this new file.

I don't know if this is the best solution, but it is working fine, with very little effort on my part.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

My sites:
Visual FoxPro (www.ml-consult.demon.co.uk)
Crystal Reports (www.ml-crystal.com)