Paginated report builder- multiple Powerquery mysql connections possible

[ntimmerman]

Hello community,

For the project I am working on currently, I need to build a report that can switch the connection between multiple databases (with the same schema) based on a parameter. I get so far that I have a list of instances that the user can choose from (essentially servername, database name) and a friendly name for the user. However, once I use these values as parameters for the powerquery connection, it wants to connect but I get a message that I haven't provided credentials: 'Credentials are required to connect to the MySql datasource' 

However, there doesn't seem to be an option in the powerquery window to add a connection:

Any clue how this can be accomplished?
Many thanks

[rajendraongole1]

Hi @ntimmerman - In Power Query, create parameters for ServerName and DatabaseName.
Populate the parameters dynamically by providing a list of server and database combinations or hardcode them for testing.

Use the parameters in your data source connection,

• Power BI requires credentials for each connection individually. Even if the schema is identical, credentials must be stored per database.
• Go to File > Options and Settings > Data Source Settings in Power BI Desktop.
• Add credentials for each possible combination of server and database manually.

More details please check the below learn objective.

https://community.fabric.microsoft.com/t5/Service/Dynamic-DataSources-in-Power-BI-Report-Builder/m-p...

https://learn.microsoft.com/en-us/power-query/power-query-query-parameters

https://www.youtube.com/watch?v=TEIdI5bFCSU

https://community.powerbi.com/t5/Desktop/Connecting-a-Power-BI-report-to-a-DB-Dynamically-based-on-t...

Manage a SQL Server data source - Power BI | Microsoft Learn

[ntimmerman]

Hi @rajendraongole1 ,

Thanks for your reply! However, my question was regarding paginated report builder. (Power BI Report Builder) Not PowerBI desktop. I can't find similar functionality in the paginated report builder.

[rajendraongole1]

Hi @ntimmerman -you can create Parameters for Server and Database, please follow the steps.:

Go to the Report Data pane in Report Builder.
Add two parameters:
ServerName: The name of the SQL server
DatabaseName: The target database name.
You can also create a parameter to allow users to choose predefined connections if needed.
Use Expressions in the Data Source Connection String:

Right-click on the data source and select Properties.
in connectin string we can configure
="Data Source=" & Parameters!ServerName.Value & ";Initial Catalog=" & Parameters!DatabaseName.Value
Since Report Builder does not prompt for credentials dynamically with parameterized data sources, you need to:
Use Stored Credentials (with a shared data source on a server).
Alternatively, set credentials in the report server where the report is hosted.
Navigate to the Credentials tab in the data source properties and configure them accordingly.

[ntimmerman]

Hi @rajendraongole1 ,

Thanks for your explanation! 

However, the datasource I want to connect to is a MySQL database. not a SQL server database. Any clue how that can be accomplished? Thank you

No MySQL option in the dropdown list...

[rajendraongole1]

Hi @ntimmerman - if the above suggestions works please mark this as a solution 

Thank you