Year to date filter

building semantic layer
1

Hi, fellow BOers;

I’m trying to create a filter object that I can use in a monthly report. The purpose of the filter is to include all records from a table called Incidents for the year of last month. For example, if I run the report now, it should give me all records from 1/1/2000 through 1/31/2000. If I run the next January (the final report for 2000) it will give me all records from 1/1/2000 through 12/31/2000. This is running in BO 5.0.1 against an Access 97 database. I created the query in Access and then copied the following SQL into a filter object in designer:

WHERE (((Incident.Date)>=DateValue(“1/1/” & IIf(Month(Now())=1,Year(Now())-1,Year(Now()))) And (Incident.Date)<DateValue(Month(Now()) & “/1/” & Year(Now()))))

When I parse the object, I get an error that I have “too few parameters. Expected 2.” Any ideas?

Michael Welter
Sr. Technical Analyst
AirTouch

Listserv Archives (BOB member since 2002-06-25)

2

Hi Michael,
I have a parameter table that I use a subquery on to get variables like this. The parameter table is automatically updated each night. I use this parameter table to drive all my reports. The table has 3 columns: primary key, description and value. The logic is something like this: year = parameter.value when parameter.primarykey = 1 Simon

Listserv Archives (BOB member since 2002-06-25)