DatePicker parameter not working when published

4 posts, 0 answers
  1. Gerry
    Gerry avatar
    2 posts
    Member since:
    Mar 2016

    Posted 10 Mar Link to this post

    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?

  2. Stef
    Admin
    Stef avatar
    3053 posts

    Posted 12 Mar Link to this post

    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
    Do you want to have your say when we set our development plans? Do you want to know when a feature you care about is added or when a bug fixed? Explore the Telerik Feedback Portal and vote to affect the priority of the items
  3. DevCraft banner
  4. Gerry
    Gerry avatar
    2 posts
    Member since:
    Mar 2016

    Posted 14 Mar in reply to Stef Link to this post

    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
  5. Stef
    Admin
    Stef avatar
    3053 posts

    Posted 17 Mar Link to this post

    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
    Do you want to have your say when we set our development plans? Do you want to know when a feature you care about is added or when a bug fixed? Explore the Telerik Feedback Portal and vote to affect the priority of the items
Back to Top