Help on working with 2 SQL databases in Crystal Reports

[dorupirvu]

Lets have the following databases:
1. One database containing a table named db1.“Accounts” with the following columns:
a.UserName
b.Symbol
c.NumberShares
Example:
UserName Symbol NumberShares
John Symb1 10
John Symb2 20
George Symb3 50

2. Another database containing a table named db2.”Symbols” with the following columns:
a.Symbol
b.Price
Example:
Symbol Price
Symb1 123.2
Symb2 100
Symb3 80

I want to build a report containing:
1. All the symbols corresponding to a specified user
2. The value for each symbol (Value = db1.Accounts.NumberShares*db2.Symbols.Price)
Report example:
UserName: John
Symbols Value
Symb1 10*123.2
Symb2 20*100

Any idea will be appreciated.

Best regards,
Doru

 

[Tirol]

You can create a view under SQL Server.After that, you can add it to your report and use it like the other tables.

View 's sql statement =
"select db2.Symbols.symbol as symbol , db1.Accounts.NumberShares * db2.Symbols.Price as value
from db1.Accounts,db2.Symbols
where db1.Accounts.symbol = db2.Symbols.symbol "

 

[dorupirvu]

Thanks Tirol for your idea,

But could you tell me please what I have to do if the databases are on different SQL servers (I mean one database is on a SQL server running in Canada and the other database is on a SQL server running in US).

Best regards,
Doru

 

[Tirol]

here is the solution ;
for example
SQL Server Name in US is USSERVER
SQL Server Name in CANADA is CANADASERVER

1-)from Client Network Utilty add servers
2-)in a SQL Query sentence write down
the server name(called in Client Network Utilty )
which you want to add .
'SP_addlinkedserver CANADASERVER'
and execute.
3-)
now write down your query

"dbo" = YOUR USER NAME 'it can be "sa" or anyting else'

"select CANADASERVER.DBO.db2.Symbols.symbol as symbol , USSERVER.DBO.db1.Accounts.NumberShares * CANADASERVER.DBO.db2.Symbols.Price as value
from USSERVER.DBO.db1.Accounts,CANADASERVER.DBO.db2.Symbols
where USSERVER.DBO.db1.Accounts.symbol = CANADASERVER.DBO.db2.Symbols.symbol "

(Note:You can test these from SQL Query Analyzer)

Have a nice query