johnf814
asked on
Subreport only pulling in 1 record
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.
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.
DustinKikuchi
Have you ensured that your sub-report links are correct?
ASKER
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.
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
Are you expecting more than 1 record to correspond to the record in the main report?
Where do you have the subreport?
mlmcc
ASKER
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.
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.
Check the subreport selection formula. Make sure it is correct.
mlmcc
mlmcc
ASKER
{BAQReportResult.Part.Part
This is the selection formula.
This is the selection formula.
That looks correct.
You should be getting information on the part number from the main report {?Pm-BAQReportResult.Part.
mlmcc
You should be getting information on the part number from the main report {?Pm-BAQReportResult.Part.
mlmcc
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
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
ASKER
Here is a screenshot of my report options for the subreport.
I put in the RecordNumber field, but it only shows one record anyways so it just shows record #1 for everything.
I put in the RecordNumber field, but it only shows one record anyways so it just shows record #1 for everything.
Run the subreport separately with the selection formula and enter 1 part number
I would turn off the VERIFY ON FIRST REFRESH
mlmcc
I would turn off the VERIFY ON FIRST REFRESH
mlmcc
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
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
ASKER
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.
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.
Can you upload the report?
Can you upload the subreport showing all the data?
mlmcc
Can you upload the subreport showing all the data?
mlmcc
> 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
> 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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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
Not much help, I know. :-)
James