Comparing Last Year Period to This Year in Crystal Reports

>This was one of the toughest things I had to figure out in Crystal because there are very few examples out there on the internet on how to have columns in a report that compare the previous year values to this year.

The challenge:
My CFO wanted a report that showed waste receipts (we are a landfill) that had several columns that looked something like this:

Last Year to date
This year to date
Variance in tons
Variance in %
Previous Year Last 30 Days
This Year Last 30 Days
Variance in Tons
Variance in Percent
Total Receipts Last Year
Forecast Receipts for this year

So I thought how in the world do you do this because crystal only has one detail section and in my head I couldn’t figure out how you would compare two years values with only one query. Here is what I found.

Crystal has built in functions to calculate what period a date is in. Which is great but the logic on how to apply it was escaping me. What I ended up doing is a detail section that looks like this:

I only needed a couple of fields from the database record, the date of the Transaction [TimeOut] the Transaction number [Ticket] and the Tons Received [Tons]

I had to hide the Tons field because we are going to use this for calculations but since a total on tons would be a total for all query results there is no need to use it. Then for each of the period fields I created formula fields using the build in period functions of crystal for example Last Year to Date Tons [LYTD_Tons] is

If {Ticket.TimeOut} in LastYearYTD Then
{@tons}
Else
0

So now I have a tons field that will only display a value when the date of the transaction is in last year to date. Otherwise this value will be a zero. By applying this same logic to the other period fields I can create summary fields that sum all of the period based fields. I placed this summary in the group for each customer. So what I get is a row on the report that shows Customer Name, LYTD, PYMTD, MTD, LY Total. Now that I have these fields I can create additional summaries that calucate the variance from last year.

The end result looks something like this:

In my humble opinion this is truly a work of art. If you have questions or would like more detail please add comments

Crystal Reports Date Difference

>

I have searched and searched over the internet many times trying to perfomr the simple task fo calculating the difference between two dates in Crystal Reports. The result I was looking for had to be formatted in DD:HH:MM:SS. After hours of searching here is how I did it.

Create a formula field that gets the total number of seconds between the start date and completed date:

DATEDIFF(“s”, {Incidents.Start_Date},{Incidents.Completed} )

I then used a second formula field to perform the arithmetic to calculate the remainders and format the output:

WhilePrintingRecords;
NumberVar TotalSec := {@TotalSeconds};
NumberVar Days := Truncate (TotalSec / 86400);
NumberVar Hours := Truncate (Remainder ( TotalSec , 86400) / 3600) ;
NumberVar Minutes := Truncate (Remainder ( TotalSec , 3600) / 60) ;
NumberVar Seconds := Remainder (TotalSec , 60) ;
Totext ( Days , ‘##’ ) + ‘:’ +
Totext ( Hours , ’00’ ) + ‘:’ +
Totext ( Minutes , ’00’ ) + ‘:’ +
Totext ( Seconds , ’00’ )

There that works!! It seemed like it took forever to find the answer to this problem!
Up Next is how to do this based on a work calendar!