Ranking problem, topN with multiple ranks. 3

[smcnulty2000]

Crystal 10. MS-SQL Server.

My problem:

This is a topN problem. But it is a little more weird than most.

I have a set of receipts in a table, and another with the entity related to them.

I have a report built that gets the top 200 payers out of a list of about 1650 customers, Based on the full month. I'm using group sort for this. I'm also able to get the rank on these numerically using this:

EvaluateAfter (Sum ({@04AmntPdThisMonth}, {@Groups01License}));
global numbervar numGreybar;
numGreybar:=numGreybar+1.0;

Problem: I have two more columns to complete. One shows the same customer's numerical rank at YTD. The last shows the customer's numerical rank at YTD Last year at this date.

So essentially I have three different rank columns, each of which has to rank the data set according to their own totals.

So the output might look like this:

xxxxxxxxxx  R1  R2  R3
customer 1   1  2  1
customer 2   2  1  2
customer 3   3  3  4
customer 4   4  4  3
customer 5   5  5  5

I'm fairly certain that if I had to do only 100 of these, I'd be done by now because I'd use TopN. But TopN only works for 1-100 inclusive.

If anyone has a clever way to get TopN for 200 (for multiple columns), or another approach to this I'd be happy to hear it.

One thing I did try was dropping the data into an array and then using a for loop to count. However this didn't quite work because of an evaluation time issue.

As usual, if I've given insufficient information let me know and I'll update.

Thanks,

Scott.

 

[lbass]

One way to do this is to add each additional rank column using a subreport. In the subreport, you would use the topN sort and use groupnumber to represent the ranks (if there is only one group). Then you would insert the subreport in a group header_b section of the main report, but without linking it to the group. Instead, create a shared variable in the main report, as in:

whileprintingrecords;
shared numbervar cust := {table.customer};

Place this in the GH#_a section, and then in the subreport, go to the section expert->group header/footer (wherever your rank is)->suppress->x+2 and enter:

whileprintingrecords;
shared numbervar cust;
{table.customer} <> cust

This will suppress all but the appropriate rank. Then in the main report, format the GH_a section to "Underlay following section", assuming the rank is in the GH_a section. Repeat the process for the third set of ranks.

-LB

 

[smcnulty2000]

Okay, this wasn't the solution I used but it got me to the one I did end up using.

What I ended up using was a main report that was entirely suppressed, with the totals being added to an array.

Then I built a subreport that is the only part that displays, it took the items from the array built by the main report and ran through a for loop that checked for any given element how many were greater than that element in the array. Every time it found one that was greater than or equal to the current total, it would increment a counter.

Repeated for the third column.

So I ended up getting the ranks I needed and it takes about two minutes to run through all the iterations. I'll post the formulas if anyone wants them.

Thanks lbass, that was what I needed.

Scott.

 

[fernandoyanes]

Thanks, SmCnulty2000 for you info, I would like to see the formulas since I have a similar problems, I'll apreciate it,

Thanks, Fernando

 

[smcnulty2000]

This is right out of my tech diary for that day:

The trick is to use a counter that ascends in some fashion. Then you make ten arrays (or however many you need).

The counter is checked by an if-then statement or case statement and the correct array is selected. If the counter is
between 5001 and 6000, then subtract 5000 from the counter to get the element. Likewise the do loop will be used to compare elements to a current value.

All of this is used for 'ranking'. If you don't make the array big enough (7000 elements) then the numbers are off enough to maybe matter.

FYI, this is not quick to run. Running through an extra 10000 loops times 200 entries makes for an extra minute or two.
Double that if you are doing an additional rank. If there is a limit on how many 1000 point arrays you can have, I don't know it.

First formula, main report, header section:

shared numbervar array arrLastYrYTD01;
shared numbervar array arrLastYrYTD02;
shared numbervar array arrLastYrYTD03;
shared numbervar array arrLastYrYTD04;
shared numbervar array arrLastYrYTD05;
shared numbervar array arrLastYrYTD06;
shared numbervar array arrLastYrYTD07;
shared numbervar array arrLastYrYTD08;
shared numbervar array arrLastYrYTD09;
shared numbervar array arrLastYrYTD10;

shared numbervar array arrCurrYrYTD;
numbervar counter:=1;
numbervar countercurr:=1;
shared numbervar maxarraysize:=1000;
redim arrLastYrYTD01[maxarraysize];
redim arrLastYrYTD02[maxarraysize];
redim arrLastYrYTD03[maxarraysize];
redim arrLastYrYTD04[maxarraysize];
redim arrLastYrYTD05[maxarraysize];
redim arrLastYrYTD06[maxarraysize];
redim arrLastYrYTD07[maxarraysize];
redim arrLastYrYTD08[maxarraysize];
redim arrLastYrYTD09[maxarraysize];
redim arrLastYrYTD10[maxarraysize];


redim arrCurrYrYTD[maxarraysize];

//Last YTD Rank
//Current Month Rank


local numbervar x;
local numbervar y;
local numbervar numcount:=0;

for x:=1 to maxarraysize
Do
(
arrLastYrYTD01[x]:=1;
arrLastYrYTD02[x]:=1;
arrLastYrYTD03[x]:=1;
arrLastYrYTD04[x]:=1;
arrLastYrYTD05[x]:=1;
arrLastYrYTD06[x]:=1;
arrLastYrYTD07[x]:=1;
arrLastYrYTD08[x]:=1;
arrLastYrYTD09[x]:=1;
arrLastYrYTD10[x]:=1;
arrCurrYrYTD[x]:=1;

);

numcount;




0; //This is here to make the formula not break.
//Formulas may do operations involving manipulating arrays but can't end with an array.
// the zero gives the formula the ability to end with an output that is not an array.
//End of first formula

