Forum Discussion

aroyappan's avatar
aroyappan
Frequent Visitor
9 years ago
Solved

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.

Need Help
  • Anonymous's avatar
    Anonymous
    9 years ago

    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

6 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    9 years ago

    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's avatar
      hrsingla88
      Frequent Visitor
      8 years ago

      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's avatar
      aroyappan
      Frequent Visitor
      9 years ago

      Thank you Xiaoxin. 

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

      • Anonymous's avatar
        Anonymous
        Not applicable
        9 years ago

        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

  • GilbertQ's avatar
    GilbertQ
    Super User
    9 years ago

    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.