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
7070 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 to schedule the report in b.o?

1050

webI report not showing Barcode in PDF format, i have used BC39 font,but barcode is shown in HTML but not in PDF, please any solutions??

4406

How to generate the report from excel sheet?

1179

is it possible to export the webi reports to crystal reports?with out using businessview?

2289

What is the difference between slice and dice and cross tab report?

1044

how many reports have u created ? explain a few.

2099

How to create a new calendar in CMC? Can u please tell me the process to create the required calendar. Ex:- Year of that calendar to start with Jan-15 and ends with Jan -30 and the Next month to start with Feb-31 and ends with Feb-14.

2445

Explain what task is 98% of the required work of a business analyst?

1103

how to create metric universe ?

2342

What Is Snow Flake Schema?

1206

What is object stores?

984

What are the join problems comes appart from loop,chasm & fan Traps

2132

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

1009

What is the dense rank?

1231

Explain about an assignment you worked on in which you had to amass a huge amount of data, and then analyze it?

1153