Trying to find a 'latest' payment 1

[Iain75]

I am trying to create an object on our v5.1.7 Universe to show the Users the 'LAST PAYMENT' made by a customer.

Each transaction for a customer is given a unique ascending reference number.

In a program, I could use:

SELECT
FT.CUST_NO,
FT.TRANSACTION_AMOUNT,
FT.TRANSACATION_DATE
FROM
FINANCIAL_TRANSACTION FT,
WHERE
FT.TRANSACTION_CODE = 'PAY' AND
FT.CUST_NO =:CUST-NO
ORDER BY
TRANSACTION_ID_DESC
LIMIT TO 1 ROW;

How can I create an object so that a User can create a report showing the latest payment for each customer?

Using 'calculations' in conditions,appears to be very confusing and is a bit-long winded to repeat in Multiple Queries. Effectively what I am looking for is a way of defining an object which becomes a calculation condition.

(apologies if this message has appeared before, I have tried to submit it 3 times this morning!!!)

 

[blom0344]

If the ascending reference number is something like :

REF12345 with a fixed structure you could prune the integer-part out of it (--> 12345) and then use the rank functionality in reporter to show only the top 1 value.

Say: pruned ref-number = objectx

Alternative is to create an object with an OLAP function (if your database allows this) like:

Rank() over (partition by customer order by objectx desc) as #rank

This will return a value = 1 for the highest value of objectx for each customer. You can then simply use a filter...

T. Blom
Information analyst
tbl@shimano-eu.com

 

[Sridharan]

If you using Calculation was too much for users you can create a Predefined Condition at the Universe that selects only the Latest Payment for each customer and show that record... Here is a brief sketch(Change it as per your needs)...

FINANCIAL_TRANSACTION.REF_NO = (SELECT MAX(FT.REF_NO) FROM FINANCIAL_TRANSACTION FT WHERE FINANCIAL_TRANSACTION.CUST_NO = FT.CUST_NO)

In the report use this condition.. I've taken the Max Ref No since you've said its a Unique Ascending Number and the Max Ref No maps to the Latest Payment...

Sri