Paginated reports essentially began to flourish under SQL Server Reporting Services (SSRS) as part of Microsoft’s previous BI stack and are mainly used for operational reporting, often delivered as PDFs emailed to users on a daily or weekly schedule.

Whereas Power BI is leveraged as a visual analytics tool to enable rapid, ad-hoc data exploration focused on visualizations. This blog will tell you how to connect Azure PostgreSQL in Power BI paginated reports and will highlight the limitation of paginated reports to connect to Azure PostgreSQL and solution for that limitation!

Limitation:

When we connect to paginated report, we will see only few data sources by which we can connect and create the reports.

Data Sources which support Paginated Report:

1. Azure Analysis Services
2. Azure Synapse Analytics
3. Common Data Service (Preview)
4. Enter Data
5. Microsoft SQL Server
6. Microsoft SQL Server Analysis Services
7. Oracle Database
8. Teradata

So, it supports only few data sources to connect and can extract the data into report builder to create reports, if you have a need of connecting to other data source like PostgreSQL Server then below are the 2 ways that we can follow.

Step by Step process to create Power BI Paginated Reports Azure PostgreSQL

Connecting PostgreSQL in Power BI with ODBC

With built-in support for ODBC on Microsoft Windows, ODBC provides self-service integration with self-service analytics tools, such as Microsoft Power BI.

To set up a new ODBC data source for PostgreSQL:

1. Install the latest PostgreSQL ODBC drivers

• Download the latest 64bit driver installer from “https://www.postgresql.org/ftp/odbc/versions/msi/”
• Unzip the file and run the psqlodbc-setup.

2. Open the 64bit ODBC Administrator : Windows 10(64bit version) supports 32 and 64 sources – always use the 64bit ODBC Administrator.

• Search for ODBC and choose ODBC Data Sources (64bit):

• Right Click and choose Run as Administrator.

3. Open the User DSN tab and click Add

4. Choose the latest PostgreSQL ODBC driver.

5. Enter the ODBC credentials:

• Use any Data Source and Description you prefer.
• Enter PostgreSQL Server details in Server field.
• Enter the port number (Default: 5432).
• Enter Username and Password.
• Enter database details.

6. Test the data source connection

• Click Test:

• And if it works you are good to go

• Click on Save to create the ODBC data source:

7. Power BI Desktop

• Connecting PostgreSQL with ODBC from Get Data.
• In DSN à Select Data Source which you have add in ODBC Data Source in 64bit which is (eg : PostgreSQL35W)

8. Extract required tables from Azure PostgreSQL Database and publish those datasets into workspace in Power BI Service.

9. From Power BI Report Builder add the connection which is “Power BI Dataset Connection” to connect Power BI Dataset.

10. Add a new dataset and select the data source and click on query designer to write DAX Query to extract the data from Power BI Dataset.

11. After creating the data set you can create the reports from the datasets.

12. Publish this Paginated Report into Power BI Workspace in Power BI Service.

Limitation:

• Power BI does not support to get data from ODBC data source in a live connection or DirectQuery mode.

Disadvantage:

• Power BI Data Gateway is required to configure and connect to ODBC data source if the data source supports Platform as a Service (PAAS) also.

Azure Database for PostgreSQL

If your data source supports Platform as a Service (PAAS) then it is recommended to use Azure Database for PostgreSQL which supports DirectQuery mode to get live data into PowerBI Repots.

• Data Gateway is not required for both (Import/DrectQuery) mode in Azure database for PostgreSQL because it support Platform as a Service (PAAS).

• Extract required tables from Azure PostgreSQL Database and publish those datasets into workspace in Power BI Service.

• From Power BI Report Builder add the connection which is “Power BI Dataset Connection” to connect Power BI Dataset.

• Add a new dataset and select the data source and click on query designer to write DAX Query to extract the data from Power BI Dataset.

• After creating the data set you can create the reports from the datasets.
• Publish this Paginated Report into Power BI Workspace in Power BI Service.

Note : Workspace needs to be Premium not Power BI Pro License.

If you have a requirement to create a paginated report (or) migrate SSRS reports and if the data source is Azure PostgreSQL, you can follow 2nd scenario. If the data source is on-premises PostgreSQL then you can follow 1st scenario. You can effortlessly share reports based on ODBC data sources with other users in your organization using a Power BI Report Server through the above scenarios.