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
- 5778 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
Did you face any problems while creating reports? Name? Solutions?
How to sort the dynamic objects alphabetically on the Universe based on and SQL Server
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?
BO Security (creating the restriction sets in Universe and applying on selected groups or users) or Database security (Creating the tables in database insrting the user details in that tables and giving conditions in self restricting joins in Universe )Which Security will improve the performance of the reports and for best practices ? I am thinking BO Security will give the better performance, could you please share your thoughts on this please... Thanks...
U r project in which stage in implementation?
Explain about a time when you discovered a more efficient way to do a work task?
What is the difference between business requirement document and use case document.
What sort of existing documents should business analysts refer to when starting on a new project?
Explain in detail about type 2(scd),
What is the profile and package?
How to create Index to the table in the universe designer and how it improves the Query performence
Explain how can we do load testing for webi reports? (Example if I have have to test one webi report for concurrent users refreshing report at one time)?
Give some information on report bursting and how to do it in bca as I have to split the report and send diff reports to diff people?
WHAT ARE THE META DATA TYPES?WHICH META USING IN BO'S
Explain is there any default username & pwd for bo designer & supervisor?
