Skip to Content
avatar image
Former Member

Calculating League Table Position

Hi,

I am trying to calculate the value position in a table of values for instance -

Garden Arches –

Name Sales Value Position

Micheal £10,500 4

David £20,700 2

Richard £20,250 3

Peter £50,740 1

How do I calculate the position number?

Many thanks,

Chris.

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

7 Answers

  • Best Answer
    Dec 06, 2017 at 05:33 PM

    Hi Chris,

    Here's what you need to do:

    1. Save another copy of this same report. Let's call it 'Main Report' and the other copy 'Subreport'.

    2. Open 'Subreport'.

    3. Create a formula with this code:

    shared numbervar array arr;
    shared numbervar x;
    If NOT(xIN arr) Then
    (
      x:=x+1; 
      redim preserve arr[x]; 
      arr[x]:= {Value field};
    );
    '';

    4. Drag and drop this formula field beside the Value Field.

    5. Suppress ALL sections of this report.

    6. Go to the Record Sort Expert and add the 'Value' field as the first sort field. Sort in Descending Order.

    7. Save and close this report

    8. Open the other report called 'Main Report' that we saved in Step 1.

    9. Go to 'Insert Subreport' > Choose an existing report > Browse to the location where you saved 'Subreport' and click OK.

    10. Place the Subreport on the Report Header.

    11. Create a formula with this code:

    shared numbervar array arr;
    local numbervar i;
    local numbervar rank;
    for i :=1 to ubound(arr) do
    (
      if {Value field} = arr[i] then
      ( 
        rank := i;
        exit for;
      );
    ); 
    rank;

    12. Place this formula field beside the Value Field

    13. Right-click the Subreport on the Report Header > Format Subreport > Subreport tab > Check 'Suppress Blank Subreport'.

    14. Go to the Section Expert > Highlight the Report Header > Check 'Suppress Blank Section'.

    -Abhilash

    Add comment
    10|10000 characters needed characters exceeded

  • Dec 09, 2017 at 09:20 AM

    Hi Chris,

    1. Insert another Group Header 1b section and move the Subreport from the Report Header to this new section.

    2. Right-click the Subreport > Select Change Subreport Links > Move the Category field to the pane on the right > From the drop-down at the bottom right that says 'Select data in subreport based on field', choose the Category field from the drop-down.

    3. Back in the Main Report, create a formula with this code and place this on the Group footer:

    shared numbervar array arr := 0;
    shared numbervar x := 0;

    -Abhilash

    Add comment
    10|10000 characters needed characters exceeded

  • avatar image
    Former Member
    Dec 11, 2017 at 09:55 AM

    Hi Abhilash,


    Followed your steps exactly and here is the result.

    Any ideas what I am doing wrong?

    Many thanks,

    Chris.

    Add comment
    10|10000 characters needed characters exceeded

  • avatar image
    Former Member
    Dec 11, 2017 at 12:36 PM

    Hi Abhilash,

    Here is a screenshot of the design view of the report so you can see the groups etc.

    Add comment
    10|10000 characters needed characters exceeded

  • avatar image
    Former Member
    Dec 07, 2017 at 09:13 AM

    Many thanks for your help I will give this a try today.

    Regards,

    Chris.

    Add comment
    10|10000 characters needed characters exceeded

  • avatar image
    Former Member
    Dec 07, 2017 at 10:43 AM

    Hi Abilash,

    I have done exactly as you instructed but the rank number is not resetting when the category changes

    Hope the screenshot helps.

    Add comment
    10|10000 characters needed characters exceeded

  • avatar image
    Former Member
    Dec 07, 2017 at 10:51 AM

    Hi,

    I have gone throught he pages of the report and the rank number resets to 0 after it reaches 80.

    This would not be a problem if I can get the rank number to reset after each category.

    Regards,

    Chris.

    Add comment
    10|10000 characters needed characters exceeded