Parameter in WHERE clause using Functions -- sql server reporting services

BLKeller
12/9/2005 2:32:03 PM

I'm trying to use a report parameter in my where clause to compare against
the month portion of a field. Reporting Services (version 2000) does not seem
to like this. Here's the WHERE clause I'm trying to get to work (of course
it's all on one line):

WHERE (company = @Company) AND (MONTH(close_time) = @DSMonth1) AND
(YEAR(close_time) = @DSYear1)

When I try to execute the query, I get an error that states:

Invalid object name "dbo.RAW_DW.MONTH"
Invalid object name "dbo.RAW_DW.YEAR"

It seems Reporting Services thinks MONTH and YEAR are fields when I'm just
trying to use the MONTH and YEAR functions to extract that data from the
datetime field (close_time) and compare it to the report parameter. It may
just be a formatting issue on my part.

Does anyone know what's wrong with the clause?

Thanks in advance.

Rodney Landrum
12/12/2005 2:52:08 PM

Hi Brenda,
I dont think that the error you are getting is related to the WHERE clause
in this case. Do you have RAW_DW.Month and ...YEAR in your overall query?
What are the data types of the two parameters, @DSMonth1 and @DSYear1?
Without seeing the full query, it is hard to troubleshoot the error, if
indeed these are valid objects in your database.
Rodney Landrum

[quoted text, click to view]

sql server reporting services : Parameter in WHERE clause using Functions