Monday, June 27, 2011

The value provided for the report parameter 'LastUploadDate' is not valid for its type

I have a data driven subscription set up that passes a date value from a query to the report for processing. After processing an email is sent. However I did not receive the email. When the report runs the following error occurs in the report server log.

Throwing Microsoft.ReportingServices.Diagnostics.Utilities.ReportParameterTypeMismatchException: , Microsoft.ReportingServices.Diagnostics.Utilities.ReportParameterTypeMismatchException: The value provided for the report parameter 'LastUploadDate' is not valid for its type.;

The report server log can be found the installation directory of SQL Reporting Server. In my case: 
C:\Program Files\Microsoft SQL Server\MSRS10_50.MSSQLSERVER\Reporting Services\LogFiles

The field in the database is of type Date, as is the report parameter definition which is DateTime. If I pass  the date parameter as '2010-06-23' it works fine. On my previous reporting server this subscription work without any problem. I do not understand why it does not work anymore. It is a SQL2008 R2 Reporting Server which connects to a SQL 2008 server. After one day struggling with it I implemented next work around.
The parameter is called: LastUploadDate
The database field is called: Statdate

My previous query for the dat driven subscription:
SELECT Statdate, CustomerID
FROM Mytable

My workaround:
SELECT Cast(YEAR(Statdate) as char(4)) + '-' + CAST(MONTH(Statdate) as CHAR(2)) + '-' + CAST(DAY(Statdate) AS CHAR(2)) AS CASTDATE,

FROM Mytable

So I made a hard coded string of the date I needed. It is indeed not so nice, but it works.
If you have had the same experience? Please let me know how you have fixed it.

1 comment:

Shaun said...

I have also run across the issue of passing a DateTime parameter. I've seen many people struggling with Regional incompatibilities, but that is not my case. I think I may do the same, use a String instead, and convert the date using a similar workaround (not desirable, but probably necessary).

Hopefully someone discovers a proper fix.