Connecting Crystal Reports XI Standard to PostgreSQL

  2006-05-22


At Synthesys we’re using PostgreSQL as the database for a large web application.� For the time being we’re writing our reports in Crystal Reports, although we’re hoping to move to a free platform in the future.

Unfortunately we only have Crystal Reports XI Standard Edition which evidently doesn’t support ODBC connections.� Crystal wants you to buy the Professional Edition to get that kind of enterprise functionality.

Our first attempt to get the two talking consisted of using Microsoft Access’s External Table Linking capability.� While this was horrifying in concept, it actually worked beautifully until you needed to sort by a PostgreSQL text field.� Microsoft Access read text fields as memo fields, and you can’t sort by memo fields.

So back to the drawing board.� Our next attempt was to use PgOleDb in hopes that Crystal allowed that.� For some reason Crystal allows OLE connections, but not ODBC.� However, whenever we tried to add a table to a report, we received an OLE/ADO error.� Have I mentioned I hate Window’s data access technologies?� Well I do.

Our final attempt consisted of connecting to ODBC through OLE.� I don’t know why this is possible, but it is.� Not only is it possible, but it works beautifully.

For some bizarre reason you can connect to a PostgreSQL database from Crystal Reports XI Standard Edition if you use an OLE wrapper around a PostgreSQL ODBC connection.

Many thanks to psqlODBC, and no thanks to Crystal.