Using min/max year in a formula used for grouping/restrictions 1

[DJWheezyWeez]

I'm using CR XI.

Here is my formula:

//{@New Period 0}
if {table.PERIOD} in 0 to 9 and {table.YEAR} <> minimum({table.YEAR})
then 0
else if {table.PERIOD} in 10 to 12 and {table.YEAR} <> minimum({table.YEAR})
then 1
else {table.PERIOD}

I'm using this formula as a group. I also use it in my restrictions: {@New Period 0} in 1 to {?Last Period}. The Period is the month and I'm using the fiscal year. When people want to see this data for the calender year, they need to see Jan, Feb, Mar which is Period 10, 11, 12 of the previous year. The current report uses {?MinYear} in place of minimum({table.YEAR}) making the user type in the minimum year but it works. Since the report already works, it's not a huge deal if I can't get this new formula to work, but there are a lot of parameter prompts and I thought I might be able to take a few out this way making it easier for the user.

What I want to do is, instead of having a parameter for the user to type in the minimum year, I want the report to look into the YEAR field and choose the minimum year for them. My formulas check out but when I run the report, I get a bunch of errors, none of which make sense to me. Can anyone shed some light on this? Any help is greatly appreciated.

-DJWW

 

[lbass]

You wouldn't be able to group on a formula that references a summary. You would have to create a SQL expression that returned the minimum and then reference that in the formula. The SQL expression would access the database directly and get the minimum year in the database (not just the minimum as defined by your report criteria). You might be better off just using the parameter.

-LB

 

[DJWheezyWeez]

I had a feeling that might be the answer. Thanks for the help.