Power BI Embedded Change data sources and gateways dynamically

[Anonymous]

Scenario:

Power BI report embedded in an application connected to SQL server using Gateway.

I want to use the same report (Report ID, Workspace ID) but change the data source dynamically to another SQL server using separate Gateway. This is to handle multiple customers having different servers & databases. Database schema is the same

Is this possible?  

[MRIZ]

Hi, have you ever able to achieve this?? I am in same situation right now. need help.

Regards,

Rizwan

[Anonymous · Accepted solution]

Hi @Anonymous ,

if you want to dynamic change the datasource, you can refer to below steps:

1. Create a table with these datasources.(id, server name, database name , item)

2. Write a power query custom funciton which used to analysis the records and connect to datasource.(function parameters: server name, user name, password)

3. Filter the datasource table and use the filtered records to inveke the custom funciton.

Sample of sql database

let
    loadData=(Servername as text, DbName as text, itemName as text)=>
    let
        Source = Sql.Databases(Servername){[Name=DbName]}[Data]{[Schema="dbo",Item=itemName]}[Data]
    in
        Source
in
    loadData

Use:

Table structure: id, server name, db name, item name

let
Selected = Table.SelectRows(SourceTable, each [id] = 10),
ServerName= List.First(Selected[server name]),
DBName= List.First(Selected[db name]),
ItemName=List.First(Selected[item name]),
Source= loadData(ServerName,DBName,ItemName)
in
Source

More details : Solved: Dynamically change data source of published report - Microsoft Power BI Community

Solved: Change gateway datasource - Microsoft Power BI Community

Please refer to the document to see if it helps you.

Manage on-premises data gateway high-availability clusters and load balancing | Microsoft Learn

If it still does not help, you can create an idea here:  Ideas (powerbi.com)

Best Regards

Community Support Team _ Polly

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

[Sairam_V]

@Anonymous , Actually it is working in the PBI desktop. Not in the service. producing error