Crystal Reports - Subreports multiplying

I am using Crystal Reports 2013

I have a main report with 5 groups.  The data source for the main report is a SQL stored procedure.  I have added 1 sub report that uses a different SQL stored procedure.  Both stored procedures pass the same parameters to the reports.  

I have linked the sub report on all the parameter in the stored procedures.  I have placed the sub report in a group footer.

When I run the report I get the first main report with it's matching sub report on a page, just like I am after.  Then I get the second page with the main report with the first record repeated with the second sub report results, 3rd page the same main report and the third sub report results.  This continues until I run out of sub report results and then I get the second main report with the sub reports starting again.

Can someone point out where I have gone wrong?   As you suspect I am a newbie and if this is answered somewhere and I just didn't find it, please just point me in that direction.  I really appreciate any help I can get.

Michele
mbooher2015
Michele BooherAsked:
Who is Participating?
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.

mlmccCommented:
Which group footer did you put it in?

Sounds like you put it in the innermost group footer rather than one of the outer footers.

mlmcc
0
Ido MilletProfessor of MIS at Penn State Erie and Owner, Millet SoftwareCommented:
Sounds like you need to move the subreport from the Group Footer to the Report Footer.
0
James0628Commented:
We may need to see the report, with some sample output, to really understand what's happening.  If you don't know, you can attach the report (the .rpt file) to a post.

When I run the report I get the first main report with it's matching sub report on a page, just like I am after.
Do you mean "first main report group", or "first main report page", or ...?

Then I get the second page with the main report with the first record repeated with the second sub report results, 3rd page the same main report and the third sub report results.
Is the first record actually repeated on the second page (and maybe the third)?  If you remove the subreport, does that first record appear more than once in the report?

This continues until I run out of sub report results and then I get the second main report with the sub reports starting again.
"run out of sub report results" makes it sound like your subreport is getting broken up into pieces, like you should have a single subreport that includes 5 lines, but, instead, you get 5 separate subreports with 1 line each.  If that's the case, it seems that your subreport parameters are limiting the data too much, and, as already suggested, the subreport may be in the wrong location (eg. the wrong group footer).

 James
0
Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Michele BooherAuthor Commented:
mlmcc and IdoMillet thanks for the help, I did try all the group footers, including the report footer and I still have the same problem.
0
Michele BooherAuthor Commented:
James0628  both the main report and the sub report should change pages on the memberid.  I want 1 page or pages with the detail of the main report and then the subreport that matches that member id.  when all records for that memberid (main and subreport) print , then start a new page with the second memberid.

I am attaching the report file as you suggested, hoping someone may have a little time to have a look.

Thanks again Michele
Con-pled.rpt
0
Ido MilletProfessor of MIS at Penn State Erie and Owner, Millet SoftwareCommented:
You need to link the subreport to the main report using Member ID. Make sure the automatically generated record selection formula in the subreport indeed restricts the subreport records.

Place the linked subreport in the Group Footer of the member.
0

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
James0628Commented:
OK, this is third edit of this post (I keep seeing something new :-).

 You don't use the subreport parameters in the subreport record selection formula, but that's presumably correct, since the subreport is using a stored procedure.  The subreport parameters are presumably (supposed to be) used in the stored procedure to limit the data to the desired records.

 You linked @MemberId in the main report to @MemberId in the subreport.  Is the main report run for one member at a time?  IOW, is @MemberId a single member #, or does it somehow include multiple values?

 If the parameter includes multiple values, then you probably want to link the MemberId field (the field used for your group), instead of the parameter, to the subreport parameter, so that the subreport will only include the results for the member in the current group.

 If the parameter only includes a single value, then that shouldn't be a problem, although, in that case, why have a group on the MemberId field?


 If that doesn't help, I'm still not clear on exactly what's wrong.

 Is the subreport showing the correct data and the subreport is just too long (running onto multiple pages)?

 Or are you getting more subreports than you want?  If so, is the problem that the data that should be on one subreport is split between multiple subreports, or you're getting multiple subreports showing the same data, or something else?

 James
0
Michele BooherAuthor Commented:
Thank you everyone.  The reminder to move the sub report back to the memberid group footer and linking the subreport to the memberid field of the main report did the trick.  I really appreciate you all taking the time to lend me a hand.

Michele
0
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.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.