Forum Discussion
Helper IPaginated report builder- multiple Powerquery mysql connections possible
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
5 Replies
- rajendraongole1
Super User
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://learn.microsoft.com/en-us/power-query/power-query-query-parameters
https://www.youtube.com/watch?v=TEIdI5bFCSU
Manage a SQL Server data source - Power BI | Microsoft Learn
- rajendraongole1
ntimmermanHi 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.
