467,923 Members | 1,244 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 467,923 developers. It's quick & easy.

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
st**********@hotmail.com
Jul 21 '05 #1
  • viewed: 6003
Share:
2 Replies
PJ
Stu,

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

PJ
"Stu" <st**********@hotmail.com> wrote in message
news:23**************************@posting.google.c om...
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
st**********@hotmail.com
Jul 21 '05 #2
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
"Stu" <st**********@hotmail.com> wrote in message
news:23**************************@posting.google.c om...
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
st**********@hotmail.com

Jul 21 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

4 posts views Thread by scott cooper | last post: by
2 posts views Thread by B. L via AccessMonster.com | last post: by
5 posts views Thread by Axel Gallus | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.