Crystal report create groups based on a formula

Posted on 2014-08-11
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
    LVL 100

    Expert Comment

    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

    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 100

    Expert Comment

    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?

    LVL 47

    Expert Comment

    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
    FROM (
                -- 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

    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 100

    Accepted Solution

    If the order is not a concern, You could simulate the grouping

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


    Author Closing Comment

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

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    How your wiki can always stay up-to-date

    Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
    - Increase transparency
    - Onboard new hires faster
    - Access from mobile/offline

    Suggested Solutions

    Title # Comments Views Activity
    static class 3 44
    dividesSelf challange 15 57
    userCompare  challenge 3 51
    mergeTwo  challenge 13 53
    I know it’s not a new topic to discuss and it has lots of online contents already available over the net. But Then I thought it would be useful to this site’s visitors and can have online repository on vim most commonly used commands. This post h…
    Since upgrading to Office 2013 or higher installing the Smart Indenter addin will fail. This article will explain how to install it so it will work regardless of the Office version installed.
    An introduction to basic programming syntax in Java by creating a simple program. Viewers can follow the tutorial as they create their first class in Java. Definitions and explanations about each element are given to help prepare viewers for future …
    In this fifth video of the Xpdf series, we discuss and demonstrate the PDFdetach utility, which is able to list and, more importantly, extract attachments that are embedded in PDF files. It does this via a command line interface, making it suitable …

    759 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

    Need Help in Real-Time?

    Connect with top rated Experts

    13 Experts available now in Live!

    Get 1:1 Help Now