Technical Questions
 Crystal Reports Forum : Crystal Reports 9 through 2020 : Technical Questions
Message Icon Topic: Trouble with a Subreport Post Reply Post New Topic
Author Message
Frank in VA
Groupie
Groupie
Avatar

Joined: 15 Nov 2007
Location: United States
Online Status: Offline
Posts: 46
Quote Frank in VA Replybullet Topic: Trouble with a Subreport
    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
IP IP Logged
wsc27
Newbie
Newbie


Joined: 25 Jun 2012
Online Status: Offline
Posts: 10
Quote wsc27 Replybullet 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.
IP IP Logged
Frank in VA
Groupie
Groupie
Avatar

Joined: 15 Nov 2007
Location: United States
Online Status: Offline
Posts: 46
Quote Frank in VA Replybullet 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
IP IP Logged
lockwelle
Moderator
Moderator


Joined: 21 Dec 2007
Online Status: Offline
Posts: 4372
Quote lockwelle Replybullet 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.
IP IP Logged
Frank in VA
Groupie
Groupie
Avatar

Joined: 15 Nov 2007
Location: United States
Online Status: Offline
Posts: 46
Quote Frank in VA Replybullet 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
IP IP Logged
hilfy
Admin Group
Admin Group
Avatar

Joined: 20 Nov 2006
Online Status: Offline
Posts: 3701
Quote hilfy Replybullet 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
IP IP Logged
Frank in VA
Groupie
Groupie
Avatar

Joined: 15 Nov 2007
Location: United States
Online Status: Offline
Posts: 46
Quote Frank in VA Replybullet 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
IP IP Logged
Frank in VA
Groupie
Groupie
Avatar

Joined: 15 Nov 2007
Location: United States
Online Status: Offline
Posts: 46
Quote Frank in VA Replybullet 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
IP IP Logged
Post Reply Post New Topic
Printable version Printable version

Forum Jump
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot delete your posts in this forum
You cannot edit your posts in this forum
You cannot create polls in this forum
You cannot vote in polls in this forum



This page was generated in 0.031 seconds.