tables from different datasources

Question

Summary

Content

Hi all

I am using BI Publisher 11.1.1.7 and have a report which needs to use 2 different data sources (databases). I have selected one database as the default data source in my data model, this database has most of the tables and I also need to use a few tables from another database. I would like to create one sql query which will use tables from the 2 databases, if possible. My question is; can I reference a table from a different database in an sql query?

Please assist. Thanks.

Pavan kumar. · Answer

Can you send the Screenshot of the data sets with join condition along with query for the main data set ? So that can analyse the solution.

Sopmat · Answer

Hi Pavan

I have already created list of values for each of the 3 fields as well as the parameters associated with these LOVs. The issue I have is that I can't include the folowing condition in the main data set (which uses Dsource1):

where field1 = :parameter1 and field2 =  :parameter2 and field3 = :parameter3

because field2 and field3 don't come from Dsource1 but from Dsource2 which is the data source for the second data set so the above condition doesn't work as field1 and field2 don't exist within that main data set. How can I amend my data model so that I use all the 3 parameters? Thanks.

Pavan kumar. · Answer

Hi,

While you are creating the list of values, create 3 values input as you are required ie.., it's fine to choose SQL Query for parameters.By using Query builder or using direct query for retrieving the values from the data sets.Then Create a parameter and invoke the corresponding list of value which was created earlier.Then Add the Parameter to the Data set with the syntax  ":Parameter"

List Of values: Each Query will work with explicitly depends on it's own data set, bcz it should have function from the front end of the report

where field1 = :parameter1 and field2 =  :parameter2 and field3 = :parameter3

TIP: You should have to create 3 different LOV's and Params associated with it and these params should invoke from the Data model query with above Syntax

It's because when the data engine executes the query it should be authenticated with the parameter selection to the report, so that you should append the parameter as condition part of it otherwise there is no use of the parameter creation.

NOTE: Ensure that you have provided the precise join condition over it.