How to add built in oracle analytic function to cognos (specifically lead/lag)?

[FM]

Hello.

I am in charge of a rather large cognos installation.
We have over 100 authors and over 2000 consumers.

Our authors vary in their experience, but once of constant requests i've gotten, is for the Oracle analytical functions
lead and lag.
(Note: i do know how to add functions to cognos. )
Since these functions give much better performance then the alternatives (outer join to identical query, rownum + 1)
i'd like to ask if anyone here has come across this problem.

Due to this, we regularly have an issue where authors write reports in SQL. This is an issue since they are later harder to maintain.

If i can offer these analytical functions from the cognos interface, out authors won't have the incentive to do this.

So, does anyone know how to add analytical functions to cognos? Or should i try for a pl/sql wrapper?  :P

Thanks in advance.

[CognosPaul]

Cognos doesn't natively support the analytical functions, but you can still use them in RS and FM.

The following is a field I have in the business layer of one of my models.

Code Select Expand

{lag(DIM_TIME.TIME_KEY,1,0) over (order by DIM_TIME.TIME_KEY)}

Note that the curly brackets and the field names. The curly brackets forces Cognos to use the field expression as SQL.

So if a user pulled in [Time].[Date] and [Time].[Previous Date] then the SQL would look like:

Code Select Expand

select "DIM_TIME"."DAY_DATE" "Date1", lag(DIM_TIME.TIME_KEY,1,0) over (order by DIM_TIME.TIME_KEY) "Previous_Date" from "DS"."DIM_TIME" "DIM_TIME"

The problem is that if the user tries to pull only [Time].[Previous Date], perhaps before they pull [Time].[Date], then Cognos will throw an error:
UDA-SQL-0524 An unexpected request tree node type was encountered during the code generation phase of the PREPARE operation.

As long as the query is built correctly than the SQL will be generated correctly.