[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now


Crystal report create groups based on a formula

Posted on 2014-08-11
Medium Priority
Last Modified: 2014-08-12
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.
Question by:ryann
  • 3
  • 3
LVL 101

Expert Comment

ID: 40254684
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
    "Over 100"

Open in new window

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


Author Comment

ID: 40254708
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.
LVL 101

Expert Comment

ID: 40254723
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?

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

LVL 49

Expert Comment

ID: 40254735
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.
            WHEN (customer_dollars) > 1000000 THEN '$1m plus'
            WHEN (customer_dollars) > 100000 THEN '$100k plus'
            ELSE '<100k' END
    , customer
    , customer_dollars
            -- For each customer the dollar amount needs to be calculated 
                , SUM(dollars) AS customer_dollars
            FROM some_tables
            GROUP BY
      ) AS derived
            WHEN (customer_dollars) > 1000000 THEN 1
            WHEN (customer_dollars) > 100000 THEN 2
            ELSE 3 END
      , customer

Open in new window


Author Comment

ID: 40254736
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

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
LVL 101

Accepted Solution

mlmcc earned 2000 total points
ID: 40254829
If the order is not a concern, You could simulate the grouping

Group by customer ID
Insert your first formula
Right click it
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.


Author Closing Comment

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

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Whether you've completed a degree in computer sciences or you're a self-taught programmer, writing your first lines of code in the real world is always a challenge. Here are some of the most common pitfalls for new programmers.
In this post we will learn different types of Android Layout and some basics of an Android App.
In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…

872 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question