ReportServer Forum

hMobile

Member

Registered: 2018-03-27

Using two datasources at once

Hi Reporters,

I'm using a datasource that has id_client as a column, but not de real_name_client. That circumstance produces a non very readable report.

I have a master table, outside the original datasource, where I can cross (JOIN) the id and the real name...

Is there any possibility to use two datasources and do a JOIN? Can somebody point me to an example?

At this moment I'm using the CE so I haven't all the features of Enterprise Edition.

Thanks in advance, any ideas are welcome!

Rom

Last edited by hMobile (2018-05-16 10:02:13)

eduardo

Administrator

Registered: 2016-11-01

Website

Re: Using two datasources at once

Hi Rom,

why don't you create a datasource where you have the master table and use this datasource?

Regards,
Eduardo

hMobile

Member

Registered: 2018-03-27

Re: Using two datasources at once

Hi Eduardo,

Because they are two .csv lists and I'm looking not to have an intermediate process...

Thanks,

Rom

eduardo

Administrator

Registered: 2016-11-01

Website

Re: Using two datasources at once

Hi Rom,

a report can currently have only one datasource. Datasource parameters may use another datasource, though.
You can also use datasource bundles if you need to e.g. have a different datasource per user: https://reportserver.net/en/guides/admi … rce-Bundle (Usually the database bundle is used when you want to execute the same report on different databases, but actually it is executing the same report using different datasource definitions (which can point to the same database, but with different connection parameters).)

If you want to combine more than one datasource you may use a script datasource for this: https://reportserver.net/en/guides/scri … tasources/
But for this you have to use the enterprise edition. You may try with the enterprise evaluation.

Regards,
Eduardo

hMobile

Member

Registered: 2018-03-27

Re: Using two datasources at once

Gracias Eduardo,

I need to evaluate other options as the first one you posted...

Cheers,

Rom

eduardo

Administrator

Registered: 2016-11-01

Website

Re: Using two datasources at once

Hi Rom,

for database bundles you also need the enterprise edition. You can see a feature comparison here:
https://reportserver.net/en/pricing/

Regards,
Eduardo

Hamza_DBM

Member

Registered: 2018-06-04

Re: Using two datasources at once

Hi Eduardo,

I am using an enterprise evaluation. I want to combine more than one datasource (two different databases).
As I need to create a report  (Dynamic list) based on data from two separate Databases.
You said that it is possible! would you please provide me with the right steps.

This Url https://reportserver.net/en/guides/scri … tasources/
Shows just how to produces any sort of data and output in a table format!

Best Regards

eduardo

Administrator

Registered: 2016-11-01

Website

Re: Using two datasources at once

Hi Hamza_DBM,

yes, the idea is that the script datasource reads data from two datasources and combine them into one table format. Then you can use this script datasource as a "normal" datasource.

We will prepare an example script and will let you know.

Regards,
Eduardo

Hamza_DBM

Member

Registered: 2018-06-04

Re: Using two datasources at once

Hi Eduardo,

Thank you. I will be waiting for your script example.

Regards,
Hamza

Last edited by Hamza_DBM (2018-06-13 12:21:18)

Rodion

Member

Registered: 2017-05-17

Re: Using two datasources at once

Hello Hamza_DBM,

we created a script for you that accomplishes based operations.

import java.util.Iterator;

import net.datenwerke.rs.base.service.datasources.definitions.DatabaseDatasourceConfig;
import net.datenwerke.rs.base.service.datasources.table.impl.TableDataSource;
import net.datenwerke.rs.base.service.datasources.transformers.DatasourceTransformationService;
import net.datenwerke.rs.base.service.reportengines.table.SimpleDataSupplier;
import net.datenwerke.rs.base.service.reportengines.table.output.object.RSTableModel;
import net.datenwerke.rs.base.service.reportengines.table.output.object.RSTableRow;
import net.datenwerke.rs.core.service.datasourcemanager.DatasourceService;
import net.datenwerke.rs.core.service.datasourcemanager.entities.DatasourceContainer;
import net.datenwerke.rs.core.service.datasourcemanager.entities.DatasourceContainerProviderImpl;
import net.datenwerke.rs.core.service.datasourcemanager.entities.DatasourceDefinition;

	
dataSupplier = GLOBALS.getInstance(SimpleDataSupplier.class);
dsService = GLOBALS.getInstance(DatasourceService.class);
datasourceTransformationService = GLOBALS.getInstance(DatasourceTransformationService.class);

	
RSTableModel go() throws Exception{
	DatasourceDefinition definition = dsService.getDatasourceByName("DatabaseName"); // DatabaseDatasource
	String query = "select * from myTable";
		
	DatasourceContainer c = new DatasourceContainer();
	c.setDatasource(definition);
		
	DatabaseDatasourceConfig dsConfig = new DatabaseDatasourceConfig();
	dsConfig.setQuery(query);
	c.setDatasourceConfig(dsConfig);
 
	DatasourceContainerProviderImpl provider = new DatasourceContainerProviderImpl(c);
		
	TableDataSource tds = datasourceTransformationService.transform(TableDataSource.class, provider, null);
	RSTableModel table = dataSupplier.getData(tds);
		
	printTable(table);

	tout.println("END");
	}
	
