Stored procedure in Crystal report

[former_member458725]

Hi,

     I have a stored procedure with three parameters[ two string and one date parameter] which is connected to the crystal report. When I create the Crystal report I have selected this stored procedure as Data Source. When I finished the Report creation Wizard, The parameters are already set as of the Stored Procedure Parameter.

What I need is :

          The parameter prompted as a simple text field,  I need date time picker for date parameter, and other two parameter with CFL of Customer and List of Currency respectively. Is there any option to do so ?

Please Help, Thanks in advance !!!!

[abhilash_kumar]

Hi Vibin,

When you create a report against a stored procedure, the parameters are always prompted as simple text fields; meaning date/datetime parameters will not show the calendar picker.

To show the calendar picker, follow these steps:

1) Create a blank report (you may point its datasource to an excel file too)

2) Go to the Field Explorer > Right-click Parameter Fields > Create New parameter. Choose the datatype as "DateTime" and type as "Static"

3) Go to Insert > Subreport. Choose the stored procedure as the Datasource of this Subreport. Follow the wizard and you might need to enter the values for the first time. Once you're back to the report canvas go to the Main Report's design Page.

4) Right-click the Subreport > Select Change Subreport Links > Choose the DateTime parameter from the Pane on the left and move to the Fields to link to Pane on the right.

5) From the drop-down on the bottom left-side where it says "Subreport Parameter field to use", choose the DateTime prompt from the Subreport's source.

6) Next for the other two parameters, you can simply create them manually in the Subreport just like you did in the Main Report. Only this time the datatype will be different and you might have to manually type in the List of Values in the Parameter creation screen.

Let me know how this goes!

-Abhilash

[former_member458725]

I did what you said, But when I link the main report parameter to sub report i got an error

"Cannot find the parameter field in the subreport rpt", But there is @region Parameter Filed in sub report which was set while linked the Stored Procedure.

I have checked the parameter type also.

In main report i have created - >  " Region @ select * from [@REGION] "

[Former Member]

Hi Vibin,

I remember having worked with older versions i.e CR XI and CR XI R2 where a DateTime picker was shown for a stored procedure parameter.

What is the version/patch of Crystal Reports and database that you are using?

Thanks,

Prathamesh

[former_member458725]

I am using SAP 8.81(8.81.320) PL : 10 HOTFIX 1

[abhilash_kumar]

Hi Vibin,

You do not need to link the String type parameters. You said you wanted a calendar picker, so you just need to link the Date parameters.

From the drop-down I see that there are two Date/DateTime parameters. So you need to create 2 similar parameters in the Main Report. Call the 1st parameter "From_date" and the 2nd one "To_Date".

Then link the From_date parameter to ?@dateFrom and the To_Date to ?@dateTo.

-Abhilash

[former_member458725]

Date is working properly. I need a list too Customer list or Region[Region is a User defined table]. When i try to link that region to subreport region I got that error. I have no issue with date, its working perfectly.

[Former Member]

What is the error?

If you are using the main report-sub report approach then check the following:

1.The parameters in main report need to be of same datatype that is expected by the stored procedure parameters in the sub report.

2. If you need a drop-down list for the parameters, then create a dynamic parameter in the main report. a. In the 'Create New parameter' window, choose 'Dynamic' and key-in required details in the "Value" and "Parameters".

b. Now use this parameter to link to the respective sub report parameter which gets created by-default due to stored procedure datasource.

Thanks,

Prathamesh

[former_member600936]

Hello Sir

I tried the above method and it works perfectly fine in design mode.But has i import report in SAP ,

SAP Business one preview, I am not seeing the drop down boxes for my parameters. What is the reason