Range on Rank()

[ToriBurns]

Good day Gurus!

I have a relational data source and a report which shows the rank of a specific data. I used a crosstab for this. It's working fine, except for that the requirement should show the range of the rank and not just the number of rank.

Sample output:

Rank     Data              Value
1          result1          100.00
2          result2            99.75
3          result3            90.50
3          result4            90.50
3          result4            90.50
6          result5            83.00
7          result5            75.00

Desired output:

Rank        Data              Value
1             result1          100.00
2             result2            99.75
3-5          result3            90.50
3-5          result4            90.50
3-5          result4            90.50
6             result5            83.00
7             result5            75.00

Is it possible to do this? Thanks in advance.. ;D

[cognos_learning]

Hi,

We can use dense Rank function to achieve this Requirement.


Thanks
Kumar_CognosDeveloper

[ToriBurns]

can you please tell me how? :)

[Cognos.Developer]

Hi,

We can use t he dense rank function as like below



dense_rank( [Value] for [Data]) ----- In Cognos


dense_rank() over (partition by [Data] order by  [Value] ) ------ DB Level

Dense ranks gives Result like 1,2,3,3,3,4,5,5,5,.....


Thanks
Laxman Kumar

[ToriBurns]

Thanks for the reply Laxman Kumar.  ;D
I tried both of your suggestion but it throws an error.  :(

Error:

QE-DEF-0459 CCLException
QE-DEF-0260 Parsing error before or near position: 18 of: "dense_rank() over"
QE-DEF-0261 QFWP - Parsing text: dense_rank() over (partition by [Data] order by [Value])
QE-DEF-0459 CCLException
QE-DEF-0260 Parsing error before or near position: 18 of: "dense_rank() over"
QE-DEF-0261 QFWP - Parsing text: dense_rank() over (partition by [Data] order by [Value])

You said that

Dense ranks gives Result like 1,2,3,3,3,4,5,5,5,.....

This is the output of my work now.. What I want to achieve is something like:

Rank        Data              Value
1             result1          100.00
2             result2            99.75
3-5          result3            90.50
3-5          result4            90.50
3-5          result4            90.50
6             result5            83.00
7             result5            75.00

I wonder if this is achievable?..Thanks. :D

[nblank]

Add a rownumber to it:
Rownumber   Rank        Data              Value
1                    1             result1          100.00
2                     2             result2            99.75
3                    3          result3            90.50
4                     3          result4            90.50
5                     3          result4            90.50
6                     6             result5            83.00
7                     7             result5            75.00

Then on that data set use the min(rownumber) and max(rownumber) partition by Rank
Combine that into your new rank to get your desired result

[ToriBurns]

I have to add two data item? one for min(RowNumber) partition by Rank and one for max(RowNumber) partition by Rank?

[nblank]

First see that you get the following result:
Rownumber   Rank        Data              Value        min(rownumber)  max(rownumber)
1                    1             result1          100.00        1                         1
2                     2             result2            99.75        2                        2
3                    3          result3            90.50            3                        5
4                     3          result4            90.50           3                        5
5                     3          result4            90.50           3                        5
6                     6             result5            83.00        6                        6
7                     7             result5            75.00        7                        7

Then combine these min and max as your custom rank:
case when min(rownumer) = max(rownumer) then min(rownumer)
        else min(rownumer) + '-' + max(rownumer) end

[ToriBurns]

I can't do the min and max(rownumber) :(
I can't fully understand this : "Then on that data set use the min(rownumber) and max(rownumber) partition by Rank"
Is partition by a syntax? It throws an error. :(

[nblank]

Sorry, now I just see the post before that you got an error with the rank.

This sort of (complex) requirements I allways prepare in the database. So on an Oracle database the min, max and partition will work.

If you have to do this in Cognos, you can use and SQL node as source for the query.

[ToriBurns]

Thank you so much nblank. Still can't do it on oracle database.  :( I'll try to figure it out. Thanks for your help and time. :D ;D

[nblank]

OK. Got that.

It is possible to the the actual sql of the query that feeds your list. Select the query in report studio and choose Tools > Show generated SQL/MDX.

Copy that query and make an new query in report studio with SQL as source. Drag in "Query" from Insertable Objects and drag in "SQL" from Insertable Objects next to it.

Below an example of your data and the result you want to see:

with datasample as
(
select 1 rank ,        'result1' data  ,        100.00 value from dual union all
select 2        ,        'result2'  ,          99.75 from dual union all
select 3        ,    'result3' ,           90.50 from dual union all
select 3        ,     'result4'  ,           90.50 from dual union all
select 3    ,      'result4'   ,         90.50 from dual union all
select 6   ,       'result5'    ,        83.00 from dual union all
select 7  ,        'result5'     ,       75.00 from dual
)
select rank original_rank
       , data
       , value
       , rn
       , dr
       , min(rn) over (partition by dr) min_rn
       , max(rn) over (partition by dr) max_rn
       , case when min(rn) over (partition by dr) = max(rn) over (partition by dr)
                    then to_char(min(rn) over (partition by dr))
                 else min(rn) over (partition by dr) || '-' || max(rn) over (partition by dr)
                 end rank
  from (select datasample.rank
                  , datasample.data
                  , datasample.value
                  , ROW_NUMBER() over(order by datasample.rank  )  AS rn
                  , DENSE_RANK() OVER (ORDER BY datasample.rank) AS dr
           from datasample
           )

Replace the first part in my example with the query you originally use

with datasample as
(
select 1 rank ,        'result1' data  ,        100.00 value from dual union all
select 2        ,        'result2'  ,          99.75 from dual union all
select 3        ,    'result3' ,           90.50 from dual union all
select 3        ,     'result4'  ,           90.50 from dual union all
select 3    ,      'result4'   ,         90.50 from dual union all
select 6   ,       'result5'    ,        83.00 from dual union all
select 7  ,        'result5'     ,       75.00 from dual
)

Becomes

with datasample as
(
< your query frm the list>
)

Dont forget to select the right Data Source for your query and set SQL syntax to "Native".

When you rebuild my example query, just test it in parts.

Hope it works for you!!

Greets Nanno

[ToriBurns]

Thank you very much for your time nblank but there was an error thrown. :(

Error: The server returned an unrecognizable query framework response.

Thanks for the idea. I'll try to explore it. :)

[ToriBurns]

Yey!!!!!Thank you very much nblank.. It's now working. I followed your suggestion. I used SQL node as source. But, I change SQL syntax to pass-through. I did it in database instead. Thank you so much. Without your suggestion I would not be able to think of another way to do it. Thank you so much. :) ;D

[nblank]

Thanks for letting me know it works. Glad I could help.