Skip to Content

Automatically number rows in Crosstab based on Top N sort

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!

Add comment
10|10000 characters needed characters exceeded

  • Follow
  • Get RSS Feed

6 Answers

  • Best Answer
    Feb 22, 2017 at 11:50 AM

    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

    Add comment
    10|10000 characters needed characters exceeded

  • Feb 08, 2017 at 07:47 AM

    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

    Add comment
    10|10000 characters needed characters exceeded

  • Feb 08, 2017 at 03:03 PM

    Thanks, Abhilash. I emailed you the report.

    Add comment
    10|10000 characters needed characters exceeded

  • Feb 21, 2017 at 11:17 PM

    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.

    Add comment
    10|10000 characters needed characters exceeded

  • Feb 22, 2017 at 04:17 PM

    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!!!!

    Add comment
    10|10000 characters needed characters exceeded

  • Feb 23, 2017 at 10:03 AM

    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
    Add comment
    10|10000 characters needed characters exceeded