Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Subreport only pulling in 1 record

Posted on 2012-08-15
18
Medium Priority
?
1,341 Views
Last Modified: 2012-11-09
I've done some pretty extensive searching and still have yet to find a solution to my problem.

I have a custom report that is generated by Epicor and I have a subreport in it. I have linked the subreport to the main report and made sure that "can grow" is checked. When I run the report, the subreport shows up, but it only shows one sub-record for each main record. If I run the subreport on its own, all records show up. It is not a case of "can grow" because I can check the XML that the report is generating and even the XML only has one record from the subreport. I've tried a few different things, like grouping on the subreport, creating the subreport using the wizard, linking to an existing report. No matter what, it acts the same.

Please help!
Thanks.
0
Comment
Question by:johnf814
  • 6
  • 5
  • 4
  • +1
16 Comments
 
LVL 4

Expert Comment

by:DustinKikuchi
ID: 38296511
Have you ensured that your sub-report links are correct?
0
 

Author Comment

by:johnf814
ID: 38296539
Yes. The data that does show up in the subreport corresponds correctly to the field in the main report that is being used as the main link.
0
 
LVL 101

Expert Comment

by:mlmcc
ID: 38296614
When you link a subreport to the main report, it creates a filter so that only the related records show up in the subreport.  It sounds like it is working as expected.

Are you expecting more than 1 record to correspond to the record in the main report?

Where do you have the subreport?

mlmcc
0
How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

 

Author Comment

by:johnf814
ID: 38296779
Let me explain the scenario:

I am running a report with a group based on my internal part number. In the group are some quantities and some calculations along with a list of related jobs where the part is used. The subreport is used to list any component manufacturers and their part numbers, and this is linked by my internal part number. The internal part number in the subreport is linked to the internal part number in the main report, which is used for grouping. An internal part number can have multiple manufacturers and manufacturer part numbers. When I run the report, I only get one manufacturer and part number.

The subreport is literally just a list of internal part numbers, manufacturers, and manufacturer part numbers. If I run just that report, I can see all manufacturers and their part numbers for each specific internal part number.

The subreport is in the group footer. If I put it in the details section, it lists the subreport for each job. I only need it to show up once.
0
 
LVL 101

Expert Comment

by:mlmcc
ID: 38296945
Check the subreport selection formula.  Make sure it is correct.

mlmcc
0
 

Author Comment

by:johnf814
ID: 38297023
{BAQReportResult.Part.PartNum} = {?Pm-BAQReportResult.Part.PartNum}

This is the selection formula.
0
 
LVL 101

Expert Comment

by:mlmcc
ID: 38297883
That looks correct.

You should be getting information on the part number from the main report {?Pm-BAQReportResult.Part.PartNum}

mlmcc
0
 
LVL 35

Expert Comment

by:James0628
ID: 38299446
Could the subreport be suppressing some records?

 Check the Report Options for the subreport and see if "Select Distinct Records" is checked.  It doesn't sound like that's your problem, but there's no harm in making sure.

 Try putting the special RecordNumber field on the subreport, to confirm that it's only reading 1 record.

 James
0
 

Author Comment

by:johnf814
ID: 38302119
Here is a screenshot of my report options for the subreport.Report Options
I put in the RecordNumber field, but it only shows one record anyways so it just shows record #1 for everything.
0
 
LVL 101

Expert Comment

by:mlmcc
ID: 38303358
Run the subreport separately with the selection formula and enter 1 part number

I would turn off the VERIFY ON FIRST REFRESH

mlmcc
0
 
LVL 35

Expert Comment

by:James0628
ID: 38303669
Where did you put RecordNumber?  If you put it in the report footer in the subreport and it's showing 1, that confirms that the subreport is only reading 1 record (as opposed to reading multiple records, but only showing one of them for some reason).  If you put RecordNumber in some other subreport section, it's still possible that the subreport is reading more records, but you're only seeing the section with RecordNumber once, after the first record has been read.


 You said "If I run the subreport on its own, all records show up".  How did you run it?  If you haven't already, you can right-click on the subreport and select "Save Subreport As" and save the subreport as a completely separate report.  Then you can open it in CR and run it and it should ask for whatever fields you had linked from the main report.  Enter the same values that the subreport was getting from the main report and see what you get.

 James
