Previous 12 months

[CoSpringsGuy]

My table stores statistics and has two fields to use for dates. year (numeric field) and month (text field)

I have no idea why they decided to store like this but thats what I have to work with. I have a report where a user enters a Through Date parameter and I need to return the previous 12 months data. I created code that works but I cant help to think that someone has created something a bit cleaner. Any thoughts?

//
({Clnt_Stats_MTD.FOUR_DIGIT_YEAR}=year({?Through Date})
and tonumber({Clnt_Stats_MTD.STAT_MONTH})in [month({?Through Date}) to 1])
or
({Clnt_Stats_MTD.FOUR_DIGIT_YEAR}=year({?Through Date})-1
and tonumber({Clnt_Stats_MTD.STAT_MONTH})in [month({?Through Date})+1 to 12])

_____________________________________
Crystal Reports XI Developer Version
Intersystems Cache 5.X ODBC connection

 

[chelseatech]

Your suggestion wont' work as the months roll around, and you will end up including or excluding some months.

Better solution is to convert your fields to a working date with (assume 1st of each month)
Date({Clnt_Stats_MTD.FOUR_DIGIT_YEAR},
{Clnt_Stats_MTD.STAT_MONTH}),1)

Then your record selection is simply
{@FormulaAbove} in [{?Through Date} to
DateAdd("yyyy",1, {?Through Date})]

Editor and Publisher of Crystal Clear

http://www.chelseatech.co.nz/pubs.htm

 

[CoSpringsGuy]

I like your solution and I may put it in place because it is a little cleaner but I have thoroughly tested my solution and it works fine every time.

Thanks Chelsea

_____________________________________
Crystal Reports XI Developer Version
Intersystems Cache 5.X ODBC connection