You can get the required results by using RANK function. Create an obje

I need to select the rows based on the
latest 'Payment_due_date' column value.
Let's say I have a Deski report with 3 columns:
Payment_due_date,
Cust_Name,
Oustanding_Amt.

Report is like this:
=====================================
Payment_due_date,Cust_Name,Oustanding_Amt
02/28/11 , Bob, 115
03/31/11 , Bob, 90
02/28/11 , Mike, 120
02/28/11 , Susan, 220
05/31/11 , Bob, 80,
03/31/11 , Susan, 70
05/31/11 , Mike, 220
05/31/11 , Susan, 120
04/30/11 , Susan, 130

So,we would like to see (based on latest Payment_due_date)
==================
05/31/11 , Bob, 80,
05/31/11 , Mike, 220
05/31/11 , Susan, 120

Oustanding_Amt comes as SUM(Oustanding_Amt) from Universe.
If we use Max(Payment_due_date),we do get one row but then,
the column 'Oustanding_Amt' sums up.
Not allowed to use do SQL Override .Context Operators
aren't working.

Thanks in advance:)

Question Posted / rajitha praburam

3 Answers
7200 Views
I also Faced
E-Mail Answers

Answer Posted / rajitha praburam

You can get the required results by using RANK function.

Create an object 'Rank' with Number Data type and the below
select option.

RANK() OVER (PARTITION BY CUST_NAME ORDER BY
PAYMENT_DUE_DATE DESC)

In the report, use report level filter add Rank = 1
condition.

Is This Answer Correct ?

1 Yes

0 No

Post New Answer View All Answers

Please Help Members By Posting Answers For Below Questions

Explain how many universes can be created in a project?

1119

If 2 tables are joined by Shortcut join, if query is generated based these join, what will be the join type and what will be the generated query? Thanks in advance?

2258

What is the difference between the public object&schema object?

1132

Explain the difference between union and group?

1176

What is the profile and package?

2346

Explain what are the measure objects?

1046

What is report template in b.o?

1076

How to execute the plsql procedure from the report by clicking with mouse?

1183

Can we Save (save as) WEBI Doc as DESKI?

8298

Explain what are the steps to be taken to schedule the report?

1156

Explain is there any default username & pwd for bo designer & supervisor?

1063

What is thumbnail?

1023

Explain when we use aggregate awareness function in designer?

1091

Explain what three things do you consider to be the most important factors for a manager?

1055

What Is Snow Flake Schema?

1261