Home » Developer & Programmer » Reports & Discoverer » Pick the Latest Record (Rank?) and Count (Oracle BI Discoverer )
Pick the Latest Record (Rank?) and Count [message #473944] Wed, 01 September 2010 10:50 Go to next message
amalison
Messages: 2
Registered: September 2010
Location: Canada
Junior Member

Hi,

Am working on a workbook to count the number of enrolments and withdrawals in the program. My data looks like this
name semester status year
A 1 enrol 2010
A 2 withdraw 2010
A 3 enrol 2010
B 1 enrol 2010
B 2 withdraw 2010

I want to count their latest status only. It should come up with
Total Enrol - 2
Total Withdrawn - 1

For total Withdrawn, I tried 'rank' and filter to equals 1 but it does not allow me. Is there any way to have this work?
Here's my calculation:
count(decode((FIRST_VALUE(status) OVER(PARTITION BY year, name ORDER BY semester DESC)),'withdraw', name))
It tells me that 'Aggregation of Analytic function not allowed'
Re: Pick the Latest Record (Rank?) and Count [message #473963 is a reply to message #473944] Wed, 01 September 2010 11:12 Go to previous messageGo to next message
cookiemonster
Messages: 13917
Registered: September 2008
Location: Rainy Manchester
Senior Member
Well it'd help if you gave the full query.
You should be able to make it work with some nesting:
SELECT count(decode(col1,'withdraw', name))
FROM (SELECT FIRST_VALUE(status) OVER(PARTITION BY year, name ORDER BY semester DESC))
      FROM .....
      WHERE .....
     )
GROUP BY .....


Also can you have a read of the orafaq forum guide and follow it in future.
Re: Pick the Latest Record (Rank?) and Count [message #473964 is a reply to message #473963] Wed, 01 September 2010 11:14 Go to previous messageGo to next message
amalison
Messages: 2
Registered: September 2010
Location: Canada
Junior Member


am using Oracle BI Discoverer. Can't edit the SQL or not know how. Can I?
Re: Pick the Latest Record (Rank?) and Count [message #473965 is a reply to message #473964] Wed, 01 September 2010 11:18 Go to previous message
cookiemonster
Messages: 13917
Registered: September 2008
Location: Rainy Manchester
Senior Member
No idea, never used BI.
Can you create a view that does what you want then use that instead?
Previous Topic: calling report from another report
Next Topic: Reports 10g - Parameter Form - Desname
Goto Forum:
  


Current Time: Fri Mar 29 02:41:00 CDT 2024