Call Stored Procedures (with Parameters) using Crystal Reports 7

[antzzz]

I would like to call Stored Procedures with Parameters from an Informix Dynamic Server v 7.3 (Win 2000) database using Crystal Reports 7.

How do you do this? Everytime I try to specify a Stored Procedure as the source for my Report, it is detected and even asks to specify a Parameter BUT even if I do specify a valid and correct parameter, Crystal Reports complains that the Stored Procedure cannot be resolved (even if it was able to detect it!).

I know you can use Crystal Reports Query Designer to call a Stored Procedure but how do you specify a parameter in the query itself.

Pls. help The Report is needed urgently!

 

[Guest_imported]

I'm sorry, but I don´t Know your answer, but I need help about Informix Dynamic Server v 7.3 and Stored Procedures, you could tell me a tutorial in

http://www about

it. Thanks, thanks, very much.

 

[dmooreora]

Yes,

It is true that you can used stored procedures in Crystal Reports with Informix and other databases. Unfortunately, the stored procedure only works if it returns values into a datasource, i.e. a view or a table, that can be used inside the crystal reports form. This datasource has to be bounded to the Crystal report form. One suggestion I have is to create a stored procedure that updates or inserts into a datasource AND executes the stored procedure that you are sending parameters to. If you stored procedure is not working to return values into a table or view (datasource), you will get the error that you are seeing.

Dmooreora

 

[pengzhang]

Hi Dmooreora,

Do you mean that instead of invoking a stored procedure directlt from Crystal Report, A view(or table) has to be created for Crystal Report to invoke and the stored procedure is called in the view?

Can you elaborate it a little bit 'cause I have the same question.

By the way, in the return of a stored procedure, there are always "expression" as the column name for each retured value, which is inconvenient to use in Crystal Report? Can we have some kind of mechanism to name the returned value?

Thanks

 

[dmooreora]

Hi,

Actually, it works this way:

Making sure that you have stored procedure properties option is set on file/options -> database tab

Using report -> report expert, you would select the database though ODBC or another connector -> Choose stored procedures. The stored procedures must have fields that are included as a data source on the report.

Here is an example stored procedure:

create procedure <proc_name> (param1 <datatype>, param2 <datatype>)
SELECT field1, field2... ,
param1 AS <ALIAS FIELD NAME>,
param2 AS <ALIAS FIELD NAME>

FROM
chris_daterange
WHERE
field2 >= param1
AND
field2 <= param2
end procedure

The above example aliases each stored procedure parameter fields and includes them in the SELECT statement which then get returned to Seagate Crystal Reports. CR will be able to display the parameter values in the report by their alias name.

Hope this helps some.
Dmooreora

 

[vishnuchandar]

Hi dmooreora,

Have you tried the sample SP (given by you) on Informix? Using Oracle or MS-SQL Server SP works fine. But, in case of Informix, using SP as DataSource gives problem. I am facing the same requirement and problem as antzzz faces.

Antzzz, did you solve that problem? If so, please help me on the same

vishnuchandar@vsnl.net

 

[Guest_imported]

Since you can see the stored procedure in crystal designer, then you have the options set to see stored procedures.

I have not started using crystal on informix 7.31. I am currently trying to get a linked server set up correctly between informix 7.31 and mssql 2000 so I can write reports against the sql server instead of the unix system.

But in every other environment I have worked on with crystal, the parameter is placed in the stored procedure, not within the crystal tools. When crystal calls a stored procedure with parameters, the sql tools ask for the parameters input. In Crystal Designer, after the parameter has been set up in sql, you can format the parameter text, and add drop downs. I would guess it works similarly in informix. So, just try calling the procedure in crystal query, and see if it asks for a parameter.


Tom