Re: Stored Procedure/Return Rowset/Crystal

Thank you Jason!!!!!!!!!!!!!!!!!!!!!!

That worked perfectly!

Mike

Michael -

I too had horrible luck getting Crystal Reports to call PostgreSQL

functions when setting them up as a "stored procedure".

However, I had some success using the "Add Command" functionality in the

database explorer. This lets you input arbitrary SQL (including selects

from set-returning functions). You also retain the ability to

substitute any Crystal parameters into the query. It seems that after

doing one "sample" run, Crystal learns what columns are coming back from

the set-returning function. The command then becomes a pseudo-table,

appearing right next to your other tables, and allowing you to drag and

drop fields onto your report.

I've used the above technique extensively in liked subreports, so I can

get one call to the function per record in the master report.

Does that help at all?

On Wed, 2003-11-12 at 05:38, Godshall Michael wrote:

> I am trying to develop a report using Crystal that reports against a

> stored procedure. The stored procedure creates a table based on a few

> queries, a few parameters input, and the returns the rows of the table

> with counts of specific records based on grouping. Crystal would then

> display the results of these counts and within Crystal I would

> breakdown these groups into smaller groups with counts.

>

> I can execute the functions and have it return either rowsets or

> record sets in a pgadminIII window. However when I try to use Crystal

> to report against it I get an error when adding it via the database

> expert. "Unable to identify a function that satisfies the given

> argument types. You may need to add explicit typecasts."

>

> If I create a view against the stored procedure and have Crystal point

> to the view then it allows me to add the view to the report and it

> works fine. The problem with this is that I then lose my ability to

> pass the parameters to the stored procedure when the report is run.

>

> The report requirements force much of the grouping and counting to be

> done before passing the data to Crystal since by design Crystal seems

> to only allow all of the rows of the table to be parsed from beginning

> to end once.

>

> Is anyone aware of a workaround that would enable me to report against

> a function which returns a rowset or record set in Crystal?

>

>

> Michael Godshall

> GMAC Global Relocation Services

> 900 So. Frontage Road

> Woodridge, IL 60517

> 630-427-2070 office

> 630-972-2287 fax

>

> michael_godshall ( at ) gmachs ( dot ) com

--

Jason Topaz

Michael Godshall
GMAC Global Relocation <http://www.gmacglobalrelocation.com/> Services
900 So. Frontage Road
Woodridge, IL 60517
630-427-2070 office
630-972-2287 fax

michael_godshall(at)gmachs(dot)com <mailto:michael_godshall(at)gmachs(dot)com>