Report Design
 Crystal Reports Forum : Crystal Reports 9 through 2022 : Report Design
Message Icon Topic: Crosstabs Post Reply Post New Topic
Author Message
Crystal1962
Newbie
Newbie
Avatar

Joined: 08 May 2008
Online Status: Offline
Posts: 1
Quote Crystal1962 Replybullet Topic: Crosstabs
     Posted: 08 May 2008 at 5:00pm
I am trying to calculate previous year data for the same month range.
 
Current Year
Start Date:  04/01/08  ?StartDate
End Date:    04/30/08  ?EndDate
 
Previous Year
Start Date:  04/01/07
End Date:    04/30/07
 
 
Example:
 
                      Current Yr      Previous Yr     Variance
Books Sold     $   500.00      $    400.00     $  100.00
 
I have set up a parameter in my select statement for the current year start date and end date for the current year.  See above, ?StartDate and ?EndDate.
 
The fields that i am trying to calc are in blue.
 
Thanking you in advance, Lisa
 
 
 
Lisa
IP IP Logged
Lugh
Senior Member
Senior Member
Avatar

Joined: 14 Nov 2007
Online Status: Offline
Posts: 377
Quote Lugh Replybullet Posted: 09 May 2008 at 4:40am
It's a bit tricky, but not impossible.

First, you need to set up your selection criteria. 

(BookSoldDate BETWEEN ?StartDate AND ?EndDate) OR
    (BookSoldDate BETWEEN DATEADD(y,-1,?StartDate) AND DATEADD(y,-1,?Enddate))


Then, you'll need to collapse the data a bit, to make it the fit the desired format.  The simplest way to do this is with a couple formulae.  (A cross-tab will also work, but getting the variance in there is trickier.)  Create a formula (let's call it CurrentYear) that is simply:

IF Year({MyReport.BookSoldDate}) = Year(Date()) THEN
    {MyReport.BookSoldAmt}
ELSE
    0

Create another formula (PastYear) that is similar:

IF Year({MyReport.BookSoldDate}) <> Year(Date()) THEN
    {MyReport.BookSoldAmt}
ELSE
    0

and add it to the report.  In the footer section, put these three formulae:

SUM(@CurrentYear)  //this will be the "Current Yr" total

SUM(@PastYear) //this will be the "Previous Yr" total

SUM(@CurrentYear) - SUM(@PastYear) //this will be the "Variance"


IP IP Logged
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