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!
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
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
Hi Abhilash,
Just checking that you received my report via your email address.
Thanks!
Cheryl
Thanks, Abhilash. I emailed you the report.
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.
Abhilash,
Thank you SO much for your help. Exactly what I was looking for!
2 additional issues:
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!!!!
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