Can you use Shared Connections in Paginated Power BI Report using stored procedure with parameters

[DAX99]

Hi! I'm working on converting our SSRS reports to Paginated Power BI reports. This is for an on-prem SQL Server database.  In SSRS, we had a Shared Connection (where we defined server and database) and shared Datasets (.rdl files that were linked to stored procedures). I'm able to recreate the reports in Paginated Power BI Report builder but I can't seem to use a Shared Connection - I have no choice but to embed and define the connection in each report. Also I can't use an .rdl file but I have to specify the stored procedure name in the report. Most of our stored procedures take incoming parameters that the end user enters when running the report.  The new Paginated Reports are working but I'm not sure this is the best practice. We would prefer to use a Shared Connection so that we don't have to define the database/server in each report (and thinking it may be more secure). I've been reading about Semantic Model connections but the only way I can get that to work is by setting up a Semantic Model (In PBI Desktop) but I have to attach it to some table or stored procedure. This works for one report we have where there are no user defined parameters. I'm just trying to find out best practices in terms of whether it is even possible to use a shared connection for our situation (on prem database). Also I know we can't use .rdl files but is there some other way to mimic what we had done in SSRS (shared connection). I feel like I'm missing something in terms of how the Gateway, Connection, and Data set all work together. Just wondering if anyone has been in a similar situation and wanting to use a Shared Connection (linked to Gateway). We have Pro, not Premium. Thank you for any feedback!!

[collinq · Accepted solution]

Hi @DAX99 ,

If I am understanding your question correctly then this is my answer:
You can set up an Enterprise Gateway - putting that on a server somewhere that can also get to your data source.  Then, you can create teh Data Source.  And then, you use that Data Source in each paginated report.

And, if you are going to use stored procedures all you have to do is modify the paramaters for each time (if that is even needed).

[collinq]

Hi @DAX99 ,

Power BI does not support shared connections or shared datasets on Paginated Reports.  Each Paginated REport has to define its own connection string.

Therefore, I think that the way you are doing it is probably the best way given the security settings and the methods that the tools allow.  But, you may be able to use parameterized connection string to manage consistency for each of the reports.  

You could use Stored Procedures with parameters in Pagineted Reports.

But, probably the best route would be to use at least the same gateway connection across all of the Paginated Reports and use the parameters or maybe even templates to at least reduce as much duplication as possible.

OR, you could upgrade to Premium where you can host shared semantic models for Paginated Reports - but, I see that you said you do not have Premium and probably purchasing that is outside your abilities at the moment.

[DAX99]

Hi @collinq and thank you for your reply, it is much appreciated.  Good to know that I am probably on the right track.  If you don't mind can I ask a follow-up question as far as how to go about using the same gateway connection across all of the Paginated Reports.  Right now I have my Paginated reports set up with an individual Data Source where I specify the Data Source and Initial Catalog for each report. Then I set up the Data set to be linked to that Data Source and I have specified the stored procedure (with incoming parameters as entered by the end user when running the report).  All is working in the sense that the report works.  But if I can use a shared Gateway in some way or some other shared resource, we would like to do that. I'm not seeing how I can set up the Paginated Report to the Gateway.   As you mentioned yes, I am on Pro and at this time upgrading to Premium I don't think is an option.  Thank you again for your help!!

[collinq · Accepted solution]

Hi @DAX99 ,

If I am understanding your question correctly then this is my answer:
You can set up an Enterprise Gateway - putting that on a server somewhere that can also get to your data source.  Then, you can create teh Data Source.  And then, you use that Data Source in each paginated report.

And, if you are going to use stored procedures all you have to do is modify the paramaters for each time (if that is even needed).

[DAX99 · Accepted solution]

Thank you @collinq !  I will give that a shot, trying if we can set up an Enterprise Gateway.  thank you!