Skip to Content

Crystal Report formula for Rank based on number of records

Hi,

I am creating a crystal report in which I have location and number of insurance claims.

Number of insurance claim is a summary field.

I want to add formula for Claim Rank as follow.

Thanks.

Location    No Of Insurance Claims    Claim Rank
---------------------------------------------------
A           8                         1
B           7                         2
C           7                         2
D           6                         3
E           5                         4
F           5                         4
G           5                         4
--------------------------------------------------


Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

1 Answer

  • Dec 05, 2017 at 10:56 AM
    Hi Saurabh,

    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(x IN arr) Then 
    ( 
    x := x + 1; 
    redim preserve arr[x]; 
    arr[x] := <Summary field>; //Replace this with the Insurance Claims Summary
    ); 
    '';

    4. Drag and drop this formula field beside the Insurance Claims Summary.

    5. Suppress ALL sections of this report.

    5. Save and close this report

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

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

    8. Place the Subreport on the Report Header.

    9. Create a formula with this code:

    shared numbervar array arr; 
    local numbervar i; 
    local numbervar rank; 
    for i := 1 to ubound(arr) do 
    ( 
      if <Summary Field> = arr[i] then //Replace Summary field with the Insurance Claims Summary
      ( 
        rank := i; 
        exit for; 
      ); 
    ); 
    rank;

    10. Place this formula field beside the Insurance Claims field

    11. Right-click the Subreport > Format Subreport > Subreport tab > Check 'Suppress Blank Subreport'.

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

    -Abhilash

    Add comment
    10|10000 characters needed characters exceeded