tables from different datasources

Sopmat

Summary

tables from different datasources

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.

Maraprik

you can create two datasets and link them. This will depend on your requirement. or write a SQL with DB links to the other DB.

Regards

K

Pavan kumar.

Hi,

As per your requirement it's intuitively possible to connect across different data sources in this version.For this, you have to do that select one source as parent data set (G1) and later select secondary data set (G2) and provide join Condition as necessary between both data sets.Now create sample.xml and validate it whether you are able to receiving the expected data output as per the data model.

Please Mark it as Answered if found this as helpful

Thanks,

Pavan kumar

Sopmat

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.

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.

Sopmat

Hi Pavan

Thanks, I have created the 2 datasets; the first data set uses the main data source (default data source) and the second data set uses the other data source. I linked the 2 data sets and successfully generated sample data. This worked well without parameters.

The problem is on the parameters. I need to use 3 parameters; 2 of the parameters are created from fields that come from one data source while the third parameter comes from the other data source. I have created a list of values for the parameters and now need to add code for the parameters to the first data set sql query which has a different data source from the other data set.

My question is; how do I add the parameter code to the first data set sql code? Not all parameters were created from the main data source. My code should be

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

but field2 and field3 come from a different data source which is used by the second data set and this wouldn't work. How do I resolve this?

Please help. Thanks

Pavan kumar.

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.