Second formula to load the array, main report, detail or group section:

evaluateafter(Sum ({@numTaxPdLastYearYTD}, {@Groups01License}));
shared numbervar array arrLastYrYTD01;
shared numbervar array arrLastYrYTD02;
shared numbervar array arrLastYrYTD03;
shared numbervar array arrLastYrYTD04;
shared numbervar array arrLastYrYTD05;
shared numbervar array arrLastYrYTD06;
shared numbervar array arrLastYrYTD07;
shared numbervar array arrLastYrYTD08;
shared numbervar array arrLastYrYTD09;
shared numbervar array arrLastYrYTD10;

shared numbervar maxarraysize;
numbervar counter;
local numbervar numValue:=(Sum ({@numTaxPdLastYearYTD}, {@Groups01License}));

local numbervar x;

if numValue=0 then
0
else (
select counter
Case 1 to 1000:
arrLastYrYTD01[counter]:=numValue
Case 1001 to 2000:
arrLastYrYTD02[counter-1000]:=numValue
Case 2001 to 3000:
arrLastYrYTD03[counter-2000]:=numValue
Case 3001 to 4000:
arrLastYrYTD04[counter-3000]:=numValue
Case 4001 to 5000 :
arrLastYrYTD05[counter-4000]:=numValue
Case 5001 to 6000 :
arrLastYrYTD06[counter-5000]:=numValue
Case 6001 to 7000 :
arrLastYrYTD07[counter-6000]:=numValue
Case 7001 to 8000 :
arrLastYrYTD08[counter-7000]:=numValue
Case 8001 to 9000 :
arrLastYrYTD09[counter-8000]:=numValue
Case 9001 to 10000 :
arrLastYrYTD10[counter-9000]:=numValue
default:
0);

if counter=10000 then counter else //This should hold it at 10,000 if it gets that high.
counter:=counter+1;

//End of Second formula

Third is the setup inside the subreport.
Subreport, header section

shared numbervar array arrLastYrYTD01;
shared numbervar array arrLastYrYTD02;
shared numbervar array arrLastYrYTD03;
shared numbervar array arrLastYrYTD04;
shared numbervar array arrLastYrYTD05;
shared numbervar array arrLastYrYTD06;
shared numbervar array arrLastYrYTD07;
shared numbervar array arrLastYrYTD08;
shared numbervar array arrLastYrYTD09;
shared numbervar array arrLastYrYTD10;

shared numbervar array arrCurrYrYTD;
shared numbervar maxarraysize:=1000;
numbervar counter:=1;
numbervar countercurr:=1;

redim preserve arrLastYrYTD01[maxarraysize];
redim preserve arrLastYrYTD02[maxarraysize];
redim preserve arrLastYrYTD03[maxarraysize];
redim preserve arrLastYrYTD04[maxarraysize];
redim preserve arrLastYrYTD05[maxarraysize];
redim preserve arrLastYrYTD06[maxarraysize];
redim preserve arrLastYrYTD07[maxarraysize];
redim preserve arrLastYrYTD08[maxarraysize];
redim preserve arrLastYrYTD09[maxarraysize];
redim preserve arrLastYrYTD10[maxarraysize];

redim preserve arrCurrYrYTD[maxarraysize];

0; //This is here to make the formula not break.
//End of third formula

Last (fourth) is the formula to make the array get checked by the subreport. This is in the subreport, detail or group section.

//Last YTD Rank
//Current Month Rank
evaluateafter({@timingfield});
shared numbervar maxarraysize;
shared numbervar array arrLastYrYTD01;
shared numbervar array arrLastYrYTD02;
shared numbervar array arrLastYrYTD03;
shared numbervar array arrLastYrYTD04;
shared numbervar array arrLastYrYTD05;
shared numbervar array arrLastYrYTD06;
shared numbervar array arrLastYrYTD07;
shared numbervar array arrLastYrYTD08;
shared numbervar array arrLastYrYTD09;
shared numbervar array arrLastYrYTD10;

local numbervar x;
local numbervar numValue:=(Sum ({@numTaxPdLastYearYTD}, {@Groups01License}));
local numbervar numcount:=0;

for x:=1 to 10000
Do
(
Select x
Case 1 to 1000:

(if (arrLastYrYTD01[x] >= numValue)
then
numcount:=numcount+1
else numcount)

Case 1001 to 2000:
(if (arrLastYrYTD02[x-1000] >= numValue)
then
numcount:=numcount+1
else numcount)
Case 2001 to 3000:
(if (arrLastYrYTD03[x-2000] >= numValue)
then
numcount:=numcount+1
else numcount)
Case 3001 to 4000:
(if (arrLastYrYTD04[x-3000] >= numValue)
then
numcount:=numcount+1
else numcount)
Case 4001 to 5000 :
(if (arrLastYrYTD05[x-4000] >= numValue)
then
numcount:=numcount+1
else numcount)
Case 5001 to 6000 :
(if (arrLastYrYTD06[x-5000] >= numValue)
then
numcount:=numcount+1
else numcount)
Case 6001 to 7000 :
(if (arrLastYrYTD07[x-6000] >= numValue)
then
numcount:=numcount+1
else numcount)
Case 7001 to 8000 :
(if (arrLastYrYTD08[x-7000] >= numValue)
then
numcount:=numcount+1
else numcount)
Case 8001 to 9000 :
(if (arrLastYrYTD09[x-8000] >= numValue)
then
numcount:=numcount+1
else numcount)
Case 9001 to 10000 :
(if (arrLastYrYTD10[x-9000] >= numValue)
then
numcount:=numcount+1
else numcount)
default:
0
);

numcount; //This displays the final count.
//End of fourth formula

Scott.