Dynamic connection string based on list

[LordOfAfford]

Hi,

I want to be able to connect to different SQL database environments using a dynamic connection string.
Let's say I have Table list defined with the connections

How do I get this data into a source connection so it's updated dynamically by the other values from the list ?

Is there a way the report can have a drop down list from the values Test1, 2 and 3 and when selected the Test1\Test1 will be updated to value Test2\Test2 and then the report will refresh the data based on the new source connection to the other database.

I tried the approach of a parameter but you can not have that as a drop-down selection list on your report.

I tried to have the Table list in the parameter, but the parameter is not updated when you change the drop-down Table list on your report.

Is there any solution for this ?

We are using on premise Power BI server.

Regards

[LordOfAfford]

I'm not actually looking for a template file that can be opened on demand by developers but rather a report with this embedded in it as a choice at runtime when they open the report on the Power BI Server. And then based on what the choice of their parameter will be they can see the contents of tables on that database with the selected paramter.

[jennratten]

The data sources would have to be cloud-based (not requiring a gateway, at least not in the PBIX, since each on-prem data source requires a separate gateway connection and that cannot be done dynamically).  Further, since the user should be making selections from an embedded report, the options for the user would need to be available in the data model (not Power Query). I think you would need to include queries for each datasource that would be an option to select, load all of them to the model with applicable visuals and then present the choice to the user in the form of bookmarks so the user can toggle.  

[jennratten]

Hello - you can create parameters that have a list of suggested values.  These will appear as drop-down menus for the user to select.

Then create your query to use those parameters.

If you like, you can then save your file as a PBIT (template).  This will show the parameters when the file is opened and prompt the user to make selections and refresh based on those selections.

Please let me know if you are looking for something different.