Count the occurence and summarize

[batkosta]

Dear Experts,

I've only been using crystal for few days and am already facing the creation of a complex dashboard for our top management. The situation is as follows:

CR XI

I'm using a command to pull the required data from a postgres database. The resultset looks like that:

SaleRef, CustomerID, Field1, Field2, etc

133421 1234
133422 1235
133423 1236
133424 1235
133425 1236
133427 1235
133426 1234
133427 1233
133428 1233
133429 1200

What I need to do is count the number of sales by each customer and then group the results based on the number of sales (Dummy Group which cannot be linked to a field).

So the final result shoould be a table similar to this:

#ofSales Customers
1 1
2 3
3 1

The summary states that there is one customer with one purchase (1200), three customers with two purchases each (1233, 1234, 1236) and one customer with three purchases (1235).

Do you think this is acheivable in crystal at all?


Many Thanks,
Mr.K

 

[Madawc]

Groups are based on some property of an individual record. Not the number of members of a group. That's a limit of Crystal, which is a reporting tool and not a full language.

You can control the order in which groups are shown, check the 'Top N' function. Does this help?

Depending on your set-up, you may also be able to include a stored procedure in SQL.

Madawc Williams (East Anglia, UK). Using Windows XP & Crystal 10

 

[batkosta]

Thanks for your quick reply Madawc, stored procs are not an option in this case but i'll keep trying with a combination of group selection formulas and running total formulas.

As usual any help appreciated.

Mr.K

 

[lbass]

You could use a command like this:

Select count(table.`SalesRef`) as salescnt,table.`customer`
From `table` table
Group by table.`customer`

Link this command to your customer field in the other command and choose 'enforce both' in the link options for the equal join. Then use {command.salescnt} as your group field.

Or you could build this command into your main command. I think you would then have to use syntax like:

Select table.`field1`, (select count(A.`SalesRef`) from table A where A.`customer` = table.`customer`) as salesref,
table.`field2`
From `table` table [etc.]

-LB

 

[batkosta]

Thanks for the suggestion, I managed to get the desired effect by creating a group based on the CustomerID and using Running Totals with evaluation formulas. Then placed the totals in the report footer and drew some borders to create a feel for a crosstab. I think crystal is a powerful reporting platform which needs some understanding and imagination in order to get the most of it.


Thanks for you help,

Mr.K