Skip to Content
0

Crystal Report formula for Rank based on number of records

Nov 30, 2017 at 07:53 AM

18

avatar image
Former Member

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


10 |10000 characters needed characters left characters exceeded
* Please Login or Register to Answer, Follow or Comment.

1 Answer

Abhilash Kumar
Dec 05, 2017 at 10:56 AM
0
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

Share
10 |10000 characters needed characters left characters exceeded