+1-757-461-3022 x124

Third-Party Reporting Tools

Overview

There are some excellent 3rd party reporting tools that integrate nicely with PostgreSQL. Crystal Reports and MS Access are two that you may be familiar with, perhaps you've developed many reports with your legacy ERP that you want to port over for usage with your shiny new xTuple ERP. The answer is ODBC.

Download and install the ODBC driver, psqlodbc, for your version of postgres from here (psqlodbc_08_04_0100.zip should work fine). This gets installed on the workstation that you're running your ODBC enabled application from.  Then configure the ODBC driver for your PostgreSQL/xTuple database installation:

Go to Start -> Settings -> Control Panel -> Administrative Tools -> Data Sources (ODBC).

  • The ODBC Data Source Administrator interface should appear. There are several options for setting up ODBC data sources. This install will be for a User data source.
  • Click the User DSN tab and click the Add button.  The Create New Data Source interface should appear. The only thing to do here is to select the PostgreSQL Unicode driver for the configuration step. 
  • Select the PostgreSQL Unicode driver and click Finish. The interface for configuring the PostgreSQL driver should appear. These settings must be correct, or you will not be able to connect to your database.

Data Source - Is the name used when referring to this datasource.
Database - Is the name of your xTuple database.
Description - Enter a description for the connection.
Server - Is the IP Address (or hostname) of your PostgreSQL server.
Port - The server's port that PostgreSQL is running on.
User Name - You may have to use mfgadmin or admin if your regular user doesn't work, or consult your administrator.
Password - The user's password.

To avoid not being able to see the full columns of datatype Text in Crystal Reports or MS Access and other 3rd party reporting software, you need to now configure the PostgreSQL ODBC driver properly.  The default setting in the ODBC driver is to pass PostgreSQL 'text' datatypes as LongVarChar (with a default length of 8190). However, in Access and Crystal Reports it converts this into a 'memo' datatype instead of a 'text' datatype (in access text = varchar(255)).  In the ODBC driver setup this can be fixed by unchecking the 'Text as LongVarChar' option and making sure the varchar length is 255.  Of course, if your text in your PostgreSQL column is greater than 255 characters, the data will be truncated.

  • Click Datasource On Page 1
  • Uncheck 'Text as LongVarChar' and 'Bools as Char', as in the screenshot

  • Click OK
  • Back on the main screen, click Test. Your connection should work now.
  • Click Save
  • Click OK

You are now ready to use your ODBC connection!

Additional Resources

PostgreSQL & Crystal_Reports wiki

 

up
98 users have voted.