Crystal report create groups based on a formula

I am using a sql view for the data in the report. I need to group the report by range of dollars. For each customer the dollar amount needs to be calculated and based on the result it will fall into one of the dollar ranges. Is this possible?
I am using crystal xi.
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Commented:
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"
``````
Is you amount field from the database or do you use a formula for it?

mlmcc
0
Author Commented:
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.
0
Commented:
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
0
freelancerCommented:
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
;
``````
0
Author Commented:
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
0
Commented:
If the order is not a concern, You could simulate the grouping

Group by customer ID
Right click it
Click INSERT -->  SUMMARY
Set the summary as a sum
Put it in the group footer

You can then sort the group by the summary value.  That will put them in ascending or descending order
PUt your third formula into the group header and it will be similar to the grouping you desire.

mlmcc
0

Experts Exchange Solution brought to you by

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Author Commented:
Thanks! I tried what you suggested. It got me on the right track.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Programming

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.