I am using Cognos 8.4 with postgreSQL database that does NOT support analytical functions like rank.
I need to get the Top 25 customers by sales. Writing my own sql I would just order desc by sales and limit 25. How can I do this in Cognos if I can't utilize 'rank' or 'running_count'? I also do NOT want to set a governor to globally limit the number of rows returned. And I do NOT want to process locally - I want to do the processing in the database.
Thank you,
-John
If the database doesn't support rank you will most likely be forced to use a expression that requires local processing.
Maybe you could use the the pre-sort property of the query item and after that a summary filter. But if this would work I don't know.....
Hi,
You mention you could write your own SQL to limit your rows so perhaps consider using the SQL option in Query Explorer to pass the required information to your query.
Alternatively, you could utilise row number from the toolbox and then use a style variable to hide row numbers over 25, although this will be using local processing.
Regards
Rob
You could also set a filter to exclude rows that are over rownumber 25. Conditional explorer ususally more expensive in terms of performance. Actually, local processing in this case may not be all that bad.
I would not go for the conditional solution, because when the query returns 1000 records, these records are returned first and then hidden/removed by the cognos server.
So a lot of overhead....
If your table contains an aggregate (like the total of sales per customer) or is not too large, then you can define a view that will absolutely guarantee handling on the database.
The technique involved is based on set-based (instead of windows based) generation of rownumbers:
SELECT
CUSTID,
SALES,
(SELECT COUNT(*) FROM SALESTABLE S2 WHERE S2.SALES < S1.SALES) AS ROWNUM
FROM
SALESTABLE S1
ORDER BY SALES DESC
The number of rows scanned increases quite fast. Without proper indexing this may lead to n*n records for a table containing n rows
However, if this performs, then it is simply a matter of setting a filter on the generated ROWNUM to achieve the required result.