Sort limited records after data pull

[awfrance]

I am having a problem trying to sort records displayed on the report. I have a report for our credit department that calculates three columns, over 60, over 90 and over 120. It adds the columns, and limits the records shown to 100 to create a "Top 100" report. At the bottom of the report are sum totals for both the top 100 customers listed and sums for the total custmers in the table. The credit manager has requested that the results be sorted in alphabetical order rather than the total order. The problem I am having is that if I sort by customer, the totals are off. I need to be able to sort the 100 customers listed, not the entire customer table. Does anyone know of a way I can sort the shown records?

 

[awfrance]

Crystal XI R2 is my version.

 

[O2BNSV]

You might try putting this data into a cross tab. Try putting the alpha name in the row and your date formula in the column heading and putting the credit amount in the detail. Doing so would allow you to have your topN = 100 records show in alphabetical order, you would still have the same columns, which would be totalled at the end of the report.

 

[lbass]

I think you would have to do the topN in an unlinked subreport in the report header. In the subreport, after adding the group sort, create a formula:

whileprintingrecords;
shared stringvar array customer;
shared numbervar array rank;
numbervar i := i + 1;
numbervar j := 100;

if i < j + 1 then (
redim preserve customer;
redim preserve rank;
customer := {table.customer};
rank := groupnumber
);
rank

Then in the main report, group on the customer field and add the summaries. Then go to the section expert->group header->suppress->x+2 and enter:

whileprintingrecords;
shared stringvar array customer;
shared numbervar array rank;
numbervar i;
numbervar j := ubound(customer);
numbervar k := 0;

for i := 1 to j do(
if {table.customer} = customer then
k := rank
);
k = 0

For the summaries of the topN values, you would have to use two formulas in the main report:

//{@accum} to be placed in the group footer:
whileprintingrecords;
numbervar sumamt;
numbervar k;
if k > 0 then
sumamt := sumamt + sum({@calculation},{table.customer});

//{@display} for the report footer:
whileprintingrecords;
numbervar sumamt;

-LB

 

[awfrance]

Its not working for me. Created a subreport and created the formulas you suggested, but on the subreport I cannot group sort using the calculation formula and the group sort will not let me add multiple based on columns. Any ideas? Also, when I get to a page that has a row larger than 100 i get an array error.

 

[lbass]

How were you able to implement the top100 the first time around? I assumed you used a group sort. Please explain.

Also please show the content of the calculations you used to determine the top100, including the content of any nested formulas.

-LB

 

[awfrance]

I wrote formulas based on the three column calculations using arrays to gather totals for the 100 shown.

ArrayCounter formula:

// set to evaluate while printing records
WhilePrintingRecords;

// declare the arrays as global to be used in other formula's

// global array's used for running totals
Global NumberVar Array calArray;
Global NumberVar Array curArray;
Global NumberVar Array thirtyArray;
Global NumberVar Array sixtyArray;
Global NumberVar Array ninetyArray;
Global NumberVar Array twentyArray;
Global NumberVar Array dollarArray;
Global NumberVar Array percentArray;

// global array used for alpha sort
Global StringVar Array alphaSort;

// declare the counter variable
NumberVar Counter;

If Not({combinedaging.BALANCE} In calArray) Then
(

// increment the counter variable
Counter := Counter + 1;

// make sure Counter does not go over 100 elements
If Counter <= 100 Then
(

// Preserve elements in arrays
Redim Preserve calArray[Counter];
Redim Preserve curArray[Counter];
Redim Preserve thirtyArray[Counter];
Redim Preserve sixtyArray[Counter];
Redim Preserve ninetyArray[Counter];
Redim Preserve twentyArray[Counter];
Redim Preserve dollarArray[Counter];
Redim Preserve percentArray[Counter];
Redim Preserve alphaSort[Counter];

// assign values to arrays from dataset
calArray[Counter] := {combinedaging.BALANCE};
curArray[Counter] := {combinedaging.CURRENT};
thirtyArray[Counter] := {combinedaging.OVER30};
sixtyArray[Counter] := {combinedaging.OVER60};
ninetyArray[Counter] := {combinedaging.OVER90};
twentyArray[Counter] := {combinedaging.OVER120};
dollarArray[Counter] := {@$ OVER 60};
percentArray[Counter] := {@% OVER 60};
alphaSort[Counter] := {combinedaging.ALPHA_SORT};

)

)

Counter formula:

// set the counter to 100 to pull only the Top 100 accounts
{#Counter} < 100

-----------------------------------------------

Then I have formulas calculating the sums of the arrays like this one:

Array60 formula:

// evaluate sum calculation after the arraycounter function
EvaluateAfter ({@ArrayCounter});

// declare the array variable from the ArrayCounter function
Global NumberVar Array sixtyArray;

// calculate the sum of the sixtyArray
Sum (sixtyArray);

------------------------------------------

Then forumlas to calculate the sums minus the 100 accounts shown like this:

Other60 formula:

// declare the global variable for the difference calculation
Global NumberVar glo60;

// calculate the difference of the total and the array value
{#TotalOver60} - {@Array60};

// assign difference to the variable
glo60 := {#TotalOver60}-{@Array60};

-------------------------------------------

I have a formula for the calculation of the Over60 + Over90 + Over120 columns:

$OVER60 formula:

// calculate the combined value of the 60, 90 and 120 day buckets.
{combinedaging.OVER60} + {combinedaging.OVER90} + {combinedaging.OVER120}

------------------------------------

And a formula to calculate the percentage of the accounts total balance that is over 60 days:

%OVER60 formula:

// calculate the percentage of the combined value of the 60,90 and 120 day buckets.
Round((({combinedaging.OVER60} + {combinedaging.OVER90} + {combinedaging.OVER120}) / {combinedaging.BALANCE})* 100)

 

[awfrance]

I also put the counter variable into the details section of the section expert like so:

{#Counter} > 100

 

[lbass]

I honestly can't follow what you did to limit the set to 100 records, as I'm not that expert at the use of arrays, but since you seem to be, you should be able to adapt my solution. What you want to do in the subreport, is set the rank array to include those records that fall into the top100 and same for the customer array.

-LB