Relational data for matrix visual in Report Builder

[PowerEnthusiast]

Hi there,

I am currently working on creating Paginated reports in Report Builder. 

I would like to create various tabular reports by using data from more than one table - reading from SQL.

Unfortunately, I am unable to create a matrix view based on data from more than one table - I am one able to take data from one table per visual. 

Is it possible to create tabular reports based on more than one SQL table? 

***I have created the relationship in SQL already.

Kind regards,

[alwweb2]

I know this is a really old post, but in case others are looking for the answer, here it is.

Yes, Report Builder restricts each data bound object (like a table or matrix) to come from a single dataset, but not a single table.  You have a couple of options:

1) Use Power BI Desktop to create a model that has everything that you need.  Then, when you add your data source, create a Power BI Dataset Connection as the source.  You will then use the Query Designer to build a DAX query that pulls all of the data from all of the tables and measures that you have defined. I have not been able to find any great online resources on how to properly set this up, but I'm currently writing some courseware that will give at least one or two samples of how to configure the dataset in Report Builder and will have a sample PBIX file with a model in the class files so that you can reverse engineer it.

2) You can use your relational SQL Server as the source and use the SQL Query Designer in the Dataset Properties window and use the GUI interface (or write your own code) to create JOINs between the tables you need to use in the matrix and bring all of the data into a single dataset.

Good luck.

[Anonymous]

Definitely, you can import (or direct query) multiple tables. Just use the wizard to select multiple tables and build your relationships in Power BI akin to how you would join in SQL.

Personally however I'd just add the tables I want into SQL Server Analysis Services model there and then use a live connection to Power BI.

Worst case scenario you could make a materialized view in management studio and connect to that single table.

[PowerEnthusiast]

Hi @Anonymous . Thank you for the response.

Would I be able to do this in Report Builder? The tabular reports that I am building cannot be done through the normal table and matrix visuals found in Power BI.

Kind regards.