Using a formula as a restriction

[DJWheezyWeez]

I'm using CR XI.

I have a PeriodID and Year fields. PeriodID has numbers 0 through 12 which are months and PeriodID 0 is the Beginning Balance at the start of the year. The Year field only has two years, the current one and the next one (2010/2011). I'm working with a fiscal calender so PeriodID 1 is April and we are currently in the 2011 calender year. {table.Balance} tells me money made or lost for each month.

My current parameters have me choose a PeriodID range and either one or both years. If I want to look at {table.Balance} for May, I would need to look PeriodIDs 1 and 2 (April and May) for 2011 as well as PeriodIDs 0-12 for 2010. Problem is they are seperate fields. If I choose PeriodID 1 and 2 and both years, I would only get balances for those 4 months. If I choose PeriodIDs 0-12 and both years, I would get everything I needed but I would also get balance data from June which I do not want.

I've made the report work with this formula:

if {table.PERIODID} in 0 to 12 and 
   {table.YEAR} = 2010
then 0
else {table.PERIODID}

but I want to use minimum({table.YEAR}) in place of typing in the minimum year every time it changes. If I use minimum({table.YEAR}), I can't use it as a restriction. Is there a way around this or will I need to change the report everytime the year changes?

I hope this makes sense and any help is greatly appreciated.

-DJWW

 

[IanWaterman]

Will this work

((Period in [0 to 12] and Year = {YearParam})
or
(period in 1 to {periodParam}-1} and Year = {YearParam}-1))

Ian

 

[IanWaterman]

Sorry should be +1 for 2011

((Period in [0 to 12] and Year = {YearParam})
or
(period in 1 to {periodParam}-1} and Year = {YearParam}+1))

 

[DJWheezyWeez]

Sorry if I'm misunderstanding but is your code supposed to replace just "2010" in mine?

 

[DJWheezyWeez]

I was playing around with your code trying it in different spots in mine and realized I could just use that year parameter for people to choose the year rather than having me change the year manually everytime it needs changing. My final code is:

if {table.PERIODID} in 0 to 12 and 
   {table.YEAR} = {?YearParam}
then 0
else {table.PERIODID}

It seems to have fixed my problem as long as people type in the correct year.

Thanks for the help.

-DJWW

 

[IanWaterman]

But I thought that you had to include periods from 2011.

Ian

 

[DJWheezyWeez]

There's no year restriction on the report so all of them show up. I just needed a way to get all of 2010's PeriodIDs and only the ones I choose for 2011. When the report is run, it will almost always be run starting with PeriodID 0 so I just set all PeriodIDs in 2010 to 0. That way people can choose 0-2 and get all of 2010 plus April and May 2011.

I hope that makes more sense.

-DJWW