From dataset into Report

[Luis Lopes]

First of all:

Thanks for the time lost in trying to resolve my problems,
I have 2 Diferent Databases and N Tables in each one.

----------------------------------------------
Database1 - TableA
CliCod --- CliName --- SalesMan
(…) (…) (…)
----------------------------------------------

----------------------------------------------
Database2 - TableA
UniqueKey --- CliName --- Value
(…) (…) (…)
----------------------------------------------


My problem is not to create the SQL Statement but where to put it (implement it).
Select a.*
From database2.TableA A
Inner join Database1.TableA B ON A.CliName == B.CliName
Where A.SalesMan= @Input String from User.


Please ber with me :(

All the examples I found are for 1 database - N Tables.
I want ->2<- databases - N tables

I think the best choice is to use the Push Model, so that I create a dataset to pass to crystal report, (and crystal report viewer).

But how can I create a dataset from 2 databases when the data adapter connectionString wants just one database to connect to.

How can I cross reference two Tables in different Databases into the same DataSet, so that I can pass this dataset into the Crystal Report.

I will be very grateful you could help me, because I can not find an example doing this any where.
The SQL statement would look something like this:

Thanks for your time.

[springsoft]

simplest way would be to creat a temporary dataset, and then connect to that:
SQL sample as follows, to get data from table1 and table2 and store in tableTemp

SELECT table1.PDATE, table1.PLINE, table1.PSHIFT, table1.RUNTIME, table2.RTIME INTO tableTemp
FROM table1 INNER JOIN table2 ON table1.PDATE = table2.PDATE
WHERE (table1.PDATE=#11/19/2004#);

That gives you your server-side table ready for connection.

dave

[malleyo]

I'm not sure how a dataset works...

I use VB6 and Crystal Reports 8.5...

In VB 6, I use ADO to get my data, then I pass the recordset to Crystal Reports (using RDC). See this link (http://www.codeguru.com/forum/showthread.php?t=309534&highlight=ado) for an example. Doing it this way would allow you to easily pull data from more than one table or database.

[chomax27]

Have you tried using the Merge method of the dataset?...you can generate
2 datasets from the 2 databases...and then merge these dataset into a single one and bind your crystal report to it..just make sure that the 2 datasets have the same structure.

dataset1.merge(dataset2)

yourReport.setdatasource(dataset1)


i hope this helps :)