I need your help for building a As on Date functionality for Few of my reports & i believe you all giuys can help me on the same.
Please see the attached Excel File as a Sample Data to be worked on.
In the attached excel you can see there is 1 single Receipt Number & have 3 Different Cash Rcpt ID's. (53000, 64001, 73000) now if i select a create a Autogenerated Date & select 17-Jul-2011 then i should search for all the GL Dates & give me the Max GL Date against each of the Cash Rcpt ID's & if the Status is 'REVERSED' for that specific row, then do not show the entire Cash Rcpt ID.
in this above case if i select 17-July-2011 then for
For Cash Rcpt ID :- 53000 max Date = 12-May-2011 & status <> 'REVERSED' hence it should get displayed
For Cash Rcpt ID :- 64001 Max Date = 07-Jul-2011 & Status = 'REVERSED' hence this whole Cash Rcpt ID (2 Rows) should not come in the report.
For Cash Rcpt ID :- 73000 Max Date = 04-Jul-2011 & Status <> 'REVERSED', hence it should come in the report.
2nd Scenario
If i select 6th-July-2011 (in As on Date filed Which is Autogenerated Date Filed but not the GL Date") -- Please Note 🙂
For Cash Rcpt ID :- 53000 max Date = 12-May-2011 & status <> 'REVERSED' hence it should get displayed
For Cash Rcpt ID :- 64001 Max Date = 20-Jan-2011 & Status <> 'REVERSED' hence this Record will come in the report for this as on Date (6th Jul-2011)
For Cash Rcpt ID :- 73000 Max Date = 04-Jul-2011 & Status <> 'REVERSED', hence it should come in the report.
If i give 06-jul-2011 in the input box it should display me the below records
Cash Rcpt ID, GL Date, status, RcptAmt
53000, 12-May-2011, Cleared, 1000 "Here GL Date is <= inputDate"
64001, 20-Jan-2011,cleared,132360 (only this records will come in report, As on input date 06-July-2011 the max date for this id is 20-Jan-2011 & not reversed)
73000,4-Jul-2011,cleared,85000 "Here GL Date is <= inputDate"
If 9 Give 08-Jul-2011 then below said records should come in the report.
Cash Rcpt ID, GL Date, status, RcptAmt
53000, 12-May-2011, Cleared, 1000 "Here GL Date is <= inputDate"
64001, 07-Jul-2011,cleared,132360 (As on input date 08-July-2011 the max date for this id is 07-Jul-2011 & reversed so this entire ID "64001" wont come in the report.)
73000,4-Jul-2011,cleared,85000 "Here GL Date is <= inputDate"
But i dont see if the input Box functionaly is working.
I need your help in bringing all the above said 2 scenarios in the one single Report.
M attaching the Qvd for your ref as i see there is a Problem with the Date "Please find attached the Qvd".
I think u have got very near just a further more small Help..
if i give 20-July2011 in input Box
then you should also compare the GL Date with the <= input Date &
i should give below
53000, 12-May-2011, 1000, CLEARED -- "This record should come as the input box Date "20-Jul-2011" is >= max(GL Date which is 12-May-2011").
64001, 07-July-2011, REVERSED -- "This Row should not come as for this ID "64001" the Max Date is 07-July-2011 & it has a Status 'REVERSED' then the entire ID '64001' should not come.
73000, 04-July-2011, CLEARED -- "This record should come as the max(GL Date) is <= input Box Date. & Status is 'CLEARED'.
----- "If in case i am giving 06-July-2011 in input box then i should get below records"
53000, 12-May-2011, 1000, CLEARED -- "This record should come as the input box Date "20-Jul-2011" is >= max(GL Date which is 12-May-2011").
64001, 20-Jan-2011, CLEARED -- "This Row should come as for this ID "64001" the Max Date is 20-Jan-2011 & it has a Status 'CLEARED' then the record should come.
73000, 04-July-2011, CLEARED -- "This record should come as the max(GL Date) is <= input Box Date. & Status is 'CLEARED'.
No, if i m giving date as 06-July-2011' i.e 06/07/2011 then it should also show me the 64001 record with date as 20-Jan-2011 & Cleared as status but if i give 07/jul/2011 then this entoire Receipt ID 64001 should not come but the other 2 should come as for 64001 it has a latest status as on 7/Jul/2011 is 'Reversed'.
