cancel
Showing results for 
Search instead for 
Did you mean: 

how do you pass a parameter to a stored procedure in Crystal 11

Former Member
0 Kudos

I have a Crystal 11 report with a parameter.I've added a stored procedure to that report. When I added the stored procedure Crystal added another parameter to the report. I would like to pass the parameter I already have in the report to the stored procedure. This is very simple to do in SSRS. How do I do this in Crystal?

Accepted Solutions (0)

Answers (2)

Answers (2)

former_member260594
Active Contributor
0 Kudos

Hello James,

When you added the stored proc to the report and noticed that it added another parameter to the report that was in fact the stored proc parameter coming into the report. It sounds like you had already created a "Crystal Report" parameter but you should use the stored proc parameter as the values will be passed to the stored proc that way.

Regards,

Graham

Former Member
0 Kudos

Graham,

I have a Crystal Report that uses a view as a data source. The report has all kinds of formating, formulas, etc. that I want to keep, but I want to change the report's data source from a view to a stored procedure. Please tell me how to go about doing that.

Regards,

James

abhilash_kumar
Active Contributor
0 Kudos

Hi James,

You can change the report's datasource using the 'Set dDatasource location' option (Database > Set Datasource Location).

I'm assuming you've already done this. So, when you add a stored procedure to the report and if the stored proc has an 'In' parameter, it automatically creates a parameter in the report.

Since you've created a separate parameter and wish to link this one with the stored proc's parameter, here's what you need to do:

1) Insert the original report as s Subreport.

2) The Main report can be a blank report with just the parameter you created

3) Right-click the Subreport and select Change Subreport Links > Move the parameter to the 'Fields to link to' area.

4) Then click on the drop-down for 'Subreport parameter field to use' and select the stored procedure parameter.

This will link the Main report parameter to the stored procedure's. Make sure the parameter you create in the Main report is of the same data type as the stored procedure's parameter.

-Abhilash

Former Member
0 Kudos

Abhilash,

I tried your suggestion of  creating a subreport but when I tried to preview the main report it ran for a long time then this error message came up:

"The page size was not large enough to format the contents of an object in the report."

-James

abhilash_kumar
Active Contributor
0 Kudos

Hi James,

Have you place the subreport on the Page Header section?

Move it to the Report Header please.

-Abhilash

Former Member
0 Kudos

Abhilash,

That seems to have done it. Thanks. One question. I'm replacing existing fields with fields from my stored procedure. What's the fastest way of doing that? I'd like to avoid dragging fields from my new datasource onto the report.

-James

abhilash_kumar
Active Contributor
0 Kudos

Well, wasn't that supposed to be taken care of by the 'Set Datasource Location'?

-Abhilash

Former Member
0 Kudos

I see. That's not a problem. I'm having a problem with Crystal recognizing all the rows returned by the stored procedure. Crystak seems to be seing only one row though I know more than one row is returned.

Here's the Oracle sp code:

create or replace
PROCEDURE CNV_HANSEN_PAYROLL_SP (PARAM_APPROVAL IN INTEGER, PARAM_PAYDETAIL OUT SYS_REFCURSOR)
AS
BEGIN

open PARAM_PAYDETAIL for
select * from CNVHANSEN_I.CNV_HANSEN_PAYROLL where APPROVALNUM = PARAM_APPROVAL;

END CNV_HANSEN_PAYROLL_SP;

-James

Former Member
0 Kudos

I got the "Failed to Save document message" and lost all my work!!!!!!!

Give me SSRS, PLEASE.

Former Member
0 Kudos

I installed the Crystal R2 upgrade and I'm noticing a couple of issues:

1. When I start Crystal I get the "Server is busy. Switch to..." message.

2. The other issue (and this is more vexing) is that I cannot run reports with Business Elements built with Crystal's Business View Manager. When I try to run these reports I get: "Unknown Database Connector Error" then "Prompting failed with the following error message: ''. Error Source:  Error code: 0x80004005"

0 Kudos

Ah, that makes a difference.

The first error could be due to CR trying to contact our WEB Server to show updates etc. Likely your Firewall is blocking it.

You never said you were using BOE, the version of CR must be the same as the BOE Server so you have to go back to XI R1.

Don

Former Member
0 Kudos

Hi James,

What is the result of executing the stored procedure from SQL plus / TOAD?

It seems that you want to get all columns from CNVHANSEN_I.CNV_HANSEN_PAYROLL table where APPROVALNUM = PARAM_APPROVAL.

Do you really need a stored procedure with a cursor for that?

As per my understanding, a cursor is supposed to return one row at a time. Also, the cursor does not seem to be advanced or looped or incremented to fetch all rows.

Secondly, if you want to change a report's datasource from a 'View' to a 'stored procedure' then both should return similar number of columns. Although, I still doubt whether its a good practice.

Thanks,

Prathamesh

0 Kudos

Create a Record Selection Formula

NewParam = OrgParam

Don

Former Member
0 Kudos

I added to the existing record select formula but I'm still being prompted for the stored procedure's parameter:

{CNV_HANSEN_PAYROLL.APPROVALNUM} = {?Approval Number} and {CNV_HANSEN_PAYROLL_SP.APPROVALNUM} = {?Approval Number}