Using main report field as parameter for subreport with stored procedure

I'm starting to fear this can't be done, but I haven't lost hope yet...

I have a main report without any parameters, as designed. I need to link in a subreport whose data is derived from a stored procedure with 2 parameters. I would like these parameters to be linked to 2 fields in the main report. However, when I try to create the subreport off the stored procedure it prompts me for the two input parameters, for which I must enter values in order to add the stored procedure. Once that is done, I am unable to link the parameters to fields in the main report. In addition, the main report prompts me for the parametes for the subreport when refreshing.

Is there a way to make the subreport link to two fields in the main report and not prompt the user to enter a value?

Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

bbgbpAuthor Commented:
By the way, this is sort of on the urgent side, hence the 500 point value...

Thanks for any help offered!
I haven't tried this but can you link the main report to the subreport through the main report field to the subreport parameters?

I don't have any reports built from a stored procedur eotherwise I would try it myself.

bbgbpAuthor Commented:
That's exactly what I would like to do. Unfortuantely, the subreport parameters are not listed in the list of items to link. Only the output fields are available.
OWASP Proactive Controls

Learn the most important control and control categories that every architect and developer should include in their projects.

Are you running the reports from an application?

bbgbpAuthor Commented:
No, the report is a plain old 8.5 user execute report from Crystal. I think I've narrowed down the problem, though. I set up another report with a simple text variable linking to a subreport with a text parameter. That worked! So, I narrowed it down to a date/datetime issue. One of the parameters is being passed a date in Crystal format (yyyy,mm,dd). The problem seems to be that the date being passed to SQL is not being translated to proper T-SQL syntax for comparison against a date in a SQL server. When I try to link the date field from the main report, it does not find a suitable match for in the subreport. The datetime parameter in the subreport is not available for selection.

Any ideas? Do I have to convert the date to another format before I try to link it to the subreport?

> Unfortuantely, the subreport parameters are not listed in the list of items to link. Only the output fields are available.

You can manually set the record selection formula in the subreport to something like:
{mysp;1.myfield} = {?myparameterfrommainreport}

Good Luck.
bbgbpAuthor Commented:
PW -

That won't work, as the subreport is asking for parameters to be sent to the stored procedure.

What I ended up doing was creating a formula in the main report that converts the main report's date parameter to a datetime variable. I was then able to map the datetime variable to the sp parameter in the subreport. All seems to be working now.

So, I guess the short answer is, I wasn't able to see the all the parameters in the subreport because their datatypes did not match exactly to the fields I was trying to map.

Thanks for all your suggestions, mlmcc.


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
i have the same problem, did you get the proper solution for it ?
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Crystal Reports

From novice to tech pro — start learning today.