Ranking 2 different fields 1

[Barkley564]

I'm using CR10 with SQL server.

I have a report that correctly ranks YTD performance. I also need it to rank MTD performance.

Example:

USER MTD RANK YTD RANK
111 4 1
021 1 2
303 3 3
... etc

I need the report listed in order by YTD rank. I tried a subreport for MTD rank, and it works, but the processing time is not acceptable (20k records in the file and 500 users to report)

My thought for a soultion is to gather the MTD rank in a single subreport array and pass it back for use in the main report, but I can't seem to make it work. The user field is unique and could be used to track the MTD ranking.

I've not done an array before, so if that is the right solution can someone help me out. If there's another solution please let me know.

 

[Turkbear]

Hi,
What is the data model used?

Does each record have a YTD and MTD rank value and are there 1 to 12 MTDs fo each YTD?







To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[fsreport]

hi
i had the same problem
i used the group number from the special field section
and did a sort on the mtd decending
as for the ytd i created a sub report and used the same thing as the main report


fsreport

 

[Barkley564]

Thanks for the responses. The rank value is calculated by the report itself and that is what I'm having trouble generating. I used a formula I found in another thread to generate the YTD rank, but it works off of a TopN and the order of the list.

There is only 1 MTD value, but I need to be able to rank it as well. The formula I'm using now for YTD is:

whileprintingrecords;
numbervar rank;
numbervar gpa;

if groupnumber = 1 then (
rank := 1;
gpa := Sum ({@1stOne}, {Agent.AgentNo})
);
if Sum ({@1stOne}, {Agent.AgentNo}) <> gpa then (
rank := groupnumber;
gpa := Sum ({@1stOne}, {Agent.AgentNo})
);
rank

The problem is if I change this to the MTD number it ranks it exactly the same as YTD.

 

[lbass]

Insert the subreport in the report header,insert a group on user, add the field/calculation that you base the rank on ({@1stOne}, insert a sum on it, and then do a group sort on that field, in descending order. Add a record selection formula that limits the results to monthtodate. Then add this formula to the group section of the subreport:

whileprintingrecords;
shared numbervar array x;
shared stringvar array y;
shared numbervar i;
shared numbervar j := distinctcount({table.user});
redim preserve x[j+1];
redim preserve y[j+1];

if not(totext({table.user},"000") in y) then
(
i := i + 1;
if i < j+1 then
(
redim preserve x;
redim preserve y;
x := groupnumber;
y := totext({table.user},"000")
));

Then in the main report add this formula to the user group section:

whileprintingrecords;
shared numbervar array x;
shared stringvar array y;
shared numbervar i;
shared numbervar j;
numbervar array z;

for i := 1 to j do(
if totext({table.user},"000") = y then
(
redim preserve z[j];
z[j] := x
)
);
z[j];

-LB

 

[Barkley564]

LBass -

Thanks! That's exactly what I needed, and very thorough. I have a couple of questions so I better understand this and can apply it to other uses.

Where you used "000" as in
if not(totext({table.user},"000") I was getting an error message "Too many arguments have been given to this function". I removed the ,"000" and it worked as intended.

I was wondering what the function of the "000" was intended for to make sure I don't need it.

 

[lbass]

I assumed the field was a number field and converted it to text to ensure that it always had three-digits, e.g., "001", so that when testing whether it was in the array, it would only evaluate against "001", and not 1,11,111, etc.

-LB