Limiting data in record selection 1

[Harki06]

Hi,

I am using Crystal Reports 10 and CE10. I have a transaction table that has transaction date and amount
e.g 1/1/07 $1000
2/1/07 $2000
1/1/06 $1000
2/1/06 $3000
3/1/06 $1000

I display sum of current year, sum of previous year and %change for YTD. I want the data for previous year to be calculated UPTO the month for which currnt year data is available so as to compare apples to apples i.e if data for this year is uptil Feb (as in example above), then data for previous year should be loaded for the report until Feb and not March. Is it possible to do this in record selection?

 

[Madawc]

Possible, but complex. I'd do it as a set of 'boolians', formula fields that have no 'IF@ and return True or False. You can show them with the data to fine-tune the rule and then use it for record selection once you're sure it's right.

You need one check for current year, something like Year({your.date}) = Year(currentdate). Call this @ThisYear.

For last year, @LastYear, something like

Year({your.date}) = Year(currentdate)-1
and
{YourDate} <= currentdate-365

This doesn't allow for leap-years, that would mean more coding and perhaps using DateAdd.

Note that in record selection, the tests can be used by saying something like @ThisYear or @LastYear

Madawc Williams (East Anglia, UK). Using Windows XP & Crystal 10

 

[Harki06]

My apologies but can you explain what Year({your.date}) = Year(currentdate). Call this @ThisYear is doing when you imply your.date. is your.date referring to the transaction date in the table?

 

[lbass]

Try a record selection formula like:

year({table.date}) in year(currentdate)-1 to year(currentdate) and
month({table.date}) < month(currentdate)

You could also make this speedier by creating SQL expressions using the year and month functions.

-LB

 

[Harki06]

This works perfect! I tried retrieving it this way..Thanks a lot!!!

 

[Harki06]

I tested for 2006 to 2007 and month < 6. In that case it still retireves upto 5 months for 2006 and 2 months for 2007. i.e Jan to May for 2006 and Jan and Feb for 2007. If the user can select the month and it is not based on the current date, how do I take care of that?

Thanks!

 

[lbass]

Do you want the report to always be limited by the current date, i.e., with dates that are at least less than the month for the current date, but which could possibly be set for earlier months? You could then change the selection formula to:

year({table.date}) in year(currentdate)-1 to year(currentdate) and
month({table.date}) < month(currentdate) and
month({table.date}) <= {?lastfullmonth}

-LB

 

[lbass]

PS. I should have said {?selectedmonth} so as not to confuse things.

-LB

 

[Harki06]

What I am looking at is the user selecting ANY month and me selecting the data for the previous year only if data is present for the current year. Let me rephrase it is year1 and year2.

 

[lbass]

So if the user selects June, and it is currently March, what would you want to see on the report? If you would want to see Jan - Feb of Year 1 and Jan - Feb of Year 2, that is what my last formula would do.

-LB

 

[Harki06]

It does. My apoligies. I checked the data. Designed 2 reports to check the same. Joins with other tables is adding more data.. If the user were to select June, if Year 1 has data until June, year 2 should be displayed for June.

Thanks!