link three tables by unique Key through Paginated reports (report builder, SSRS)

[TiffanyCui]

Hi all,

I'm trying to create a full data report by Paginated Report (SSRS).

First, I connect the Oracle database to the desktop creating the layout, then publish it to the online service. The relationship among these three tables can be built automatically by the desktop. For example, when I filter by the order number, it will affect all three tables.

Second, I add the Power BI dataset which I published to the online service to Paginated reports (SSRS).  But The relationships in the Power BI model do not follow through to the Paginated report,  i.e., a data model is not created within Report Builder. When I choose the order number, it can only affect 1 table, the other two tables will not be linked. 

Third, because of the way I set it up, I've tried the SQL using the unique key to connect three tables but it didn't work out.

Please feel free to let me know if you have any thoughts.

Best Regards,

Tiffany

[FarhanAhmed]

If you want to pull out data from multiple data sources to a single Visual (e.g. Matrix Table) then you need to use LOOKUP function in order to get values from the other 2 data sources.

Lookup Function (Report Builder) - SQL Server Reporting Services (SSRS) | Microsoft Docs

[TiffanyCui · Accepted solution]

Hi FarhanAhmed,

I checked my layout. One data source, three datasets. How to link the three datasets by their same ID? And the way I added the data source is (1) power bi desktop connect to the Oracle database to create the report (2) publish to the online service (3) paginated reports add power BI dataset connection