Forum Discussion

Byzbkr's avatar
Byzbkr
Frequent Visitor
4 years ago

Changing the datasource

Hello,

I have more than one database in my SQL server. In addition, the tables in the databases are same. I created a report with a specific database. However, I want to give the user the option to change the database of the report they are viewing by giving a dropdown on the visual screen. But I don't want to show the database names as an option to the user. I have a table with names corresponding to database names in a separate sql table. the names written in this table are brought in dropdown and when user is select the name, I want the relevant database to appear. How can this be done? Thanks in advance for your help.

Need Help

5 Replies

  • lbendlin's avatar
    lbendlin
    Super User
    4 years ago

    Before you invest any more time in that be aware that the Power BI Service and specifically the Power BI gateway does NOT support switching databases.  The difference between SQL.Databases and SQL.Database does only exist in Power BI Desktop.  And we haven't even mentioned the security implications yet - as you would have to give all users full access to all databases on that server to make this work.

    • Byzbkr's avatar
      Byzbkr
      Frequent Visitor
      4 years ago

      Hi, I already using Power BI Destkop and I can give all users full access to all databases. So what should I do for switching databases in visulization-pane?

      • lbendlin's avatar
        lbendlin
        Super User
        4 years ago

        - make sure the data source is in direct query mode 

        - enable the preview feature "dynamic M query parameters

        - learn about how to set up parameters and tie them to filters 

        - modify your M to use custom SQL and include the parameter 

         

        Once again - think long and hard before implementing this.