Issue with Rank function in report studio

[DrSmyth]

Hi, this is my first proper thread in the COGNOS forum, so hello everybody...

I've got an issue with ranking and i'm not sure if it's achievable in report studio. Basically i have a report with 5 or 6 prompts (3 of these prompts are for choosing dates which will become relevant later). The output is grouped by one of the prompts (we'll call it P1)

My output needs to be ranked so that I show the top 20 P1's for a specified date based on an aggregated fact (we'll call this F1). This much I can do using a rank() in a filter... But the rest of the report gets tricky!!

Now I need to group the remainding P1's under an 'Other' category. Not sure how to do this..

Also I need to report the same P1's from the two other reporting dates that have been selected (basically using the P1 filter from the first date parameter to filter for the same P1's from the other two dates...

Been racking my brains for about a week over this and can't think of an even half decent solution. My initial response was to try and achieve this in the database by adding a rank column, but because of the filters, I can't really do this..

HELP!!!

 

[blom0344]

That sounds like a very steep requirement.

My first thought would be on using the 'except' operator which will act as the opposite to a union.

Define the query for the entire set (Q1) , then define the query that fetches just the top 20 based on rank (Q2)

Create Q3 as Q1 minus Q2 and it will store all data for the category 'Other'

Oviously this will result in having 2 separate queries to work with , unless you store the entire construction as a database object or as a SQL object in the framework package.

Ties Blom

 

[DrSmyth]

I've had an idea of creating a query with the top 20 from the first date and then creating another query with a case statement based on P1 to decide if it uses it's actual P1 value or 'Other'. Here's my first attempt but it doesn't work!!:

case when  
[P1] in [Query1].[P1] 
then [P1]
else 'Other'
end

Any ideas?

 

[DrSmyth]

Silly me, missed the brackets off of the in statement, now it kind of works. Or at least I don't get any errors. Un fortunately, it times out when running....

I'm guessing this isn't a very efficient way of doing it!!

 

[blom0344]

No ,the comparison has to be done on the Cognos server and the 'in' operator is not very efficient in database terms, probably even worse still if the Cognos server would have to perform this scan.



Ties Blom