Crystal report create groups based on a formula

Yes it is possible.

Say you want Under $50, $50-100, and Over $100

Basic formula

If {YourDollarAMount} <= 50 then
    "Ünder $50"
Else if {YourDollarAMount} <= 100 then
    "$50-100"
Else
    "Over 100"

Select allOpen in new window

Is you amount field from the database or do you use a formula for it?

mlmcc

The formula I want to use is a formula which sums another formula.
Formula 1 calculates the book value for a customer. The 2nd formula  (the one I want to use to create the groups) , sums the value of the first formula so I get a total amount for the customer.

You can't use a summary as the group formula

WHy do you want to use the total as the group formula?

Are you really wanting to sort by the total?
If so, you can use the GROUP SORT under the REPORT menu to sort the groups by a summary formula

What are the 2 formulas that get the sum?

mlmcc

Yes, it is possible to do both (calculate sum per customer and allocate into ranges by summed value) in SQL.

In SQL you could use case expressions to help you achieve the report, notice the case expression in the order by clause. It may be better to just allocate a value like 1,2,3 in SQL then allow Crystal to do its magic based on that.

-- Based on Customer dollar result place into dollar ranges.
SELECT
      CASE
            WHEN (customer_dollars) > 1000000 THEN '$1m plus'
            WHEN (customer_dollars) > 100000 THEN '$100k plus'
            ELSE '<100k' END
    , customer
    , customer_dollars
FROM (
            -- For each customer the dollar amount needs to be calculated 
            SELECT
                  customer
                , SUM(dollars) AS customer_dollars
            FROM some_tables
            GROUP BY
                  customer
      ) AS derived
ORDER BY
      CASE
            WHEN (customer_dollars) > 1000000 THEN 1
            WHEN (customer_dollars) > 100000 THEN 2
            ELSE 3 END
      , customer
;

Select allOpen in new window

Formula 1 uses fields from the view to get the book value
Named @BookValue

IF {LPlusLeaseVW.lease_earn_code} = 20
    then (((if isnull({LPlusLeaseVW.contract_ltd_funded_princ_amt}) then 0 else {LPlusLeaseVW.contract_ltd_funded_princ_amt})
- (if isnull({LPlusLeaseVW.contract_ltd_paid_princ_amt}) then 0 else {LPlusLeaseVW.contract_ltd_paid_princ_amt})
 - (if isnull({LPlusLeaseVW.contract_ltd_write_off_princ_amt}) then 0 else {LPlusLeaseVW.contract_ltd_write_off_princ_amt})))
else {LPlusLeaseVW.lease_book_value}

The 2nd formula sums the result of the 1st formula to get a total for the customer.

Named @BV By Customer
SUM({@BookValue},{LPlusLeaseVW.lease_cust_id_num})

There is a 3rd formula which sets up the groups based on the the result in formulat 2.  but this
is used in a cross-tab table. For the report I am writing a cross tab is not useful.
Here is the 3rd formula which does the grouping on the cross-tab. I though I could use it to create groups but crystal does not allow it.

@Bal Groups

select case {@BVbyCustomer}
case 0 to 100000    
    formula = "0-100,000"
case 100001 to 200000
    formula = "100,001-200,000"
case 200001 to 300000
    formula = "200,001-300,000"
case 300001 to 400000
    formula = "300,001-400,000"
case 400001 to 500000
    formula = "400,001-500,000"
case 500001 to 750000
    formula = "500,001-750,000"
case 750001 to 1000000
    formula = "750,001-1,000,000"
case is >= 1000001
    formula = "Over 1,000,000"
End Select

Thanks! I tried what you suggested. It got me on the right track.