Hello everybody,

We currently have an EDB Application with reports done on ReportBuilder using the ElevateDB DADE plugin and the end user report designer.

Most of our reports are built on EDB views and work fine, however there's a report that we need to bind to a stored procedure instead. Due to its complexity we couldn't solve using just views, but we couldn't find a way to bind to a EDB stored procedure. Is this possible? has any one been able to work this out?

Thanks for your help,

Regards,
Mario

Mario


Not using ReportBuilder I'm at a disadvantage since I don't know what the requirements are for binding. One question occurs - does the SP return a cursor/result set? If not that might be the problem.

Roy Lambert [Team Elevate]

Mario,

I have been working with RB for many years. No problem to use a stored
procedure to create the dataset.

As Roy indicated: it doesn't matter how the dataset was created: just
bind the pipeline of a report to any dataset. For the stored procedure
make sure that the procedure returns a dataset.

A sample from the manual:

Note
If you wish to return a result set from a procedure, declare the cursor
in the procedure using the
WITH RETURN clause and leave the cursor open when the procedure completes.

-- The following procedure updates any Customer row
-- with a State column value of 'FL' to 'NY' and returns a cursor
-- on the Customer table.
CREATE PROCEDURE UpdateState()
BEGIN
DECLARE CustCursor CURSOR WITH RETURN FOR Stmt;
DECLARE State CHAR(2) DEFAULT '';
PREPARE Stmt FROM 'SELECT * FROM Customer';
OPEN CustCursor;
FETCH FIRST FROM CustCursor ('State') INTO State;
WHILE NOT EOF(CustCursor) DO
IF (State = 'FL') THEN
UPDATE CustCursor SET 'State' = 'NY';
END IF;
FETCH NEXT FROM CustCursor ('State') INTO State;
END WHILE;
END

Uli

Thank you for help folks, but I'm afraid I didn't explain my self well.

We choose to place all the SQL required for report generation inside the report itself, using DADE. So there is no pipeline for us.

Using DADE (Data Tab on the report designer) you have to option to write custom SELECT statements for the data.
When working with FireBird/InterBase you have the choice to write a Query in the following form:

SELECT * FROM <stored procedure name>

And in MS SQL you can write something like this
EXEC <stored procedure name>

In EDB you should be able to write something like this:
CALL <stored procedure name>

But unfortunately the is not a valid statement for EDB as it expects a SELECT.

We even tried to enclose the call this way, but no luck.

SCRIPT
BEGIN
  USE DB;
  CALL <stored procedure name>
  USE;
END;

I wonder if any of you EDB/RB user has found a workaround to this problem?

Regards,
Mario