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.
|
1 2 3 |
postgres=# select datname from pg_database; |
Output
I have installed a PostgreSQL ODBC and driver and configured an ODBC data source named LandRegisteryReport.
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.
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.
In the dialog box, specify the LandRegisteryReport.rdl as the SSRS report name.
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.
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.
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.
Back to data source properties, you can see the 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.
Now, let us create the dataset.
Create new dataset
To create a data set, right-click on the dataset and select Add dataset.
A dialog box, Dataset properties, opens. In the dialog box, you can set the following parameters.
- Data source name: Select data source name from the drop-down box. In our case, the data source name is DsetLandregistery.
- Query type: You can choose any of the following options.
- Text: Specify the query text
- Tables: Specify the name of the table.
- Stored procedure: Select a stored procedure from the list You can also build a SQL query using the query builder.
- Filters: You can specify the filters that you want to add to the dataset retrieved by a query.
- 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.
Once the dataset is created, you can view the list of the fields under the dataset.
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.
Now, to add data from the dataset, open report Data, drag and drop the dataset fields in the “data” row of the table.
The final report design looks like the following image:
Now, click on the Preview tab to see how this report looks.
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.
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:
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.
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:
Click on Test Connection.
As you can see, the connection is established successfully. Save the changes and open the LandregisteryReport report.
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:
- How to create the Datasource to populate data
- How to create the dataset and use it to display the data on the SSRS Report
- How to deploy the SSRS report on SQL Server reporting services portal
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
- Different ways to identify and change compatibility levels in SQL Server - July 22, 2024
- Copy SQL Databases between Windows 10 and CentOS using SQL Server data tools - October 19, 2022
- Changing the location of FILESTREAM data files in SQL Database - October 14, 2022