0
 

Author Comment

by:johnf814
ID: 38306438
I put the RecordNumber in the details section of the subreport. However, like I said earlier, only one record shows up so it doesn't really show me anything by putting the record number in there.

I started with the subreport as its own entity and was able to run it and see all records for each part number. As soon as I add it to the main report, it only shows one record per part number. I even tried turning off the linking; it shows all manufacturers and manufacturer part numbers for every main part number, but it is still only showing one of each manufacturer and manufacturer part number. It's like something is filtering out the remaining records before displaying the report.

I also tried creating the subreport using the wizard and I get the same results. If I do a "save subreprt as" and run it on its own, it instantly gives me an error about not finding the parameter field from the main report and won't even let me enter in anything. It just fails. If I change the selection formula to select a specific part number, it opens the report, but again only shows one record. Everything is set to "can grow" and I've made sure to try everything that has been suggested so far.

I'm almost at the point of just scrapping the project and going a different route. I've never been in a situation like this before where something seems like it should work fine, but just refuses.
0
 
LVL 101

Expert Comment

by:mlmcc
ID: 38306446
Can you upload the report?

Can you upload the subreport showing all the data?

mlmcc
0
 
LVL 35

Expert Comment

by:James0628
ID: 38307295
> I put the RecordNumber in the details section of the subreport. However,
 > like I said earlier, only one record shows up so it doesn't really show me
 > anything by putting the record number in there.

 Which is why I said to put it in the subreport's report footer.  If the subreport is reading more records, but only showing you one of them, then all you're going to get in the detail section is the record number for that one record that's visible.  If you put RecordNumber in the subreport's report footer, you'll get the number of records that it actually read.

 It's just an easy way to make sure that the subreport is not actually reading more records than it's showing you.


 > If I do a "save subreprt as" and run it on its own, it instantly gives
 > me an error about not finding the parameter field from the main
 > report and won't even let me enter in anything.

 That's strange.  I did that here before my last post and when I ran the new report, CR just asked me for the parameter that it used to get from the main report.  I don't know why (or how) you would get an error.  In my case I had created the subreport by inserting an existing report, and then did a "save as" on that, so it started as a stand-alone report, instead of being created as a subreport using the wizard.  I wouldn't expect that to be a problem, but it's the only obvious difference (and I've never used the CR "wizards", so I don't really know anything about how they work).


 You said that the original subreport started as a separate report that showed multiple records, and when you insert it as a subreport, it just shows one record.  Did you make any changes to the subreport, like remove fields?  If the subreport uses more than one table, removing fields could change the number of records that it reads.


 FWIW, posting the report with the subreport, preferably with saved data, as mlmcc suggested, could be a big help.

 James
0
 

Accepted Solution

by:
johnf814 earned 0 total points
ID: 38313821
As it seemed like nothing was working the way it should, I rebuilt everything from scratch and have it working just fine. Not sure what happened during my first attempt, but it may possibly have something to do with the fact that I was adding the subreport to a report that I have been using for some time. Not sure why that would make any bit of difference, but it's just a wild guess. Anyways, thanks guys for your suggestions and time spent trying to help me out.
0
 
LVL 35

Expert Comment

by:James0628
ID: 38315191
I hate to say this, because it's so vague, but there's the possibility that the report or subreport was "corrupt".  I've seen one or two situations myself, and a few others reported here, where something in a report just did not seem to be working properly.  When the report was recreated, or possibly that "something" was deleted and recreated, then everything seemed to be back to normal.  My only explanation for those kinds of things is that something was wrong with the report file.

 Not much help, I know.  :-)

 James
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

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…
In a question here at Experts Exchange (https://www.experts-exchange.com/questions/29062564/Adobe-acrobat-reader-DC.html), a member asked how to create a signature in Adobe Acrobat Reader DC (the free Reader product, not the paid, full Acrobat produ…
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…
Suggested Courses

810 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