cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
aroyappan Frequent Visitor
Frequent Visitor

Dynamically change data source of published report

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.

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support Team
Community Support Team

Re: Dynamically change data source

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

Community Support Team _ Xiaoxin Sheng
If this post helps, please consider Accept it as the solution to help the other members find it more quickly
Learning resources: Power BI

View solution in original post

6 REPLIES 6
Super User
Super User

Re: Dynamically change data source

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.


Did I answer your question? Mark my post as a solution!
"Proud to be a Datanaut!"
Power BI Blog
Community Support Team
Community Support Team

Re: Dynamically change data source

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

Community Support Team _ Xiaoxin Sheng
If this post helps, please consider Accept it as the solution to help the other members find it more quickly
Learning resources: Power BI

View solution in original post

aroyappan Frequent Visitor
Frequent Visitor

Re: Dynamically change data source

Thank you Xiaoxin. 

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

Community Support Team
Community Support Team

Re: Dynamically change data source

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

Community Support Team _ Xiaoxin Sheng
If this post helps, please consider Accept it as the solution to help the other members find it more quickly
Learning resources: Power BI
Highlighted
Kulwinder Frequent Visitor
Frequent Visitor

Re: Dynamically change data source

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.

hrsingla88 Frequent Visitor
Frequent Visitor

Re: Dynamically change data source

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 ?

Helpful resources

Announcements
New Topics Started Badges Coming

New Topics Started Badges Coming

We're releasing new versions of the badge that everyone's talking about. ;) Check your inbox for notifications.

MBAS 2020

Save the new date (and location)!

Our business applications community is growing—so we needed a different venue, resulting in a new date and location. See you there!

Difinity Conference

Difinity Conference

The largest Power BI, Power Platform, and Data conference in New Zealand

Power Platform 2019 release wave 2 plan

Power Platform 2019 release wave 2 plan

Features releasing from October 2019 through March 2020

Top Solution Authors
Top Kudoed Authors (Last 30 Days)