Nisarg Upadhyay
ODBC Data source

Create SSRS reports for PostgreSQL using SSDT 2017

March 7, 2022 by

In this article, we are going to learn how we can create an SSRS report to populate the data from PostgreSQL 13 using SSDT 2017.

Environment Setup and use case

I have installed PostgreSQL 13 and SQL Server data tools 2017 on my workstation. You can read How to install PostgreSQL on Windows article to learn the step-by-step installation process of PostgreSQL. I have restored a database named UKLandRegistery. We can view the databases of PostgreSQL using the following query.

Output

Database list

I have installed a PostgreSQL ODBC and driver and configured an ODBC data source named LandRegisteryReport.

ODBC Data source

We are using the LandRegisteryReport data source to populate the data from the database.

Create an SSRS report

We are going to create a Reporting service project using SSDT 2017. Launch SQL Server data tools 2017 🡪 Create New Reporting service Project 🡪 On the new project dialog box, select Reporting Services 🡪 Select reporting Services Project. Enter the LandRegisteryData as a project name.

SSRS project in SSDT 2017

Once the project is created, the report designer opens. In the Solution Explorer, you can view three directories named Shared Data Sources, Shared Datasets, and Reports. Right-click on the Reports folder 🡪 Hover on Add and select New Item.

Create a new report in SSDT 2017

In the dialog box, specify the LandRegisteryReport.rdl as the SSRS report name.

New Report Item

An empty report will be created. To populate the data in the report, we must configure a Data Source and dataset. The Data Source is used to connect to the database and run the query. The data populated by the data source is loaded in the dataset. First, let us create a data source.

Configure data source

You can view the data sources are in the Report Data pan. To create a new data source, right-click on the data source and select Add data source.

Add Data Source...

A data source dialog box opens. Specify the data source’s name, select ODBC from the Type drop-down box. Click on Build to configure the ODBC connection.

Data Source properties

Another dialog box, Connection Properties will open. Select LandRegisteryReport from the user or system data source name drop-down box. Click OK to save the connection.

Specify ODBC Datasource

Back to data source properties, you can see the connection string.

Build connection string

Click OK to save the data source. You can see that the new data source has been created in the Data Source folder of the Report Data pan.

Data Source in SSDT 2017

Now, let us create the dataset.

Create new dataset

To create a data set, right-click on the dataset and select Add dataset.

Create new Dataset

A dialog box, Dataset properties, opens. In the dialog box, you can set the following parameters.

  1. Data source name: Select data source name from the drop-down box. In our case, the data source name is DsetLandregistery.
  2. Query type: You can choose any of the following options.
    1. Text: Specify the query text
    2. Tables: Specify the name of the table.
    3. Stored procedure: Select a stored procedure from the list
    4. You can also build a SQL query using the query builder.
  3. Filters: You can specify the filters that you want to add to the dataset retrieved by a query.
  4. Parameters: The parameters are used to create a parameterized report.

We are creating a list report in this demo, so I have not configured parameters and filters on it.

Configure dataset properties

Once the dataset is created, you can view the list of the fields under the dataset.

Dataset has been created

Now, let us design the report.

Design an SSRS Report in SSDT 2017

We want to create a simple list report to view the data, and the data should be in tabular format so, drag and drop the table from the SSRS report toolbox.

Add table

Now, to add data from the dataset, open report Data, drag and drop the dataset fields in the “data” row of the table.

Add fields from dataset

The final report design looks like the following image:

Report design

Now, click on the Preview tab to see how this report looks.

Report Preview

Now, let us deploy the report.

Deploy report on the reporting server

I have configured the SSRS report server on my workstation. To deploy the SSRS report, open Solution Explorer 🡪 Right-click on LandRegisteryReport.rdl and select Deploy.

Deploy report using SSDT 2017

The deployment process begins. Once the deployment completes, open the URL of the report. As you can see, we have encountered the following error:

An error has occurred during report processing. (rsProcessingAborted)
Cannot create a connection to data source ‘DsLandRegistary’. (rsErrorOpeningConnection)
ERROR [IM002] [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified

Screenshot of error:

Data source name not found and no default driver specified Error

To resolve this error, we must change the ODBC connection string which is embedded in the report. We can change the data source properties from the SSRS web portal. Navigate to SSRS Report 🡪 Click on (…) on the report and click on Manage.

Manage datasource

On the Manage LandregisteryReport screen, click on Data Sources and specify the connection string in the following format.

Driver={PostgreSQLUNICODE};Server=Host;Port=5432;Database=UKLandRegistery;Uid=username;Pwd=password

Screenshot:

Connection string format

Click on Test Connection.

Manage report connection

As you can see, the connection is established successfully. Save the changes and open the LandregisteryReport report.

Report has been created

As you can see, the report has been opened successfully.

Summary

In this article, we understood how we could create an SSRS report to show the data from the PostgreSQL database. This article covers the following topic:

  1. How to create the Datasource to populate data
  2. How to create the dataset and use it to display the data on the SSRS Report
  3. How to deploy the SSRS report on SQL Server reporting services portal

Nisarg Upadhyay
Latest posts by Nisarg Upadhyay (see all)
PostgreSQL, Reporting Services (SSRS)

About Nisarg Upadhyay

Nisarg Upadhyay is a SQL Server Database Administrator and Microsoft certified professional who has more than 8 years of experience with SQL Server administration and 2 years with Oracle 10g database administration. He has expertise in database design, performance tuning, backup and recovery, HA and DR setup, database migrations and upgrades. He has completed the B.Tech from Ganpat University. He can be reached on nisargupadhyay87@outlook.com

786 Views