The following steps will be followed in authoring an Intranet report using Postgres Plus as the backend database.
- Creating an ODBC DSN to access the data on Postgres Plus
- Creating a report with Report Builder 2.0
- Configuring the data source for the control
- Configuring the report layout to display data
- 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.