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:)
Answers were Sorted based on User's Feedback
Answer / 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 |
Answer / rahul
You can achieve this by having a condition.
i.e put payment_date in condition panel-->select operator
as "equal to"-->operand "calculation"-->follow the
steps"select object(payement_date)"-->select function
(maximum)-->Globally-->for each value of one or more objects
(cust name)--finish
Is This Answer Correct ? | 0 Yes | 0 No |
Answer / gs2kumar@gmail.com
Max(Payment_due_date) use this in report level create a variable or in cell.
Is This Answer Correct ? | 0 Yes | 0 No |
Hi Can anyone tell me what is Divergence in Business Object Universe Designer. Thanks, Sheik
what is the different between link and join ?
How to sort the dynamic objects alphabetically on the Universe based on and SQL Server
What is difference between custom hierarchy and report based hierarchy?
i want to creat 2way drilling using hyrarchies and hyperlinks...can anyone please help me
4.what is repository? types
what is variable ?
what is fantrap?
why do we use crystal reports rather than webintelligence reports ?
1. What is difference SAP BW, SAP BI and SAP BOXI integration? 2. Which is good for carrier wise? 3. Which is latest one (BW/BI/SAP BOXI Integration)?
How we will show the pop message while selecting multiple input values more than 10 in Crystal reports or any other reports?
WHAT ARE THE VARIOUS PRODUCTS IN BO?