link three tables by the 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

[V-lianl-msft]

Hi @TiffanyCui ,

As far as I am concerned,The PowerBI Dataset Relationships are not supported when accessed from Paginated Report Builder.  There are two suggested ways to work around these are to either rely completly on Measures from a PowerBI DataSet, or do setup a Derived table by creating new Table using a DAX table funcion like FILTER. And then relying on this single table for the Pag.Report.

Best Regards,
Liang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[TiffanyCui]

Thank you Liang, I will give your method a try.