Stored proc in a subreport in CR8.5 with parameter issue

[flowersr]

Hi,

I have a report that has a patient number as the main report's only parameter. Two 2 of the 5 subreports within it each have a stored procedure with the patient number as the stored procedure's only parameter. When I call the main report it wants me to type the patient number into 3 parameters. I followed knowledge base article c2002084 from

http://www.businessobjects.com

support section but no luck it getting it to work. Help please!

TIA,
Rich

 

[MisterMo]

If you have the parameter on the main report then you need to link it to subreport via the subreport link

you can see the parameter from the subreport as soon as you choose from the main list

actually you can see it twice, one as {pm_param} and the other as {param}. One on the left side and one on the right list. depending on the one you choose you will/will not be asked to enter the same parameter twice

I usually choose the one on the left.

Mo

 

[flowersr]

Mo,

Already did that but to no avail. I hve created many sub-reports using tables. This is 1st with a stored procedure. I noticed That 2 parameters were created in subreport when I linked them.

Thanks,
Rich

 

[MisterMo]

That would be correct I use SP's on my reports and subreports and have had similar issues,

you might have some residual code hiding somewhere or on the selection formula that has been generated by CR, one thing you could try is to save the subreport then remove it from the main and re-insert it again

Mo

 

[MisterMo]

I have just checked my report

it uses SP on both Main and Sub Report

Subreport links are
Main {@MyParam1} = Sub {?@MyParam1}
and not
Main {@MyParam1} = Sub {?pm-?@MyParam1}

Nothing on the Selection formula

Mo

 

[flowersr]

Mo,

Just created a new report to test this problem. Same thing occurs.

I am using 8.5 with no updates installed. Could I have some option not set correctly in Crystal.

Could it be something concerning the stored procedure or how it is defined? The stored procedure is defined with one parameter (integer). I run it in MS SQL query analyzer by typing 'exec usp_upmc_test 400' and I get the correct result in the results window.

The parameter and field I am referencing in the container report are both defined as number.

I am assuming I don't have to call any of the fields or parameters anything special to trigger this to work.

Thanks,
Rich

 

[flowersr]

Mo,

Should also mention I am using ODBC as my connection type to database.

Rich

 

[flowersr]

Mo,

Also, have data from main report on 1st detail line. Subreport on 2nd detail line.

Rich

 

[flowersr]

Mo,

I tried same thing in Crystal 7.0 with no updates on another work PC and get the same result.

This is why I think it is Crystal option related or database related.

TIA,
Rich

 

[MisterMo]

Rich,

Have you checked on the selection formula section if there is something there? if so could you post it plz.

The rest should not make much difference.

Unless it has something to do with versions, I am using 10.



Mo

 

[flowersr]

Mo,

Here you go:

Main report's Record Selection Formula is:

{?Admission}={Enc.AdmSys}

Sub-report's Record Selction Formula is:

{usp_UPMC_TEST;1.AdmSys} = {?Pm-Enc.AdmSys}

TIA,
Rich

 

[flowersr]

Mo,

I am using Windows 2000 Pro. what OS are you using?

Rich

 

[flowersr]

Mo,

I scavenged up a Win2K machine and will install Crystal 10 Enterprise to see if I can get it working in it. Perhaps it did not work in earlier versions as I have not heard from anyone else but you.

TIA,
Rich

 

[MisterMo]

Rich

Crystal Enterprise is not Crystal Reports is a program to manage existing reports.

OS has no bearing on that

As I said before

on the left combo of your subreport editing panel you should have two options

one should be {?Pm-Enc.AdmSys}
and the other {?Enc.AdmSys} in my case I use the latter
and the selection formula in my subreport is clear


change this {usp_UPMC_TEST;1.AdmSys} = {?Pm-Enc.AdmSys}
with {?Admission}= {?Enc.AdmSys}
where {?Admission} comes from the main
and {?Enc.AdmSys} comes from the subreport

you should use the parameter field from both the report and the subreport









Mo

 

[flowersr]

Mo,

Found that out about Enterprise. Installed Crystal 10 developer instead and it works. So it must be broken in 8.5.

Thanks,
Rich

 

[flowersr]

Mo,

By editing panel, do you mean Link panel or are you talking about Edit Selection Formula in subreport?

Rich

 

[MisterMo]

Link panel

Mo

 

[flowersr]

Mo,

I have tried changing the link connections every which way possible. I even just installed service pack 3 for 8.5 but still no luck.

Must be an undocumented feature that did not work until versions 9 or 10.

The trouble is that the database's supporting application has a 8.5 engine embedded in it so I am stuck on 8.5 for now as they want the end users to run the reports within the application.

Rich