Report Design
 Crystal Reports Forum : Crystal Reports 9 through 2022 : Report Design
Message Icon Topic: Cross-Tab 12 Month Rolling Total Columns Post Reply Post New Topic
Author Message
van604
Newbie
Newbie


Joined: 08 Apr 2016
Location: Canada
Online Status: Offline
Posts: 5
Quote van604 Replybullet Topic: Cross-Tab 12 Month Rolling Total Columns
    Posted: 11 Apr 2016 at 2:01pm
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.

IP IP Logged
Valert16
Groupie
Groupie
Avatar

Joined: 21 Mar 2016
Location: Spain
Online Status: Offline
Posts: 57
Quote Valert16 Replybullet Posted: 13 Apr 2016 at 1:28am
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 IP Logged
van604
Newbie
Newbie


Joined: 08 Apr 2016
Location: Canada
Online Status: Offline
Posts: 5
Quote van604 Replybullet Posted: 14 Apr 2016 at 9:26am
Hi Valert
The formula works well in my cross-tab.
Thanks for your help! :)
IP IP Logged
Post Reply Post New Topic
Printable version Printable version

Forum Jump
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot delete your posts in this forum
You cannot edit your posts in this forum
You cannot create polls in this forum
You cannot vote in polls in this forum



This page was generated in 0.031 seconds.