Grouping on Summary Data

[LizRuss]

Hi,

I am using CR9 and need to display data grouped by a count.

i.e. customers 1,3,5,7,9 have made 1 order
customers 2,4,6,8 have made 2 orders.

My report needs to be grouped on the number of orders that have been made.

So it would look like

1 Order:

Customer 1,
3,
5,
7,
9

2 Orders:

Customer 2,
4,
6,
8

Please advise

For a better way of life check out

http://www.marillion.com

 

[Madawc]

Crystal only lets you group on values based on individual records, not summaries.

You can use 'Top n' to put groups in a desired sequence. Does that help?

Madawc Williams (East Anglia, UK)

 

[MisterMo]

Is the Order actually a field

eg.

Cust Order

1 2
3 1
4 2
6 1

if so simply group by the Order fields first and then by Customer.

If not could you tell me how do you get the order generated.

Mo

 

[LizRuss]

No sorry i have inserted a count of order_id as a summary to get the figures

For a better way of life check out

http://www.marillion.com

 

[MisterMo]

You could do it in SQL

select CustomerId, Count(order_id) as Orders
from dbo.yourtable
group by CustomerId

this way you can group by it from crystal.

Mo

 

[lbass]

You could try creating a SQL expression {%ordercnt}:

(Select count(A.`OrderID`) from Table A where
A.`CustomerID` = Table.`CustomerID`)

The punctuation differs based on your datasource, so check your "Show SQL Query" to get an idea of what yours should be like. If your CR 9.0 accepts this, you can then insert a group on {%ordercnt}. Otherwise you can build this expression into a query by using "Add Command" as your datasource. Your query would look something like:

Select
Table.`CustomerID`, ((Select count(A.`OrderID`) from Table A where A.`CustomerID` = Table.`CustomerID`)
From
`Table` Table

Again, you would be able to use the resulting expression to group on.

-LB