Syntax error near "="

[FirstStruck]

Hi,
I'm writing a report that will be automatically ran the day after each quarter. (The first of April, July, October, and January)
I want it to display YTD for each quarter.
I think it doesn't like the (Month((Current_Date)=4)), (Month((Current_Date)=7)), etc  in my IF statement script below. It keeps giving me the syntax error in the subject line. Thanks for your help.

IF
(Month((Current_Date)=4))
Then
([Business Layer].[Payroll History Summary].[Period Control Date] between _First_Of_Month(_Add_Months((Current_Date),-3)) AND   _Last_Of_Month(_Add_Months((Current_Date),-1)))
ELSE(
IF
(Month((Current_Date)=7))
Then
([Business Layer].[Payroll History Summary].[Period Control Date] between _First_Of_Month(_Add_Months((Current_Date),-6)) AND   _Last_Of_Month(_Add_Months((Current_Date),-1)))
ELSE(
IF
(Month((Current_Date)=10))
Then
([Business Layer].[Payroll History Summary].[Period Control Date] between _First_Of_Month(_Add_Months((Current_Date),-9)) AND   _Last_Of_Month(_Add_Months((Current_Date),-1)))
ELSE(
([Business Layer].[Payroll History Summary].[Period Control Date] between _First_Of_Month(_Add_Months((Current_Date),-12)) AND   _Last_Of_Month(_Add_Months((Current_Date),-1))))))

[blom0344]

Use the extract function:

extract(month;[somedate])

as this is a true Cognos function (you use those within the expression as well)

[FirstStruck]

Blom,

Thanks for your help!
This fixed my = syntax error

However, now I moved to my next error with a Syntax error for my "between" statements.

I have tested each of them in a seperate query and they all work perfect on their own, it seems to just be the fact they are in the IF statements?

Here is the new code using the extract function to you told me about - Thanks again!

IF
(extract(Month, (Current_Date)) = extract(month, (2012-04-01)))
Then
([Business Layer].[Payroll History Summary].[Period Control Date] between _First_Of_Month(_Add_Months((Current_Date),-3)) AND _Last_Of_Month(_Add_Months((Current_Date),-1)))
ELSE(
IF
(extract(Month, (Current_Date)) = extract(month, (2012-07-01)))
Then
([Business Layer].[Payroll History Summary].[Period Control Date] between _First_Of_Month(_Add_Months((Current_Date),-6)) AND _Last_Of_Month(_Add_Months((Current_Date),-1)))
ELSE(
IF
(extract(Month, (Current_Date)) = extract(month, (2012-10-01)))
Then
([Business Layer].[Payroll History Summary].[Period Control Date] between _First_Of_Month(_Add_Months((Current_Date),-9)) AND _Last_Of_Month(_Add_Months((Current_Date),-1)))
ELSE(
([Business Layer].[Payroll History Summary].[Period Control Date] between _First_Of_Month(_Add_Months((Current_Date),-12)) AND _Last_Of_Month(_Add_Months((Current_Date),-1))))))

[blom0344]

Your are confused regarding the use of 'if then else' ..

The If part refers to logic (an equation if you like)  The  then and else parts can contain a constant, a dataitem, an expresion or another  'if then else' , but not another equation.

That is why your construction does not work. It is not a matter of syntax but the way  'if then else' (or  a case ) works

What are you attempting to achieve?

[FirstStruck]

This report will run on the first of April, July, October, and January.
April 1, 2012 I want it to display info for Jan 1, 2012 - Mar 31, 2012
July 1, 2012 I want it to display infor for Jan 1, 2012 - Jun 30, 2012
Oct 1, 2012 I want it to display info for Jan 1, 2012 - Sep 30, 2012
Jan 1, 2013 I want it to display infor for Jan 1, 2012 - Dec 31, 2012
April 1, 2013 I want it display info for Jan 1, 2013 - Mar 31, 2013

I don't want to use static dates because I don't want to have to edit the formulas each year.

[blom0344]

Would it not be an easier solution to use a filter to get the 3months data prior to the systemdate? 

[FirstStruck]

I'm not sure I follow what you're asking.

[cognostechie]

This report will run on the first of April, July, October, and January.
April 1, 2012 I want it to display info for Jan 1, 2012 - Mar 31, 2012
July 1, 2012 I want it to display infor for Jan 1, 2012 - Jun 30, 2012
Oct 1, 2012 I want it to display info for Jan 1, 2012 - Sep 30, 2012
Jan 1, 2013 I want it to display infor for Jan 1, 2012 - Dec 31, 2012
April 1, 2013 I want it display info for Jan 1, 2013 - Mar 31, 2013

I don't want to use static dates because I don't want to have to edit the formulas each year.

Do it the simple and effective way -

(
Extract(month,current_date) = 1 
   and
extract(year,[Business Layer].[Payroll History Summary].[Period Control Date]) = extract(year,current_date) -1
)
     OR
(
Extract(month,current_date) > 1 
   and
extract(year,[Business Layer].[Payroll History Summary].[Period Control Date]) = extract(year,current_date)
)