on 05-30-2012 6:22 PM
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?
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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
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
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"
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
Create a Record Selection Formula
NewParam = OrgParam
Don
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Upgrade to CR XI R2 for free, use your XI keycode to install:
http://downloads.businessobjects.com/akdlm/crystalreports/crxir2_sp4_full_bld_0-20008684.exe
http://downloads.businessobjects.com/akdlm/crystalreports/CRYSTALREPORTS06_0-20008684.EXE
Don
User | Count |
---|---|
84 | |
25 | |
12 | |
9 | |
7 | |
6 | |
5 | |
5 | |
4 | |
4 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.