This article by Dr. Jayaram Krishnaswamy shows step-by-step how you may retrieve data from a database on a Postgres Plus server and display the results in a report generated using Report Builder 2.0. Report Builder 2.0 is a report building and maintaining tool provided with SQL Server 2008. The evaluation version of SQL Server 2008 Enterprise edition was used in this tutorial. Report data is obtained via an ODBC DSN. The connection uses an EnterpriseDB ODBC driver that gets installed on the computer when EnterpriseDB is installed.

Overview

The following steps will be followed in authoring an Intranet report using Postgres Plus as the backend database.

1. Creating an ODBC DSN to access the data on Postgres Plus
2. Creating a report with Report Builder 2.0
   • Configuring the data source for the control
   • Configuring the report layout to display data
3. Deploying the report on the Report Server

The categories table in the EnterpriseDB shown will be used to provide the data for the report. The categories table is on the PGNorthwind database on the EnterpriseDB Advanced Server 8.3 shown in the next figure.

Creating an ODBC DSN to access the data on Postgres Plus

Click on Start | Control Panel | Administrative Tools | Data Sources (ODBC) to bring up the ODBC Data Source Administrator window.

Click on Add.... In the Create New Data Source window scroll down to choose EnterpriseDB 8.3 under the list heading Name as shown.

Click Finish. The EnterpriseDB ODBC Driver page gets displayed as shown. Accept the default name for the Data Source(DSN) or, if you prefer, change the name. Here the default is accepted. The Database, Server, User Name, Port and the Password should all be available to you.

If you click on the option button Datasource you display a window with two pages as shown. Make no changes to the pages and accept defaults but make sure you review the pages.

Click OK and you will be back in the EnterpriseDB Driver window. If you click on the option button Global the Global Settings window gets displayed (not shown). These are logging options as the page describes. Click Cancel to the Global Settings window.

Click on the Test button and verify that the connection was successful. Click on the Save button and save the DSN under the list heading User DSN. The DSN EnterpriseDB enters the list of DSN's created as shown here.

Creating a report with Report Builder 2.0

It is assumed that the Report Builder 2.0 is installed on the computer on which both EnterpriseDB's Advanced Server 8.3 as well as the Reporting Services / SQL Server 2008 are installed. To proceed further the SQL Server 2008 Database Engine and the Reporting Services must have started and running.

Start | All programs | Microsoft SQL Server 2008 Report Builder 2.0 will bring up the following display. Click on Report Builder 2.0.

The Report Builder 2.0 gets displayed as shown.

Double click the Table or Matrix icon. This displays the New Table or Matrix window as shown.

Configuring the data source for the control

Click on the New... button. This brings up the Data Source Properties window as shown. Click on the drop-down handle for "Select connection type:" as shown, scroll down and click on ODBC

Click on the Build... button. This brings up the Connection Properties window as shown. Click on the option "Use user or system data source name" and then click on the drop-down handle. In the list of ODBC DSN's on this machine which gets displayed in the list click on EnterpriseDB as shown.

Enter credentials for the EnterpriseDB as shown. Test the connection with the Test Connection button. A Test Results message will be generated displaying success if the connection information provided is correct as shown

Click OK to the two open windows. This will bring you back to the Data Source Properties window displaying the connection string in the window.

Click on navigational link Credentials on the left which opens the "Change the credentials used to connect to the data source". Make note that the credentials you supplied earlier have arrived at this page. If you need to be prompted you may check this option. Herein no changes will be made. Click on the OK button.

In the New Table or Matrix window DataSource1 (in this report) will be high lighted. Click on the Next button.

This brings up the "Design a query" window as shown. In the top pane of the query window type in the SQL Statement as shown. You may also test the query by clicking on the (!) button and the query result will appear in the bottom pane as shown.

A step-by-step guide to getting the most of Microsoft SQL Server Reporting Services 2008 Everything you need to create and deliver data-rich reports with SQL Server 2008 Reporting Services as quickly as possible Packed with hands-on-examples to learn and improve your skills Connect and report from databases, spreadsheets, XML Data, and more No experience of SQL Server Reporting Services required  http://www.packtpub.com/learning-sql-server-2008-reporting-services/book

