• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 721
  • Last Modified:

Crystal Reports 10, same table referenced multiple times in one report

I am new to DB reporting and am using Crystal 10.  My current report is two pages and reqiures the info of
the main person in the report and then the information of other related people, ie parents and siblings.  The problem is
that all this information is in the same table the "person" table.  I have linking gone from the persons table to another table which then links back to person table to find the related person via the person_id (key to person table and secondary key in many other tables).

Forexample.  I lookup john and drag his name from the field explorer to the report canvas.  But I also need to the name of his sibling which also resides in the person table.  I have all the tables linked but I need to know how to get all this information in one row.  I hope this makes sense any help would be appreciated.
  • 2
1 Solution
You can use a subreport to have your 2nd table as a datasource. Link the subreport on the main person. You can then have all the data on one line in the subreport.

hscdsbAuthor Commented:
I have been reading about subreports, I am not sure I understand what you mean at the momement.  Basically I need to get all my result information into one row instead of several rows that I have now.  

For example, I join in the telecom table because I need phone numbers, but there are multiple records in there for a single person so now my result set has multiple rows in it for each person, which is not what I want, I want a field for each resulting phone number in one row so I can each to the same page on the report.  Also I need to add the information about related people but that information is in the same table as the person who the report is focused on . How do I put the same fields from the same table, but on different rows in the same report.

If I understand you correctly you have one table that contains information similar to this:

Name          Id            relatedtoId
Joe Smith 123              
Jim Smith 124              123

and another table for the numbers, maybe like this

Id          Phone              Cell Phone
123       747-857-8965   444-555-4569
124       747-857-9654

Try this.

Go to Database, database expert, and add your 'person' table once
then add it again,you will be prompted asking if you want to create an alias, say yes.  You will now have two tables, one 'Person' and another one 'Person_1'.  Add your telecom table.

Now go to the links tab, click clear links on the right hand side.  Create a left outer join link from the <in this example> RelatedID field in the "Person" table to the Id field of the alias table 'Person_1'. Please note, from the limited information you have provided, I am assuming there is some field in your 'Person' table that references the relationship between parents and siblings.

Create a inner join from the 'Person' table on the ID field to your Telecom table Id field.

Now that you have your table links.  I am going to use the format of <table.field> to describe the rest of the report.

in your detail area drop the following <or similar> fields.
person_1.id, person_1.name, telecom.phone, telecom.cellphone

Goto Insert|Group and create a group on person.id
in the group header next to the group name drop the fields person.name, telecom.phone, telecom.cellphone.

what you should have now <again your field names will be different, but you didn't provide specifics to work with>
is a report that is grouped by a person listing their phone numbers with a detail of their family members and also listing the family member phone numbers.

This all hinges on whether or not their is a field in your Person table that defines that relationship.

hscdsbAuthor Commented:
I have been experimenting with adding tables with aliases and messing with linking.  My situation is a bit more complex that your example but you got me started in the right direction and I was able to solve the issue using your explanation and a combination of views which I created on the DB server.  Thanks so much for your answer you got me past a major block.

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now