Single Datasource, multiple databases

Jeremy

I am trying to write an inventory report, which is using two databases.

The primary site uses the database InvA, and where the product is in the secondary facility this database has a secondary code which is basically the key for the inventory line in the InvB database.

I have written a SQL statement that connects to the two databases and collects the data they want, combining where needed and the data matches what they want. A single table combining the product quantity by product code and lot code, regardless of facility.

1) The problem I'm having is how do I create a report that uses my SQL statement which connects to two databases? Is there a way of assigning two connection strings to a single SqlDataSource? Is there an example for this?

2) Do I have to programmatically "merge" my datasources into a single data table, and then use an ObjectDataSource? Is there an example of this?

3) Does Telerik use DataSource and Database interchangeably, meaning I can only use one database per report? Should I be asking the client if I can modify the database to include a table valued function?

Silviya

1. Only one connectionString is allowed per SQLDataSource. If you prefer to get data from two databases:- You can use two separate SqlDataSource components. Each component can be used as a DataSource for a data item (e.g. Report, SubReport, Table, Crosstab, List, etc.)- You can create a SQL query joining records from different databases.

2. If you use custom data access layer and a data model, you can use the ObjectDataSource and expressions to get data from collection properties without additional data source components - How to Databind to Collection Properties.

3. Every Data Item can be bound to a single Data Source Component. Thus if you need to use more than one data source components, you should use another data item in the main one (usually report). For example, you can use SubReport or Table item inside the main report, which would be bound to your second database.

Best Regards,
Silviya
Progress Telerik

Do you want to have your say when we set our development plans? Do you want to know when a feature you care about is added or when a bug fixed? Explore the Telerik Feedback Portal and vote to affect the priority of the items

Progress, Telerik, Ipswitch, and certain product names used herein are trademarks or registered trademarks of Progress Software Corporation and/or one of its subsidiaries or affiliates in the U.S. and/or other countries. See Trademarks for appropriate markings.