Report Builder - multiple data sources

[powerbiexpert22]

I need to create paginated report using report builder based on below data sources. how to do this using report builder?

• AWS Aurora PostgreSQL
• External ODBC MS SQL
• Excel/CSV 

[powerbiexpert22]

Hi @v-kpoloju-msft ,

do i need gateway for MSSQL and Excel to refresh paginated report available on service for end users?

if yes, then can i use single gateway to refresh both data sources (MSSQL and excel) or I need to install two gateways one for excel and another for MSSQL?

[v-kpoloju-msft]

Hi @powerbiexpert22,
Thank you for the follow-up query.

My answer is yes, if your paginated report is published to the Power BI Service and needs to connect to on-premises MSSQL and local Excel/CSV files, you will need an on-premises data gateway to refresh or load data for end users.

You do not need two separate gateways, a single standard mode on-premises data gateway can handle multiple data sources (including MSSQL and Excel) as long as both are configured under that gateway in the Power BI Service.

Install one standard mode gateway on a server or machine that has network access to both the SQL Server and the folder/location where the Excel/CSV files are stored.

In the Power BI Service, add two separate data source connections under that same gateway. One for the MSSQL database (using the right authentication). One for the folder or file path containing the Excel/CSV files. Map these data sources to your published paginated report.

Refer these links:
Install an on-premises data gateway | Microsoft Learn
Publish a paginated report to the Power BI service - Power BI | Microsoft Learn

This way, you keep the setup simple, and your report will refresh correctly for end users without needing multiple gateway installations.

Hope this helps. If you have any doubts regarding this, please feel free to ask here. We will be happy to help.

Thank you for using the Microsoft Community Forum.

[powerbiexpert22]

Hi @v-kpoloju-msft , there is one more data source Amazon Aurora PostgreSQL , i know it is cloud data source and may not required gateway however due to some security restrictions I may need gateway , do I need sperate gatway or same gateway I can use for PostgreSQL?

[v-kpoloju-msft · Accepted solution]

Hi @powerbiexpert22,
Thank you for the follow-up question.

You are correct. Amazon Aurora PostgreSQL is a cloud data source, and in most scenarios, it can connect directly without a gateway if it is publicly accessible or uses a cloud connector.

However, since in your case there are security restrictions (for example, private network, VPC, firewall rules) and the Aurora instance is not directly accessible from the Power BI Service, you can still use the same on-premises data gateway you have already set up for MSSQL and Excel.

The gateway just needs network connectivity to your Aurora PostgreSQL instance (e.g., via a VPN or private link). Install the correct 64-bit ODBC or Npgsql driver for PostgreSQL on the gateway machine. In the Power BI Service, create a new data source connection under that same gateway for Aurora PostgreSQL.

So, no separate gateway installation is needed one standard mode gateway can manage all three: MSSQL, Excel/CSV, and Aurora PostgreSQL, as long as it is configured with the right drivers and network access.

Refer these link:
Power Query PostgreSQL connector - Power Query | Microsoft Learn
What is an on-premises data gateway? | Microsoft Learn

Hope this helps clarify things and let me know what you find after giving these steps a try happy to help you investigate this further.

Thank you for using the Microsoft Community Forum.

[v-kpoloju-msft]

Hi @powerbiexpert22,

Just checking in to see if the issue has been resolved on your end. If the earlier suggestions helped, that’s great to hear! And if you’re still facing challenges, feel free to share more details happy to assist further.

Thank you.

[v-kpoloju-msft]

Hi @powerbiexpert22,

Thanks for reaching out. Also, thanks to @bhanu_gautam, for his inputs on this thread. 

You are trying to build a paginated report in Report Builder using data from AWS Aurora PostgreSQL, an external ODBC MS SQL source, and Excel/CSV files. Here is how you can approach this.

Report Builder supports connecting to multiple data sources, but each dataset must be tied to a single source. So, while you can include multiple datasets in one report, you can't directly join them across sources within the same dataset.

Connect to AWS Aurora PostgreSQL: Use the ODBC data extension in Power BI Report Builder. Make sure you have the 64-bit ODBC driver for Aurora PostgreSQL installed. You will also need to configure the Power BI Gateway to allow cloud access to your Aurora PostgreSQL instance. Power BI gateway and Report Builder support for ODBC data sources - Power BI | Microsoft Learn

Connect to External ODBC MS SQL: Similar to Aurora, use the ODBC data extension. Ensure the System DSN is configured correctly on the gateway machine. Use supported authentication methods like Windows or Basic.

Use Excel/CSV Files: You can embed data directly into your report or use Power Query to connect to Excel/CSV files. For embedded datasets, you can paste data from Excel directly into Report Builder. Enter data directly into a paginated report in Report Builder - Power BI | Microsoft Learn
Connect paginated reports to data sources using Power Query - Power BI | Microsoft Learn

Hope this helps. If you have any doubts regarding this, please feel free to ask here. We will be happy to help.
Thank you for using the Microsoft Community Forum.

[powerbiexpert22]

Hi @bhanu_gautam ,

In case if i build paginated report based on power bi semantic model (published on power bi service) then can i use parameters in this case?

[bhanu_gautam]

@powerbiexpert22 Yes, you can use parameters in a paginated report based on a Power BI semantic model published on Power BI Service.

Create a paginated report based on a Power BI shared dataset - Power BI | Microsoft Learn

[bhanu_gautam]

@powerbiexpert22 Start by creating a new report and selecting a blank report template. Next, add your data sources. For AWS Aurora PostgreSQL, go to the "Data Sources" pane, right-click, and select "Add Data Source." Name your data source, select "Use a connection embedded in my report," choose "PostgreSQL" as the connection type, and configure the connection string with the necessary details like server name, database name, user name, and password. Test the connection to ensure it is working. Similarly, for External ODBC MS SQL, add a new data source, select "ODBC" as the connection type, and configure the connection string with the DSN, user name, and password. For Excel/CSV, add a new data source, choose "Microsoft Excel" or "CSV" as the connection type, and browse to locate your file. Test all connections to ensure they are working properly.