Skip to Content
0

Automatically number rows in Crosstab based on Top N sort

Feb 07, 2017 at 06:05 PM

102

avatar image

I have a crosstab which displays the top 100 clinics, based on number of referrals. Is there a way to add an incremented number to each row which changes dynamically based on which clinics make the "top 100" and their order?

Example:

1 Clinic Z 200 Referrals

2 Clinic B 180 Referrals

3 Clinic A 150 Referrals

Thanks for any help!

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

6 Answers

Best Answer
Abhilash Kumar
Feb 22, 2017 at 11:50 AM
0

Hi Cheryl,

Here's what you need to do:

1. Right-click one of the cells of the very first 'Row' field > Format Field > Click the formula button beside 'suppress' and use this code:

shared stringvar array a1;
shared numbervar cnt;
local numbervar i;
for i := 1 to GetNumRows-2 do
(
    if NOT(gridlabelat("@ClinicName",i) IN a1) then
    (
        cnt := cnt + 1;
        redim preserve a1[cnt];
        a1[cnt] := gridlabelat("@ClinicName",i);
    )
);
false;

2. Right-click the same Row field > Format Field > Click the formula button beside 'Display String' and use this code:

shared stringvar array a1;
local numbervar j;
local stringvar str;
for j := 1 to ubound(a1) do
(
    if GridLabelAt("@ClinicName", CurrentRowIndex) = a1[j] then
    (
        str := totext(j,'#') & '. ' & a1[j];
        exit for;
    )
);
str;

This should correctly number Rows per their Top N dynamic ranking.

-Abhilash

Share
10 |10000 characters needed characters left characters exceeded
Abhilash Kumar
Feb 08, 2017 at 07:47 AM
0

Hi Cheryl,

It depends on the structure of your crosstab. Could you please attach the report with saved data or send it to my e-mail?

-Abhilash

Show 1 Share
10 |10000 characters needed characters left characters exceeded

Hi Abhilash,

Just checking that you received my report via your email address.

Thanks!

Cheryl

0
Cheryl Warner Feb 08, 2017 at 03:03 PM
0

Thanks, Abhilash. I emailed you the report.

Share
10 |10000 characters needed characters left characters exceeded
Cheryl Warner Feb 21, 2017 at 11:17 PM
0

Anyone have any ideas to help with this? I have been working on this for days and still have not found an answer.

Thanks for any help.

Share
10 |10000 characters needed characters left characters exceeded
Cheryl Warner Feb 22, 2017 at 04:17 PM
0

Abhilash,

Thank you SO much for your help. Exactly what I was looking for!

2 additional issues:

  1. My manager asked me to show grand totals on top instead of the bottom, which works, EXCEPT I lost the label for “Others”

2. My manager also wants an additional version of the same report sorting top N 100 clinic names alphabetically but retaining the rank information. Is this even possible?

Thank you again for your help!!!!

Share
10 |10000 characters needed characters left characters exceeded
Abhilash Kumar
Feb 23, 2017 at 10:03 AM
0

Hi Cheryl,

For #1, right-click one of the Row fields > Format Field > Click the formula button beside 'suppress'.

Line #4 in the code is:

for i := 1 to GetNumRows-2 do

Change that to: for i := 1 to GetNumRows-1 do#2 can't be done with a Crosstab without first supplying it just the Top 100.-Abhilash
Share
10 |10000 characters needed characters left characters exceeded