Zuzza


I have two database:

FoxPro database - contains client info (name, address)

SQL Server database - contains appointmetns, orders, jobs etc.

How can I create reports using both databases

Thank you




Re: How to create a report using multiple databases

Teo Lachev


You can have report datasets connected to different data sources. You can use subreports if you need to link them, e.g show appointments by client. The other option is to join datasets at the SQL Server level using OPENROWSET().





Re: How to create a report using multiple databases

Zuzza

Is there a way to have data from both databases in one dataset

I need to provide reports like: weekly sales by consultant - it has to be a list of customers (names) and total $ amounts sold grouped by product category.

I






Re: How to create a report using multiple databases

Teo Lachev

The linked server option (OPENROWSET or OPENQUERY) will allow you do this at the SQL Server level. Alternatively, you can implement a CLR stored procedure (assuming SQL Server 2005) which can prep the dataset. Finally, you can write a SSRS custom data extension to merge the datasets.