Crystal Reports - Setting up a one-to-many relationship in a crystal report

[Stu]

Hi there,

I am trying to set up a Crystal Report with the following tables:

Customer Info:
-Name
-Address
-CustomerID

Orders:
-CustomerID
-Order value ($)

I have N customers and each customer has M Orders. I need to display
the Customer Info for each customer, followed by a graph for each
customer's order values. I have defined a schema for the tables and
connected them with Crystal Reports Database Expert.

In order to do this, I have set up a Group in Crystal Reports based on
the Customer Name. In the footer of each group, I add a table that
displays the order values of the customer's orders.

This works fine, except for the fact that in the details section of
each group, the Customer info is repeated once for each Customer
Order. Obviously, I just want the Customer Info displayed once.

Is there a way to define a relationship like this? When I use the
Database Expert, I don't see any way to indicate that this is a
one-to-many relationship.

I am using Crystal Reports 10.

Any suggestions?

Thanks,
Stu
(e-mail address removed)

 

[PJ]

Stu,

Move all of your customer info to the group header or format each field with
the suppress if duplicate option checked.

PJ


Hi there,

I am trying to set up a Crystal Report with the following tables:

Customer Info:
-Name
-Address
-CustomerID

Orders:
-CustomerID
-Order value ($)

I have N customers and each customer has M Orders. I need to display
the Customer Info for each customer, followed by a graph for each
customer's order values. I have defined a schema for the tables and
connected them with Crystal Reports Database Expert.

In order to do this, I have set up a Group in Crystal Reports based on
the Customer Name. In the footer of each group, I add a table that
displays the order values of the customer's orders.

This works fine, except for the fact that in the details section of
each group, the Customer info is repeated once for each Customer
Order. Obviously, I just want the Customer Info displayed once.

Is there a way to define a relationship like this? When I use the
Database Expert, I don't see any way to indicate that this is a
one-to-many relationship.

I am using Crystal Reports 10.

Any suggestions?

Thanks,
Stu
(e-mail address removed)

 

[Bob]

Hi Stu,
You may like to define a view in the database that gives you the detail data
that you want. This will give better performance than sucking in the raw
table data.
ie.
Create a view with:
Select customer.name,order.id,order.value from customer inner join order on
customer.id=order.customer.id
This will give a recordset which you would group on Customer.name
You would only place order.id and order.value in the detail section.
Then place your graph in either the group header or footer as required.
HTH
Bob