|
I am using Crystal Report 2008.
Goal: Need to create a report to display rolling 12-month lease payments (columns) by company (rows) based on the date parameter entered.
For example, if user enters '3/31/2016' as date parameter, the first column will show sum of lease payments from 4/1/2016 to 3/31/2017, next column show sum from 4/1/2017 to 3/31/2018 etc.
To achieve the goal, I created a Cross-Tab report with the following:
1) Rows - Company_Code
2) Columns - @Year
Formula: if year(PaymentDate) > year(?date)
then year(PaymentDate) else year(CurrentDateTime)
3) Summarized - #RTotal0
Running Total Formula:
Summary
Field to summarize - Lease Payments
Type: Sum
Evaluate
On change of field: @Year
Reset
Use a formula: DateAdd ("m", 12, ?date)
However it is not giving the result that I wanted. Think my running total is incorrect.
Any idea how do I fix the running total to have it work?
Thanks.
|
|
Hi van604,
I never use a running total in a cross-tab, so even when it could work, I prefer other ways.
Not sure if I understand your goal, because I don't know if CurrentDateTime must be used here. Anyway have a look at my proposal...
As I see it, one method that can work is to build a formula that, for every PaymentDate field, returns the interval of 1 year it belongs to. That's to say, if user enters '3/31/2016' as date parameter, all intervals will be [4/1/2016 To 3/31/2017], [4/1/2017 To 3/31/2018], etc.
So in your case, the PaymentDate '6/15/2016' belongs to interval [4/1/2016 To 3/31/2017], but '2/15/2016' belongs to interval [4/1/2015 To 3/31/2016].
If you have a formula that returns the interval a PaymenDate belongs to, you can place it in Columns and use the SUM of Lease Payments as summary field.
The formula (Crystal syntax) could be:
DateVar DateStartCandidate := CDate(Year({Table.PaymentDate}), Month({?date} + 1), Day({?date} + 1));
If {Table.PaymentDate} < DateStartCandidate Then
DateStartCandidate := Cdate(Year({Table.PaymentDate}) - 1, Month({?date} + 1), Day({?date} + 1));
Totext(DateStartCandidate, "dd/MM/yyyy") & " - " & Totext(DateAdd("yyyy", 1, DateStartCandidate)- 1, "dd/MM/yyyy")
Change the names of fields and parameters by yours.
The formula returns intervals as strings like "4/1/2016 - 3/31/2017". Customize it to your needs.
Hope this helps...
|
IP Logged
