2 data sources for one report--Visual Fox Pro and SQL

[cgilmore]

I have several reports which I created using ODBC to my Visual Fox Pro data. We are have moved 3 of our very large tables into sql. Over the next year we plan on moving some more tables. I will need my reports to pull some data from Visual Fox Pro and some from SQL. Our database administrator has created a view to the 3 SQL tables. I can connect the VFP tables and link them to the view, but I can only pull data from one of the SQL tables. I can't pull data from the other 2 SQL tables. I am wondering if the view is the problem. I never had any luck previously pulling data from an Access view. Our database administrator thinks we need to create our fox pro report and create a SQL report and have the SQL be a subreport of the fox pro. Is there any other way around this?

 

[synapsevampire]

You can link a fox table to a SQL Server table from within CR, though the performance is horrible.

what does:

"I can connect the VFP tables and link them to the view, but I can only pull data from one of the SQL tables."

mean???

You should be able to connect to any datasource from Fox. Are you talking about remote views, or???

-k

http://www.informeddatadecisions.com

kai@informeddatadecisions.com

 

[cgilmore]

Our database administrator created a view to the 3 SQL tables, premium header, gen header and gen detail. I can link the foxpro table to the view. I can pull data from the gen header table which is the master, but I can't pull data from the children tables--premium header and gen detail. Are we going to have to create a main report from the fox pro table and then do a subreport from the SQL view?

 

[cgilmore]

Let me try this again. We have been using Crystal Reports for over a year. We have 8.5 professional edition. We have created over a hundred reports. Some are quite lengthy. We have been attaching solely to Visual Fox Pro data and have had no problems. 3 of our tables, premium header, master table, gen header, child table, gen detail, child table, have become huge so we converted these 3 tables to SQL. My first report after the move I tried creating a report where I linked to a VFP table and then I linked that to the 1st SQL table, premium header. Then I linked gen header to prem header and gen detail to gen header. I could pull data from only the VPO table and the premium header SQL table. I could not pull data from the Gen header or the gen detail tables. So our database admin. created a view from the 3 SQL tables. I can't pull this data into the detail section along with my VPO data and have it work. The only way I have been able to put out a report is by using the SQL data in a subreport and the VPO in the main report. Are we stuck doing this? Is there a better way?

 

[cgilmore]

Sorry I have a typo. Where you see VPO it should be VFP for Visual Fox Pro.

 

[synapsevampire]

Since you're using disparate data sources, it's complicated.

It would be useful to know what you're trying to do, such as left outer join, etc.

Can't you just create remote views into the SQL Server database within the DBC and access them from CR? I haven't tried this yet, but it makes sense.

An alternative is using MS Access to "LINK" all of the tables and report off of a query inside. I sometimes do this for transition systems and disaprate data sources.

-k

http://www.informeddatadecisions.com

kai@informeddatadecisions.com