PostgreSQL and Crystal Reports

From PostgreSQL wiki
Jump to navigationJump to search

Purpose of This Document

I started using Crystal Reports around release 7. Until recently I had used it to report against Microsoft Access, Microsoft Outlook, Microsoft SQL Server, and Sybase databases. When I began reporting against a PostgreSQL database I ran into some quirks that were difficult to find solutions for. I hope this document will help others who must use a Crystal Reports and PostgreSQL combination. Pre-requisites

  1. A working version of Crystal Reports. This document was written using Crystal Reports 9.0 Professional as the development tool. Prior versions of Crystal should support reporting against a PostgreSQL datasource with the exception of stored procedures. Your version of Crystal must support editing of the SQL statements generated by Crystal to report against a stored procedure.
  2. I recommend that you install Service Pack 1 and/or search for a hot fix from Crystal. There is a bug in Crystal 9.0 with how Crystal interprets some database column names because of the data type that PostgreSQL uses.
  3. This document was written using PostgreSQL 7.X as the database version. Access to a PostgreSQL database is of course required. If you cannot connect to the database please contact your admin or read the PostgreSQL documentation to check your database settings.
  4. A PostgreSQL ODBC driver. This document was written using ODBC to connect to the database. A driver can be fetched from https://odbc.postgresql.org/. I used version psqlodbc-07 03 0200.
  5. An ODBC connection configured to your PostgreSQL database.

Data Sources

  1. Reporting against tables
  2. Reporting against views
  3. Reporting against stored procedures
    • Without parameters
    • With parameters

Reporting against tables

Reporting against tables can be done with cavets. Tables can be added to Crystal Reports using the database expert like any other database BUT must be a member of the public schema. If the table(s) are not a member of the public schema and you try to add them to your report Crystal will give you an error message(Not supported). If you must report against a table in a schema other than the public schema create a view against the table and report off of that view. You can add multiple tables and link them in Crystal as with any other database.

Reporting against views

Reporting against views is the way most reports against Crystal will be done. There is nothing special you need to know or any special tricks when reporting against views. When creating Views however I recommend that you specify a data type for the column name to ensure that Crystal will be able to read the column name if you are renaming a column or assigning a name to a column.. An example of this is the following: CREATE OR REPLACE VIEW myschema.view_intercultural AS SELECT d.client_name, 'Intercultural Overall'::bpchar AS question FROM myschema.international d; Don't use ::text in place of ::bpchar in the above example, otherwise Crystal will not let you choose the column for grouping.

Reporting against stored procedures

Without parameters

This is the real reason I wrote this mini howto. Reporting against stored procedures in PostgreSQL is much different than what I had experienced with other databases. In order to report against a stored procedure you must be able to tell Crystal what data fields and their types it can expect to receive from the stored procedure. The only specify the data fields is to manually enter the SQL statement into the Crystal report. The syntax is exactly the same as if you were calling the stored procedure using psql with the exception of formatting of any parameters you are passing to the stored procedure. In Crystal 9.0 this is done by selecting from the menu on top Database->Select your ODBC datasource that you set up->Add Command. This will open a pop up window where you can enter the SQL statement directly. The format of the statement for a stored procedure without parameters is the following: SELECT field1, field2 FROM myschema.mystoredprocedure AS (field1 varchar(100), field2 int4) This is SQL for creating the stored procedure referenced in the report: CREATE OR REPLACE FUNCTION myschema.mystoredprocedure RETURNS SETOF record AS ' DECLARE r record; BEGIN for r in EXECUTE \'SELECT t.field1, t.field2 FROM myschema.mytable t ;\' loop return next r; end loop; return; END; ' LANGUAGE 'plpgsql' STABLE; The SQL for the table itself is the following: CREATE TABLE myschema.mytable ( field1 varchar(100), field2 int4 );

With parameters

In Crystal 9.0 this is done by selecting from the menu on top Database->Select your ODBC datasource that you set up->Add Command. This will open a pop up window where you can enter the SQL statement directly. The Create button on the right side of the screen is used to create any parameters that you wish to pass from Crystal to your stored procedure. The format of the statement for a stored procedure with parameters is the following: SELECT * FROM myschema.mytable({?parameter1},{?parameter2}) as recs( field1 varchar(100), field2 int4, submit_date date) This is SQL for creating the stored procedure referenced in the report: CREATE OR REPLACE FUNCTION myschema.mystoredprocedure(date, date) RETURNS SETOF record AS ' DECLARE r record; from_date ALIAS FOR $1; to_date ALIAS FOR $2; BEGIN for r in EXECUTE \'SELECT t.field1, t.field2, t.submit_date FROM myschema.mytable t WHERE t.submit_date >=\'||quote_literal( from_date )||\' and t.submit_date <=\'||quote_literal( to_date )||\' ;\' loop return next r; end loop; return; END; ' LANGUAGE 'plpgsql' STABLE; The SQL for the table itself is the following: CREATE TABLE myschema.mytable ( field1 varchar(100), field2 int4. submit_date date );

Character Sets

Be careful with character sets when using PostgreSQL via ODBC from Crystal Reports, particularly when you are upgrading to use later versions. I (Phil) found that when I moved from using Crystal Reports 8.5 to 2005, special characters wouldn't render properly. In particular, the superscript 2 character (²) was rendered as a very different character (ʕ). Eventually I found that the ODBC data source I had been using inappropriate. It turns out that I needed to change my data source so that it was of the "PostgreSQL ANSI" type instead of the "PostgreSQL" type.

The upshot is that you need to ensure that the ODBC data source type (ANSI or Unicode) needs to agree with your database. It looks like the default driver from earlier versions is interpreted as Unicode, but I'm not sure about this.

Updated: 2009-03-13 10:30