Sharing a Standard Report with Different Clients

[RaphaelNeto]

Dears

I've cracking my head around this issue and been reading all sort of documentation but I'm still quite lost in how I will accomplish this requirement.

I've been working for a single company solely creating reports and dashboards within the organization and unfortunately most of the administration and sharing possibilities have been lest aside as was handled by the IT team. Now in my new job I'm working with a consulting company building their BI projects with Power BI and this hiccup has presented to me.

We are building a Frame or Template report to sell to our clients, these potential client all have the same underlying data structure, therefore, our goal is to maintain a single pbix file while having the capacity to dynamically change the connection details and point to each of our clients DBs.

If all the DBs was locate under our company domain accessible with the same gateway we could create parameters in the desktop and using power shell (probably) create a script to dynamically change the server/DB in the online portal. Inviting each client to our domain as guest we could them share it with each of them with the external sharing option. (am I correct here?)

But as the DB will be sitting in each individual domain, requiring different gateways, while I'm logging with my own PBI credentials I won't be able to see any of these connections and won't be able to set the connection details to change dynamically.

How would I accomplish the above? creating a cloud DB in Azure (extra costs) using Azure Active Directory and service principal is the way that would solve my problems? or this situation is unsolvable and I'll have to access each client pbi portal using an email on their domain to have it set up every time I make changes to the template pbix?

 Hoping this is enough but please let me know if you require any further detail.

Thank you all for the help and keep well

[ugurdemir]

Just an idea, not tested yet;

Pushing the data to cloud as csv files and setting a dynamic parameter for the folder of csv files

[v-diye-msft]

Hi @RaphaelNeto 

If the above posts help, please kindly mark it as a answer to help others find it more quickly. thanks!

If not, please kindly elaborate more.

[collinq]

Hi @RaphaelNeto ,

We once had the same situation, we resolved it by using RLS on the report.  And, we had an existing "portal" for the external clients to log into already so we used the UPN for the sign in to set the RLS to make sure that the right clients only saw their data.  They all needed a license but we worked through that as to who would buy the licenses.

I would appreciate Kudos if my response was helpful. I would also appreciate it if you would Mark this As a Solution if it solved the problem. Thanks!

[RaphaelNeto]

Hi Collinq

Please correct me if I'm wrong but in your case you had all the data built in a single model (data from all clients) and used RLS to slice according to whom login. But see my problem is that I dont have all the data under my belt ,the clients data is sitting in completely different locations and I need to find a way to the pbi online portal to redirect the connection to each client DBs without the need to me to log in in each client pbi online portal and manually change the connection string from my own DB (development DB) to their DB (production DB).

please let me know if this clear.

Thank you though

[collinq]

Hey @RaphaelNeto ,

What about creating a Dynamic Parameter in the report that points to the right DB based on the client?  I think that would work - this would be your datasource parameter.  Each Query would point to the parameter.  Then, when the client logs in, they get the right paramater based on the sign on/client name/whatever.  (The setup would be a one-time pain but this is very scaleable and if a client isn't a client anymore very easy to remove a paramater.)

I would appreciate Kudos if my response was helpful. I would also appreciate it if you would Mark this As a Solution if it solved the problem. Thanks!

[RaphaelNeto · Accepted solution]

If I could access all the databases using a single user, yes that was the solution, but I'll have a different user for each client as their have their data on premises in different servers.

I'm almost sure there is something related to Azure Active Directory, but I'm lost there, if someone could point the directions please.

Thanks you all

[RaphaelNeto]

No solution has been founded. closed the tread