Print Page | Close Window

Trouble with a Subreport

Printed From: Crystal Reports Book
Category: Crystal Reports 9 through 2020
Forum Name: Technical Questions
Forum Discription: Formulas, charting data, Crystal syntax, etc.
URL: http://www.crystalreportsbook.com/forum/forum_posts.asp?TID=22931
Printed Date: 28 Apr 2024 at 3:25pm


Topic: Trouble with a Subreport
Posted By: Frank in VA
Subject: Trouble with a Subreport
Date Posted: 24 Mar 2021 at 8:34am
Hi All,

I'm trying to create a new subreport for an existing report. The main report is a customer report. The subreport will be a commodity report which will be linked by the customer number.

I tried linking the customer number from within the customer table in the main report to the customer number within the commodity table in the subreport. I went through the subreport Links window, selecting the customer number to link to, and the customer number in the commodity subreport table.

But when I run the main report, I only get the headings in the subreport, no other data. Not every customer number being displayed in the main report will have a matching record in the commodity table, which is probably part of the problem. How can I display the commodity information for customer numbers from the main report if it exists in the commodity table?

Thanks in advance for any advice!
Frank

-------------
Frank


Replies:
Posted By: wsc27
Date Posted: 25 Mar 2021 at 2:02am
Hi Frank,
Are you using the subreport wizard when you created the subreport? Maybe double check the fields to link to & make sure that the correct customer number field is selected as Select Data based on that field is checked. Also, depending on what other fields you are trying to populate in the subreport plays a factor if results get returned or not for the particular customer number in the subreport. Make sure that the fields are in the detail section of the subreport. Hope this helps.

Posted By: Frank in VA
Date Posted: 25 Mar 2021 at 8:30am
Hi WSC27,

Thanks for your reply. Yes, I used the wizard to set up the subreport. I have the customer number as the Field to link to, and the checkbox for Select data in subreport is checked. Currently, I am only displaying two fields on the subreport, the customer number and the commodity code, which are both in the commodity table. I know that many of the customer numbers in the main report are not going to be found in the commodity table. I was hoping that the subreport would show the data if a match was found and just skip the ones that aren't in the table.

Thanks.
Frank

-------------
Frank

Posted By: lockwelle
Date Posted: 26 Mar 2021 at 12:04pm
The subreport should take a parameter of some kind...probably the Customer Number. Link the Customer Number in the main report to the parameter value in the subreport.

Hoping that is clearer than mud.

Posted By: Frank in VA
Date Posted: 29 Apr 2021 at 4:42am
Thanks Lockwelle. This brought me closer to resolving my issues. I have one left ... the subreport is showing records for all the customers in the Customer table, not just the ones listed in the main report.

In the Database Expert of the subreport, I have the commodities table first, linked to both the customer and commodity description tables together at the same level.

How can I get the subreport to match the data (customers) from the main report, and not include all of the customers from the customer table? Am I linking them incorrectly?

Thanks!
Frank

-------------
Frank

Posted By: hilfy
Date Posted: 29 Apr 2021 at 9:50am
What type of joins do you have between the Commodity table and the Customer table in the subreport?

I might have the Customer table first and join from that to the Commodity table. Then I would set the subreport link to be from the Customer ID in the main report to the Customer ID in the Customer table of the subreport.

-Dell

-------------
Proviti, Data & Analytics Practice
http://www.protiviti.com/US-en/data-management-advanced-analytics - www.protiviti.com/US-en/data-management-advanced-analytics

Posted By: Frank in VA
Date Posted: 29 Apr 2021 at 10:20am
Thanks for your reply!

I placed the customer table first linking to the commodities table. The join type is INNER JOIN, NOT ENFORCED and the link type is Equal.

In the Subreports Links window, Field(s) to link to: Customer ID, Subreport parameter field to use: Customer ID, checkbox (checked) for Select data in subreport based on field: customer ID.

Now the subreport is prompting for a customer ID as a parameter but not returning any data. I need the subreport to not ask for a parameter, just return all the commodities for the customers that reside in the main report.

Thanks!

-------------
Frank

Posted By: Frank in VA
Date Posted: 01 Jun 2021 at 5:14am
Hi All,

After rereading all of your suggestions, I was able to get the subreport to work correctly. Thanks for your help!

Frank

-------------
Frank


Print Page | Close Window