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
Hi,
We can use dense Rank function to achieve this Requirement.
Thanks
Kumar_CognosDeveloper
can you please tell me how? :)
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
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
Quote from: Help_Cognos on 07 Jul 2014 06:25:49 AM
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
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
I have to add two data item? one for min(RowNumber) partition by Rank and one for max(RowNumber) partition by Rank?
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
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. :(
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.
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
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
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. :)
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
Thanks for letting me know it works. Glad I could help.