Is it posible to orders groups based on results of a manual RT 1

[johnwolf]

Hi

Using CR XI

I am counting new customers per sales rep during current month.

I have a report based on orders table with

Group 1 : sales rep
Group 2 : customer ID


Details:
order ID, date,

Manual RT
Formula init recuento in G1H

whileprintingrecords;
numbervar recuento:=0;

Formula recuento in detail

whileprintingrecords;
numbervar recuento;
If Minimum ({D01ORDERS_TBL.DATE}, {D01ORDERS_TBL.CUSTOMER_ID})={D01ORDERS_TBL.DATE} and {@MONTH}={@CURRENTMONTH} then recuento:=recuento+1 else recuento

Formula result recuento in G1F

whileprintingrecords;
numbervar recuento;

Now I hide everything but G1F end everything fine like

sales rep 1: 15
sales rep 3: 10
sales rep 7: 4
sales rep 8: 21

I would like to have them sorted on descending order of recuento.

Any idea welcome, even though it is using other kind of formulas.

 

[Madawc]

The TopN function is useful for ordering groups using summary totals. You can even make them conditional, something like a formula field that says

If {value} = "A" then 1 else 0

Summing such a field would give a result that could be used with TopN.

In your case, I don't think it will work, because your test uses a Minimum rather than some property of a single record.

But do you have to use the minimum?

Madawc Williams (East Anglia, UK). Using Crystal 11.5 with SQL and Windows XP

 

[johnwolf]

Thanks for your answer

The minimum function is used to determine if the order of the customer is the first one for this particular customer, i.e. if it is a new customer.

I do not no if it is the most effective way.

Any idea welcome.

 

[lbass]

You could create a SQL expression {%mindt} (field explorer->SQL expression) like this:

(
select min(`DATE`)
from D01ORDERS_TBL A
where A.`CUSTOMER_ID` = D01ORDERS_TBL.`CUSTOMER_ID`
)

The punctuation might need to be changed depending upon your datasource.

Then change your formula to:

If {%mindt} = {D01ORDERS_TBL.DATE} and
{@MONTH}={@CURRENTMONTH} then
1

Insert a sum on this at the customer ID group level. Then go to report->group sort and select this summary as your sort field.

-LB

 

[johnwolf]

Thank you very much lbass.

Works perfectly.