Configuring the report layout to display data

Click on the Next button. This brings up the "Arrange fields" page of the New Table or Matrix window displaying all the "Available fields" as shown.

Drag and drop from "Available fields" to "Row Groups" and "Values" drop areas as shown by clicking each element and dragging it on to the drop area. After this activity the fields will be as shown in the figure. Click Next.

In the "Choose the layout" page uncheck both the options as shown. By default they come up checked.

Click Next. In the "Choose a style" page, pick a style you like. Herein "Corporate" is picked.

Click Finish. This brings you back to the Report Builder's UI as shown displaying the Report Layout in the center and the datasource related objects on the left.

The report is ready to be run. Click on the "Run" icon on the "Ribbon". This begins the processing and rendering activity and when these processes are completed the report gets displayed as shown.

The "Picture" column is showing error. The reason for this is that the data type in the "Picture" field is image data(binary). Delete the data field under the column "Picture" by right clicking on it and deleting it using "Cut" in the design view. In the Main menu click on Insert and insert an Image control in the location where you removed the "Picture" by dropping the image control in that field. This brings up the image properties window as shown. Change from "Embedded" to "Database" in the drop-down as shown.

This adds additional controls to this page. Click on the handle for the "Use this field" and choose "Picture" from the drop-down. In the "Use this MIME type" choose "image/bmp" as shown.

Click OK. This brings you back to the RB2.0 designer interface with a small image icon embedded in the "Picture" column. Now from the main menu run the report.

You will get a error message, "An error occurred during client rendering. Parameter is not valid". Go back to the design and click on the properties of the image control. In the Image Properties window click to configure an expression for the image field instead of the default, by clicking on fx along side the drop-down for [picture]. This brings up the Expression window as shown.

Remove the default =Fields!picture.Value and in its place insert the following: =System.Convert.FromBase64String(Mid(System.Convert.ToBase64String(Fields!picture.Value),105))
The reason that you need to do this substitution is that the OLE Image header information has to be taken into consideration correctly, see Microsoft Technet. Click OK to close the Expression window.

Now run the report again by clicking the "Run" icon on the "ribbon". This time the report runs successfully as shown.

Deploying the report on the intranet web server

This is the easy part. Click on Office Button and from the dropdown choose "Save As" option. This brings up the "Save as Report" window showing the "http://Hodentek2:8080/ReporatServer_SANGAM". Provide a name for the report (in this case EDB_Report) and click on the Save button. The report gets saved as EDB-Report.rdl. You may now access it on the report server as well as schedule/deliver it from the Report Manager.

Summary

This tutorial described in detail the authoring of a SQL Server Reporting Services' Server Report by retrieving the data from the backend EnterpriseDB server. The report can be modified in many ways using the Report Builder 2.0.

If you have read this article you may be interested to view :

• Installation and basic features of EnterpriseDB
• Migrating from MS SQL Server 2008 to EnterpriseDB
• Creating a VB.NET application with EnterpriseDB

A step-by-step guide to getting the most of Microsoft SQL Server Reporting Services 2008 Everything you need to create and deliver data-rich reports with SQL Server 2008 Reporting Services as quickly as possible Packed with hands-on-examples to learn and improve your skills Connect and report from databases, spreadsheets, XML Data, and more No experience of SQL Server Reporting Services required  http://www.packtpub.com/learning-sql-server-2008-reporting-services/book

About the Author

Dr. Jay Krishnaswamy is a graduate of the Indian Institute of Science, Bangalore writes on database and web development related topics to several computer programming related web sites. He is an active participant in several forums and discussion groups. Before working in the IT industry as a Microsoft Certified Trainer and a Siebel Certified consultant he taught at several institutes of technology and universities in India, Australia, Brazil and the USA. Links to his articles may be found at his website or the blog. He lives in Plainsboro, NJ, USA and may be reached at jkrishnaswamy@comcast.net