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:)
- 3 Answers
- 5746 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
What are the data types present in bo? What happens if we implement view in the designer and report?
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.
What is a fantrap in business objects?
how get the grand total in master-detail report?
Difference between b.o 5.1 and 6.1b?
Regarding Hyperlinking in Business Objects. We have a few reports in QA environment where a number of hyperlinks are employed using OpenDoc function.In some of the reports,hyperlink is used to move to another tab within the same report.The formula used include "DocID" and ReportName="tab name".This works fine but now,for the PROD move,this will not work as the Doc Ids are subject to change. Is there a way to overcome this?Without DocID,how will move to a particular tab? Thanks in advance
what is universe null
I have 2 doubts regarding the "Input Control" functionality in BI4: 1) Can the Input Controls in BI4 be manipulated? I mean, can we change the IC display as BOLD or change the values from Italics to regular? Or change the colors? 2) How to get hierarchy type control in IC? Let's say my first IC is for Country and second is for State. If I select a particulat country from Ist IC,second one should show me states belonging to selected country. Is that possible? Thanks in advance!!!
is it possible to export the webi reports to crystal reports?with out using businessview?
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??
Are data mart and dwh normalized or denormalized? Do both of them exist in the same tier?
Differences between business objects and web intelligence reports?
What are all difference between business objects 6.5 and xi r2?
what is the difference b/w the refresh and buffered data?
What is the difference between business requirement document and use case document.
