Crystal Reports Year Selecton

crystal-reportsdate-range

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!

Best Answer

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}

Related Topic