DatePicker parameter not working when published

[Gerry]

I have been trying to create a financial aging report.  To do that, I have used a db2 sql with "days(date(@ARDATE as date)) - days(date(of char string for ar dates) as invoicedays".  The ARDATE is a datetime report parameter.

This statement works fine in the Execute Query option as well as the Report Designer Preview.  Unfortunately, when the report is published to our database, the invoicedays values show as zero. 

If I replace days(date(@ARDATE as date))  with days(current date), the calculation of days shows the correct values when published.

Note: The @ARDATE works with <=, >= or between in db2 sql so I think the format of data from the datepicker is '2016-03-10:hh:mm:ss'. 

Note: I can also change the parameter type to string and use csv data source as '2015-12-31, 2016-01-31,2016-02-29, etc to get the report to work.

How do I get the report to accept the @ARDATE from within the sql statement?

[Stef]

Hello Gerry,

Please test the following:

1. Add a report parameter with tyep DateTime;
2. On configuring the SqlDataSource map the @ARDATE parameter to the added report parameter, and specify the type of the @ARDATE parameter as DateTime. Thus the value will be treated as a date and there is no need of further conversions in the SQL query.
3. Check the queries executed on the SQL server and what values are used on executing SQL query used by the report.

In case you are using the HTML5 Viewer, verify that Newtosoft.Json.dll is updated to the latest possible version via VS Nuget packages manager. This guarantees that the viewer's requests to the server are formatted correctly.


I hope this information is helpful.

Regards,
Stef
Telerik

[Gerry]

Hi Stef:

I tried your suggestion plus others without success.  Nothing I change seems to allow the days function to work or even just to save the date when the report is published.

If I use days(@ardate), I get the error “SQL0418 Use of parameter not valid” during the Execute Query process.  From searching the web, I gather that DB2 SQL requires data type declaration.

Any other suggestions?

Gerry

[Stef]

Hi Gerry,

Please double-check the used .NET data provider and if it allows you to create proper connection with the database and interprets correctly the passed parameters' values.

Also the SQL query's syntax must be considered with the selected .NET data provider, whether parameters are specified by ?,@ or other reserved symbol. Test to validate the SQL query on the database server - Receiving the message "Error [SQL0418] Use of parameter marker not valid (SQL State = 42610, Vendor code = -418." when trying validate a query in Studio.

Regards,
Stef
Telerik