Cognos SQL & Native SQL

[mindbender]

Hi,

I have included a rank function in the report to filter the 6 highest values. The rank function is displayed in the Cognos SQL but not in the Native SQL . I presume this is because the Database Server(SQL Server)  does not support the function.

In this case will the database retrieve all the rows and present it to Cognos Server for Filtering. Or is there some other way the processing is carried out .

My main aim is optimum retrieval of the First 6 values.

Thanks in Advance

[goose]

The processing for ranking will take place within cognos. MSSQL 2005 supports ranking but 2000 doesnt. Im not sure if congos will push the ranking to the db (I dont have 2005 so I cant test). Depending on the volume of records selected ranking should not create excessive overhead - but I would recommend you test.

I use ranking in a modified cognos audit report to return the top five reports run each day for the last seven days. That will require the processing of 140,000 records and the report runs on average for 8 seconds and the db/cognos is on a pretty average server.

[blom0344]

If you build a query that is aimed to include only the top 6 values for a whole set, then SQL server can manage just fine.
Like:
SELECT TOP6 X.* FROM TABLE ORDER BY SOME_VALUE DESC

SQL server 2000 does not offer OLAP style functions (2005 does), so it will not be able to return top 6 results for multiple sets (like for each day)
I suspect it will return the whole set (perhaps already ordered) for processing by the C8 server