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

Posted on 2006-06-01
Last Modified: 2008-03-10
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.
Question by:hscdsb
    LVL 10

    Expert Comment

    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.


    Author Comment

    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.


    Accepted Solution

    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.,,, telecom.cellphone

    Goto Insert|Group and create a group on
    in the group header next to the group name drop the fields,, 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.


    Author Comment

    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

    Why You Should Analyze Threat Actor TTPs

    After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

    Join & Write a Comment

    I recently went through setting up a JasperReports Server using the AWS EC2 instance, and this article will cover some basic administration tasks I had to perform.
    How to increase the row limit in Jasper Server.
    Need more eyes on your posted question? Go ahead and follow the quick steps in this video to learn how to Request Attention to your question. *Log into your Experts Exchange account *Find the question you want to Request Attention for *Go to the e…
    This video gives you a great overview about bandwidth monitoring with SNMP and WMI with our network monitoring solution PRTG Network Monitor ( If you're looking for how to monitor bandwidth using netflow or packet s…

    754 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

    Need Help in Real-Time?

    Connect with top rated Experts

    18 Experts available now in Live!

    Get 1:1 Help Now