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?
Upgrade to CR XI R2 for free, use your XI keycode to install:
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.
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.
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.
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)
open PARAM_PAYDETAIL for
select * from CNVHANSEN_I.CNV_HANSEN_PAYROLL where APPROVALNUM = PARAM_APPROVAL;
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"
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.
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.