Dynamically change data source of published report

[aroyappan]

We develop reports used by multiple clients. Each clients maintains a database with same schema. We have a database that holds information regarding the list of database for the clients. I want to pass in the databaseId through query paramenter from the report URL and the report should pick the right database using the databaseId and dynamically change the datasource. Is this feasible?

Thanks in advance.

[Anonymous · Accepted solution]

Hi @aroyappan,

As @GilbertQ said, 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

Regards,

Xiaoxin Sheng

[hrsingla88]

Suppose we have multiple different SharePoint sites with same data schema and I want to query all of them dynamically based on say an initial query with the list of sites and merge the data. Is it possible ?

[aroyappan]

Thank you Xiaoxin. 

How can i dynamically change data source after I publish the report?

[Anonymous]

Hi @aroyappan,

Currently, power bi service not support to use power query custom function, it only works on desktop side. For your requirement, you can post this to ideas.

Regards,

Xiaoxin Sheng

[Kulwinder]

HI Xiaoxin

Does PowerBi support this feature now? I have similar requirement where I want to change data source , in my case , sql server connection string changes dynamically. DB schema , table  does not change . Only sql server we want to connect to and Database changes. Please let me know . Thanks in advance.

[GilbertQ]

Hi @aroyappan

Yes in theory that is correct.

You would have to have a list of the databases, as well as the returned data from your query.

After which you could then get the end result, which you could then assign to a parameter value, which in turn will then connect to the database.

You will have to do this in the Query Editor and possibly modify some of the M code to achieve this.