Crystal Reports Year Selecton

Question

I'm using Crystal Reports 2011 and have a crosstab that's displaying data by YEAR (Row) and MONTH (Column). I need to report 3 years worth of data at any given time but in the month of April is when I would need to drop an older year to pick up a newer year.

For example, currently since we are in February I am reporting 2010, 2011 and 2012. Once April 1st hits I will be reporting 2011, 2012, and 2013. So I will be dropping 2010 and picking up 2013 on the report. I am looking to automate this so the report will automatically change the years that it reports.

I've tried various record selection formulas but have not been able to nail down one that returns the desired years. The years are currently adjusted manually. On April 1st each year I manually adjust the report to drop 3 years prior and begin including current year data.

I'm looking for suggestions on how I can go about this. Appreciate the help!

If the report were run right now, it would report on January 1st 2010 through February 12th 2012, and if the report were run April 1st, it would report January 1st 2011 through April 1st 2013? Is that correct? — LittleBobbyTables - Au Revoir
Yes currently the report would display January 1st 2010 through February 12th, 2012. On April first it will continue to report January 2010. I have to go in manually to drop year 2010 and add year 2013. My report displays 3 years of data at any given time and April 1st is when I drop the historical year which is 3 years prior (2010) and start reporting the current year 2013. — Trish

LittleBobbyTables - Au Revoir LittleBobbyTables - Au Revoir · Accepted Answer · 2013-02-13T16:42:53

I think I misstated the appropriate date ranges in my comments, but if I understand this correctly, we need formulas to do the following:

if run today, the report will show data from 1/1/2010 to 12/31/2012.
if run on April 1st, it will show data from 1/1/2011 to 4/1/2013.

To do this, I came up with the followign formula fields:

Create a formula field called DateRangeFrom. It should contain:

IF datepart("m", CurrentDate) < 4 THEN

    // Show three years back if we are in the months January - March
    DateSerial (Year(DateAdd("yyyy", -3, CurrentDate)), 1, 1)
ELSE
    // Show two years back if we are in the months of April - December
    DateSerial (Year(DateAdd("yyyy", -2, CurrentDate)), 1, 1)

Create another formula called DateRangeTo. It should contain:

IF datepart("m", CurrentDate) < 4 THEN

    // Show one year back if we are in the months January - March
    DateSerial (Year(DateAdd("yyyy", -1, CurrentDate)), 12, 31)
ELSE
    // Show up to the current date if we are in the months of April - December
    CurrentDate

You can then use these fields in your record selection formula, like this:

{YourTable.YourDateColumn} > {@DateRangeFrom} 
    AND {YourTable.YourDateColumn} <= {@DateRangeTo}

EDIT:

If you just want to search by the year, remove the DateSerial functions, and change your record selection formula to use >= instead of >, and you should be left with:

Create a formula field called DateRangeFrom. It should contain:

IF datepart("m", CurrentDate) < 4 THEN

    // Show three years back if we are in the months January - March
    Year(DateAdd("yyyy", -3, CurrentDate))
ELSE
    // Show two years back if we are in the months of April - December
    Year(DateAdd("yyyy", -2, CurrentDate))

Create another formula called DateRangeTo. It should contain:

IF datepart("m", CurrentDate) < 4 THEN

    // Show one year back if we are in the months January - March
    Year(DateAdd("yyyy", -1, CurrentDate))
ELSE
    // Show up to the current date if we are in the months of April - December
    Year(CurrentDate)

You can then use these fields in your record selection formula, like this:

{YourTable.YourDateColumn} >= {@DateRangeFrom} 
    AND {YourTable.YourDateColumn} <= {@DateRangeTo}

Crystal Reports Year Selecton

1 Answers