PDA

View Full Version : Report based on two databases at the same time



jaarden
5-Aug-2013, 08:54 AM
Hi,

I am looking for a way to create a report based on different tables in 2 MSSQL databases at the same time.

Is this possible?

Regards,

Jeroen Aarden

Bob Cergol
5-Aug-2013, 04:59 PM
Hi Jeroen,

A Dynamic AI report is build on a data source. That data source can be a backend table or view, or a Dynamic AI view or Dynamic AI join. A view can join tables from multiple databases if that's what you mean. The advantage of defining views in Dynamic AI instead of the backend database is they become dynamic -- that is they can:


reference paramaters [@ParN@] whose values are provided at runtime by the user
reference enforced values [@Alias#] defined at the user profile level. These values can be singular, or contain a list of values
reference tables selected by the user at run time from a drop down list (partitions) -- tables structures of course need to be consistent

Even a connection to the database can be Dynamic. You can use an alias value from the user profile in a connection string. You can also specify multiple databases available for any given report to run against at run time. (tables & structure must of course be common)

So basically, any report in Dynamic AI can deliver tailored information based on who the user is whether CEO or Sales Mgr. or Customer Service Rep. and that information can be queried dynamically from a list of Dynamic AI database connections the user has rights to, and from a partition list of tables.

All that being said -- back to report packages. They can contain any number of reports regardless of what the data sources are for those reports and therefore are very useful for presenting information from multiple data sources for multiple databases.

Does this answer the question you have in mind?

Bob

jaarden
6-Aug-2013, 02:43 AM
Hi Bob,

Still struggling...

I have 2 database db1 and db2. In these databases there are synchronized customer tables. The report I have in mind must check the balans between these customers.
So the report has to join these 2 tables like:

select db1.customer1.id, db1.customer1.amount as balans1, db2.customer2.amount as balans2 from db1
left join db1.customer1 ON db1.customer1.id = db2.customer2.id
....

This way the report shows the amounts between the synchronised customers.

The databases are from different parties so therefore a report like this should be nice.

Regards Jeroen

jaarden
6-Aug-2013, 06:42 AM
Bob,

In the meantime I have a solution from Eddy.
So this issue s solved for now...

Regards,

Jeroen

Bob Cergol
6-Aug-2013, 08:36 AM
Jeroen,

Good to hear. I was wondering if I understood your question correctly.

Can you share the solution to it?

Bob

Eddy Kleinjan
6-Aug-2013, 08:48 AM
No secrets; just a little extra information you need to add when addressing the tables:

SELECT *
FROM
[QuBis].[dbo].[Klant]
LEFT OUTER JOIN [MUIS123].[dbo].[Factuur] ON
[Factuur].[KlantNr] = [Klant].[KlantNr]

This can even be used to address different servers in a query. For this the server must have been "linked":

SELECT *
FROM
[Server1].[QuBis].[dbo].[Klant]
LEFT OUTER JOIN [Server9].[MUIS123].[dbo].[Factuur] ON
[Factuur].[KlantNr] = [Klant].[KlantNr]

The method is called four part naming. See for example http://www.mssqltips.com/sqlservertip/1095/sql-server-four-part-naming/

Eddy

jaarden
6-Aug-2013, 09:36 AM
Thnx Eddy and Bob. This works great!