Skip to Content
author's profile photo Former Member
Former Member

Calculate sum of top N records

Hi guys,

how can I calculate the sum of my top n records?

If I add a group results, it shows only the sum of all records.

Add a comment
10|10000 characters needed characters exceeded

Assigned Tags

Related questions

2 Answers

  • Best Answer
    Posted on Nov 21, 2011 at 10:08 AM

    Hi Robert,

    First off all, sort the records in descending order based on the database field you would like to summarize. For eg: if the report shows top 5 customers in US based on sales figures, then sort the report on sales figures in Descending order.

    Next, create a formula and use this code:

    WhilePrintingRecords;
    numbervar inc;
    numbervar top_n;
    inc := inc + 1;
    if inc <= 5 then //where 5 means top 5
    top_n := top_n + {field_to_summarize}

    Place the above formula on the Details section.

    Create another formula to display the top n sums:

    whileprintingrecords
    numbervar top_n;

    Place this formula on the Report/Page Footer.

    Now, if the report is already grouped on a database field, then create a formula to reset the variables as follows:

    whileprintingrecords
    numbervar top_n := 0;
    numbervar inc := 0;

    Place the above formula in the Group Header.

    You may then place the formula that displays the sum in the Group Footer.

    Hope this helps!

    -Abhilash

    Add a comment
    10|10000 characters needed characters exceeded

  • author's profile photo Former Member
    Former Member
    Posted on Nov 22, 2011 at 03:03 PM

    You can use complex formulas with variables if you want to. But, try using a Running Total instead! They calculate after Top N has processed, so you will get only the values included in the Top N in the running total, and no complex formulas are requred!

    - George -

    Add a comment
    10|10000 characters needed characters exceeded

Before answering

You should only submit an answer when you are proposing a solution to the poster's problem. If you want the poster to clarify the question or provide more information, please leave a comment instead, requesting additional details. When answering, please include specifics, such as step-by-step instructions, context for the solution, and links to useful resources. Also, please make sure that you answer complies with our Rules of Engagement.
You must be Logged in to submit an answer.

Up to 10 attachments (including images) can be used with a maximum of 1.0 MB each and 10.5 MB total.