void printTable(RSTableModel table){
		
	// printing column names 
	int columns = table.getColumnCount();
	for(int i = 0; i < columns; i++) tout.print(table.getColumnName(i) + "\t");
		
	tout.println();
		
	// printing rows
	Iterator<RSTableRow> iterator = table.iterator();
	RSTableRow row;
		
	while(iterator.hasNext()){
		row = iterator.next();
			
		for(int i = 0; i < columns; i++) {
			if(row.getAt(i) == null) tout.print("null\t"); 
			else tout.print(row.getAt(i).toString() + "\t");
		}
		tout.println();
	}
}

return go();

Here you can see how to retrieve data from a defined datasource and query.
In this manner you can define multiple datasources.
This code shows also how to get data out from a RSTableModel (printTable-method).

- If you want to build a new RSTableModel, you can just declare it and use this method "addDataRow(RSTableRow row)" to fill the table.
If you want to define the columns (or create new), you have to operate with the tableDefinition
so :

tableDefinition = table.getTableDefinition();

// and now you can do much operations, like
public TableDefinition addColumn(String name, Class<?> type)

// or 
public void setColumnNames(List<String> columnNames)

- To get a value from a specific row of a specific column you can use "public Object getValueAt(int rowIndex, int columnIndex)".

So you can variable play with the tables and build your output.

Enjoy)
If you have further occasion please don't hesitate to ask us for help.

Greetings,
Rodion

Last edited by Rodion (2018-06-20 09:48:21)

Hamza_DBM

Member

Registered: 2018-06-04

Re: Using two datasources at once

Hi Rodion,

Thank you for your reply. It is working for one database  but what is the syntax for two databases with an SQL query with a join for two tables from two databases ?

DatasourceDefinition definition = dsService.getDatasourceByName("one database"); // how to access the two databases in the same time ?
    String query = "select * from myTable";

Last edited by Hamza_DBM (2018-06-21 18:54:27)

eduardo

Administrator

Registered: 2016-11-01

Website

Re: Using two datasources at once

Hi Hamza_DBM,

what db (MSSQL, MySQL, etc) are talking about and are they both in the same server ?

Regards,
Eduardo

Hamza_DBM

Member

Registered: 2018-06-04

Re: Using two datasources at once

Hi eduardo,

One is Oracle and the other is MySQL and they are using different server,however both are configured in My RS and working fine. What I need is to make a join connection with an SQL query.

thank you for your assistance

Hamza

eduardo

Administrator

Registered: 2016-11-01

Website

Re: Using two datasources at once

Hi Hamza,

it is not directly possible to make a join on different databases from different vendors on different servers.

You can, though, use one of the following approaches:
1. Read your data from your first database using the script above. When you have your data, use it for reading the second table from the second database in an analogous way as the script above. This is the programmatical approach. Disadvantage is of course the complexity and the performance.
2. Use a data warehousing/ETL solution to unify your tables into one table. This is the preferred solution and the solution I would recommend. For example, make an ETL process to execute every night that reads your tables and creates your unified table. Then, you can read this unified table with a standard reportserver datasource. This is the preferred way for analysis. This is a very efficient solution and you can make complex analysis on your data, e.g. using olap mondrian cubes.

You can read more information here:
http://www.unityjdbc.com/doc/multiple/multiplequery.php
https://mondrian.pentaho.com/documentation/

Regards,
Eduardo

Hamza_DBM

Member

Registered: 2018-06-04

Re: Using two datasources at once

Hi Eduard,

Thank you for the swift reply I really appreciated it. Well, I am using Birt now for doing this and I hope guys you will be able to integrate this option in RS like it is possible in Birt.

Thanks again Eduardo,

Hamza.

Last edited by Hamza_DBM (2018-06-22 16:00:23)

eduardo

Administrator

Registered: 2016-11-01

Website

Re: Using two datasources at once

Hi Hamza_DBM,

fyi, I raised ticket RS-2958 for this.

Regards,
Eduardo