Create new group based on sales level

[bv0719]

I have a report that includes a group by customer and their net sales. I need to group again on the Sum ({@Pos}, {INSALES.Customer}) field based on the amount of the @pos amount, if this makes sense. What I need is a group based on the sales level. If cusomter 123 has sales of 150.00 then that customer should be in group Bronze, if customer 456 has sales of 400.00, silver and so on. What is the best way to accomplish this. I thought that grouping on the formula field would accomplish this but no luch. I'm stumped.

thanks,
BV

 

[IdoMillet]

Two main options:

1. Get the sum from the Query (using SQL, Stored Procedure, or a View).

2. Insert a GH1b & GH1C and move them above GH1a (Group Header for Customer). Use GH1a for Gold and GH1b for Silver "Banners". Show (Suppress attribute) these additional group headers based on an expression that checks the value in the current group and the value in the previous group. NOTE: you would need a Variable to keep the sum of the previous group so it's available in the GH of the next group.

Cheers,
- Ido

CUT, Visual CUT, and DataLink Viewer:
view, e-mail, export, burst, distribute, and schedule Crystal Reports.

http://www.MilletSoftware.com

 

[bv0719]

I just used the highlight expert as a quick fix but if you have any further thoughts please let me know.

thanks,
BV

 

[lbass]

Did you try doing a TopN on the sum? Go to report->TopN. This will allow you to order the groups in descending order by the sum, and then you could create a formula that identifies the groups as gold, silver, or bronze--something like the following to be placed in the group header or footer for group (INSALES Customer):

if sum({@Pos}, {INSALES.customer}) > 600 then "Gold" else
if sum({@Pos}, {INSALES.customer}) in 400 to 599 then "Silver" else
if sum({@Pos}, {INSALES.customer}) in 150 to 399 then "Bronze" else ""

-LB