Skip to Content
avatar image
Former Member

Ranking in cross tab reports

Hi All,

I am working on a cross tab crystal report and need to display ranks by groups. I have attached a screenshot of the report for convenience (i have grayed out the data). All the columns displayed in the image are developed except for the rank column. Rank column is dependent on the % column before it (i.e. cloumn 8). Also, I was able to sort column 8. I have added a sample rank column and need the ranks accordingly by group.

Thanks in advance!

tmp.jpg (166.0 kB)
Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

2 Answers

  • Best Answer
    avatar image
    Former Member
    Jan 08, 2015 at 07:12 PM

    I was able to come to a solution. Wanted to share if it could be of any help to others. Its not the best solution for sure but is effective.

    if

    NthLargest (1,Field,Group_name) = Field

    then 1

    else if NthLargest (2,Field,Group_name) = Field

    then 2

    else if NthLargest (3,Field,Group_name) = Field

    then 3

    and so on....

    Here "Field" is the the criteria which is used to rank. The criteria in my case was a summarized field.

    And "Group_name" is the level at which the cross tab is grouped.

    Since it was highly unlikely in my case that any group will have more than 20 items so I can rank stuff without making the formula terribly big. But NthLargest has a limitation which will not work for more than 100 items.

    Thanks!

    Add comment
    10|10000 characters needed characters exceeded

  • avatar image
    Former Member
    Jan 06, 2015 at 05:44 PM

    i was able to add a serial column to the report with the help of below code in the Format Field -> "Display String" section of my report. But the problem now is it is not resetting by each group. Instead it is behaving as a serial number.

    Thanks

    numbervar e = 0;

    e:=e+1;

    totext(e,'#');

    Add comment
    10|10000 characters needed characters exceeded