全部博文(21)
分类:
2008-02-17 17:36:35
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.
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 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.
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 );
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 );