SAP Knowledge Base Article - Public

1328853 - Ranking Set of Records In Crystal Reports.

Symptom

How to set Ranking of records in Crystal Reports.

Reproducing the Issue

1.Create a report in designer with Customer Table and place the field Last Year Sales in Details section.

2.Sort the  records based on customer id's.

Resolution

1.Create a subreport in the main report.Create a formula in Main Report and 3 formulas in subreport as given below and place them in the sections suggested.

The 3 Formulas in Subreport are:

2.Create a formula[Initialize] to place the values in a array:

WhilePrintingRecords;
Shared datatype array #varname;
numberVar i;
i:=i+1;
redim preserve #varname[i];
#varname[i]:={#Table.FieldValue};

3.Place this formula,[Initialize] in the section  where you want to display the values  ie. in Details Section or Group Header.

4.Create another formula[Bubble Sort] to sort the values which we have declared in the array(Bubble Sort):

Whileprintingrecords;
Shared currencyVar array #varname;

local currencyVar temp;
local numbervar i;
local numbervar j;
numbervar counter;
counter:=Count({#Table.FieldValue})-1;

for i := 1 to counter - 1
do
(
   for j := 1 to counter do
   (
   if #varname[j + 1] > #varname[j] then//if the next element is bigger then switch them
      (
      temp := #varname[j + 1];  //set a temp variable to the larger number
      #varname[j + 1] := #varname[j]; //set the (j + 1)th element to the smaller number
      #varname[j] := temp;//set the jth element to the temp value(the larger number)
     
      );
   );
)

5.Place this formula,[Bubble Sort] accordingly in Group footer1, if you placed the Initialize formula in Group Header1 and Place it in Report Footer Section, if you placed the Initialize formula in Details Section.

6.Create another formula,[Sorted Array] where, we are displaying the values which are sorted in the Array:

WhilePrintingRecords;
Shared datatype array #varname;
numberVar x;
stringVar str;
for x:=1 to Count({#Table.FieldValue}) step 1 do
str:=str+chr(10)&#varname[x];
str

7.Place this formula, [Sorted Array] in Report Footer.

8.Create a formula, [Ranking] in Main Report :

WhilePrintingRecords;
Shared datatype array #varname;
local numberVar i;
local numberVar j;
for i:=1 to UBound(#varname) step 1 do
(
if {#Table.FieldValue}<>#varname[i] then
(j:=j+1;
true)
else
exit for
);
j+1

9.Place the formula,[Bubble Sort] in the Details Section or the Group Header Section accodingly where you want to display the data.

Keywords

KBA , BI-RA-CR , Crystal Reports designer or BusinessViews Manager , How To

Product

Crystal Reports 2008 V0 ; SAP Crystal Reports XI ; SAP Crystal Reports XI R2