Solved

Using main report field as parameter for subreport with stored procedure

Posted on 2003-11-07
10
1,372 Views
Last Modified: 2008-01-09
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?

Thanks!
0
Comment
Question by:bbgbp
10 Comments
 
LVL 1

Author Comment

by:bbgbp
ID: 9703586
By the way, this is sort of on the urgent side, hence the 500 point value...

Thanks for any help offered!
0
 
LVL 100

Expert Comment

by:mlmcc
ID: 9704282
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.

mlmcc
0
 
LVL 1

Author Comment

by:bbgbp
ID: 9704313
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.
0
Active Directory Webinar

We all know we need to protect and secure our privileges, but where to start? Join Experts Exchange and ManageEngine on Tuesday, April 11, 2017 10:00 AM PDT to learn how to track and secure privileged users in Active Directory.

 
LVL 100

Expert Comment

by:mlmcc
ID: 9705826
Are you running the reports from an application?

mlmcc
0
 
LVL 1

Author Comment

by:bbgbp
ID: 9705928
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?
0
 
LVL 6

Expert Comment

by:PWinter
ID: 9706344
Hi.

> 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.
PW
0
 
LVL 1

Accepted Solution

by:
bbgbp earned 0 total points
ID: 9707420
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.

0
 

Expert Comment

by:mahabat
ID: 11655399
hi
i have the same problem, did you get the proper solution for it ?
0

Featured Post

Networking for the Cloud Era

Join Microsoft and Riverbed for a discussion and demonstration of enhancements to SteelConnect:
-One-click orchestration and cloud connectivity in Azure environments
-Tight integration of SD-WAN and WAN optimization capabilities
-Scalability and resiliency equal to a data center

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Crystal Reports: 5 Tests for Top Performance It is complete, your masterpiece report.  Not only does it meet your customer’s expectations, it blows them out the water, all they want is beautifully summarised and displayed in a myriad of ways. …
I hate sub reports and always consider them the last resort in any reporting solution.  The negative effect on performance and maintainability is just not worth the easy ride they give the report writer.  Nine times out of ten reporting requirements…
This video shows how to use Hyena, from SystemTools Software, to bulk import 100 user accounts from an external text file. View in 1080p for best video quality.
Nobody understands Phishing better than an anti-spam company. That’s why we are providing Phishing Awareness Training to our customers. According to a report by Verizon, only 3% of targeted users report malicious emails to management. With compan…

830 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question