How to connect to Power BI dataset

[meddojeddo]

I want to build a paginated report which uses a dataset created in Power BI desktop. We do not have Power BI Premium, only Report Server. Is it possible for me to host this dataset somewhere where the paginated report can connect to it? 

[d_gosbell · Accepted solution]

You cannot connect to Power BI Datasets hosted on Power BI Report Server. But what you can do on-prem is to re-create the dataset as an Analysis Service Tabular model, then you can connect your paginated report to that (plus you can connect Power BI to the same data set using a live connection and publish those reports to Power BI Report Server)

[meddojeddo]

@d_gosbell are you able to implement user defined parameters in the Analysis Services Tabular data model to be used with the paginated report?

[d_gosbell]

---

@meddojeddo wrote:

@d_gosbell are you able to implement user defined parameters in the Analysis Services Tabular data model to be used with the paginated report?

---

It depends what you mean by "user defined parameters", if you talking about the "What if" parameters in Power BI Desktop then these just generate a calculated table of numbers and you could copy the table expression from Power BI and paste it into a SSAS model. 

[meddojeddo]

@d_gosbellwhat I mean is input parameters where the user is prompted, for example, to select a date range or an employee and the date is filtered based on the imput. Like in a stored procedure basically. How can this be included in a Power BI dataset query?

[d_gosbell]

---

@meddojeddo wrote:

@d_gosbellwhat I mean is input parameters where the user is prompted, for example, to select a date range or an employee and the date is filtered based on the imput. Like in a stored procedure basically. How can this be included in a Power BI dataset query?

---

Oh, so parameters like that are not defined in the model, they are just included in the query. The query designer in Report Builder has an option when you drag a column into the filters pane which you can tick to turn the filter into a parameter.

Or you can just declare the parameter in your query

eg

EVALUATE FILTER('Product', 'Product'[Color] = @color ) 

This would define a parameter called @color but then you have to manually create a query parameter reference and then link that to a report parameter. (this is because a report can have multiple datasets so you can have one parameter at the report level which passes down to parameters on each dataset).

So it's much simpler when you are first starting out to just tick the box in the filters pane in the query designer and let the designer build all that for you.