Cannot Rank or Group By Second-Pass Formula Results in Crystal Reports

Posted on 2008-11-14
Last Modified: 2013-12-19
I am attempting to build a report that pulls the 10 highest valued results from a second-pass formula.  The formula I want to pull the top 10 from is the result of a subtraction of 2 second pass formulas:
These are basically SUMs using the 3 formula methodology in the second pass.

Can I do this, and if so how?
Question by:Idratherbegolfing
    LVL 100

    Expert Comment

    You can but it requires using a subreport.

    Does the report have a subreport?
    If so you can't do it.


    Author Comment

    Thanks!  I don't currently have a sub-report, but I could certainly build one and insert it into this report.  I did some research on this topic online before I wrote this post and found in several places that people suggested using a subreport.  Unfortunately none of them really talked in detail about how to do this.  I've tried several ways I thought it could be done with a subreport, but haven't had any success.  Could you point me in the right direction?  Thanks again!
    LVL 100

    Expert Comment

    Check this aritcle.  It shows a method of passing arrays into a subreport to do what you want.  You would have to modify it to fit your needs.


    Author Comment

    Thanks.  I'll give this a try and let you know how it works!

    Author Comment

    I still can't seem to get this to work as the field I need to sum "Cannot Be Summarized" in the first summary formula that you insert into the report footer (its a second pass formula result):

    //This formula uses Crystal syntax.
    Shared CurrencyVar curCounter;
    Shared CurrencyVar curTotal;
    curCounter := curCounter + {Customer.Last Year's Sales};
    curTotal := (curCounter/Sum ({Customer.Last Year's Sales}))*100;

    While playing around with this, I think I got everything else to work using a formula that can be summarized (but won't give me the data I need) except for the fact that my subreport only pulls the first "ShowValue" number wherever I insert it into the report, which seems odd.  

    Basically I have a table that has 3 fields I use for this report.  Product, Price, and Price Type.  I have 2 Price Types that I want to analyze the differences on so I've built formulas to calculate the difference between PriceType1 and PriceType2 for each Product.  Then I want to show only the top 10 results (in descending order) from this difference calculation.

    So maybe I'm taking the wrong approach by using the 3 formula method to calculate this difference?

    Anyone have any ideas?

    LVL 100

    Accepted Solution

    So you have a table like

    Product       Price       PriceType
    book            1.00         wholesale
    book            2.00         retail
    pot               2.00        wholesale
    pot               3.00        retail

    One thing you could try is
    Add a second copy of the table
    Link on the product field
    In the select expert
    Table1.Pricetype = 'Retail'
    Table2.PriceTyoe = 'Wholesale'

    You then should have a single record for each product and should be able to do the comparison without printtime formulas

    If that doesn't work and the table structure is as I assumed, I'll try to come up with an idea.


    Author Comment

    That's a great idea.  I'll give that a shot and see how that works.  Thanks again!

    Author Closing Comment

    This worked.  I added a second instance of the table and was able to get all my data into one record so as not to have to use second pass formulas.  Without using the second pass formulas I was easily able to do some topN groups to pull my top 10 price differences.  Thanks!

    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

    Truncate is a DDL Command where as Delete is a DML Command. Both will delete data from table, but what is the difference between these below statements truncate table <table_name> ?? delete from <table_name> ?? The first command cannot be …
    Cursors in Oracle: A cursor is used to process individual rows returned by database system for a query. In oracle every SQL statement executed by the oracle server has a private area. This area contains information about the SQL statement and the…
    This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.
    This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.

    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