How to have multiple databases defined in 1 gateway and switch reports back and forth between them?

[kdixon5490]

How to have multiple databases defined in 1 gateway and switch reports back and forth between them?

Currently I have have several databases with the same structure on 1 server.    DB1 and DB2.   

How can set up a datasource within my gateway for each of these 2 databases and then be able to point a Powerbi Report to 1 or the other depending on my need at the moment?  I do not want to have to republish the report everytime I want to change databases.

Thanks in Advance.

[Anonymous]

Hi  @kdixon5490,

You can create parameters for Server and Database and change them in the service, this blog perfectly explains how to do it:

https://community.powerbi.com/t5/Community-Blog/Using-the-Power-BI-Service-Parameters-to-change-conn...

Regards

[kdixon5490]

Thank you for help.   I am rather new to powerbi and trying to set up some stuff. 

I have another I did exactly what he did and have everything set up for the parameter.  I go in and enter a new database and try and refresh and it gives me an error that a gateway is not available.     I went into manage gateways and added a new datasource for the new database server combo.  I thought it would attach to that but it did not.  As you can see in the picture I have  datasources the second one in the list was the original datasource and the highlighted one is the new one.

[v-yuezhe-msft]

@kdixon5490,

Based on my test, the parameter feature in Power BI Service doesn't work for on-premises SQL server data source.  When I publish a PBIX file to Power BI Service, it is not possible to change the database parameter in Power BI Service.


Currently, you still need to add all the data sources of your dataset within gateway, and then set schedule refresh for the dataset. Also  make sure the connection strings you use in Power BI Desktop are same as that you use in gateway.

Regards,
Lydia