Questions on setting up filter for fiscal YTD totals using current date

[sstryk46]

Current environment: Report Net 1.1 Oracle 10
Issue: I am creating a scheduled report that needs to calculate Fiscal YTD totals (10/1/year – 9/31/year+1) based upon the current date – the date the report is running.

I have been trying to set up a filter using the following logic without success, any suggestions?
 
If current month < 10 then
[date] between (year of current date – 1), month 10, day 1 and year of current date, month 9, day 30
else
[date] between (year of current date ), month 10, day 1 and end date of current year
end if

I tried the following code thinking that I could replace the hard coded dates later, but the conditional statement will not work

IF (extract(month,current_date) <10 ) THEN
(
[ Date]
between _make_timestamp(2005,10,1) and _make_timestamp(2006,9,30)
)
ELSE
(
[ Date]
between _make_timestamp(2006,10,1) and _make_timestamp(20006,12,31)
)

Error Message: QE-DEF-0259 There was a parsing error before or near position: 124, text starting at position: 54 "C Pickup Package].[Picked Up Date Dimension].[Picked Up Date] between".

BTW if I replace “(extract(month,current_date) <10 )” with “(10=10)” then the filter will execute and return values…

Any Advice? – I am going home for the day, back tomorrow (Friday) early – thanks.

[CoginAustin]

(
  extract(month,current_date) <10 AND [ Date] between _make_timestamp(2005,10,1) and Make_timestamp(2006,9,30)
)
OR
(
 extract(month,current_date) >=10 AND [ Date] between _make_timestamp(2006,10,1) and _make_timestamp(20006,12,31)
)

[sstryk46]

Thank you for your reply CoginAustin,
The code you suggested does pass the syntax check, but when I view the tabular data that includes the filter I get the following error:

QE-DEF-0259 There was a parsing error before or near position: 124, text starting at position: 54 "C Pickup Package].[Picked Up Date Dimension].[Picked Up Date] between".

This is the same error I receiving earlier...

I believe that I can't do this functionality in a single filter, but must create a new query that returns the fiscal year for the current date (from the date dimension) and then join that result set to another query such that I get all transactions that have that fiscal year - I can then use that set to calcualte the FYTD subreport.  I will post up if and when this new tack works...

It is a good thing that my datamart is not large or this type of mult-query processing would eat up a large amount of time/resources. I do find it strange that I can not code such a simple logic based filter.

[bdybldr]

Have you tried using a tabular set with two tabular models, Prior FY and Current FY.  Define your filter for each data set.

This should simplify the filter syntax.