Hi All,
I am working on implementing a condition of Rank () Over Partition by and Order by functionality in Cognos Report studio, but i observed that we only have Rank function which only does the Order By portion. Below is the attached SQL:
SELECT TO_CHAR(date_key,'YYYYMM') YYYYMM,
date_key as dte,
day_of_month,
RANK() OVER (PARTITION BY month_of_year, year_number ORDER BY day_of_month ) as rank
FROM mis.date_table
WHERE day_of_week = 'FRIDAY'
AND date_key > TRUNC(LAST_DAY(ADD_MONTHS(SYSDATE,-6)))
Can somebody please suggest me how do we pull Rank in Report Studio or how do we write it in a pass thru SQL.
By the look of it you have already written the pass-through SQL , haven't you. You need to make sure that the use of SQL within reports is set for users through capabilities.
In report studio drag an SQL icon to the query explorer and use either native (preferred) or passthrough as SQL-syntax (see properties)