I'm new to SSRS and would appreciate some assistance please.
I have created a dataset based on a view with a filter on the DateValueApplixFormat field in order to prompt for a StartDate and EndDate.
When I execute this query from the 'Data' tab, it prompts for the start and end date (which is in yyyy-mm-dd format) and gives me the required results.
I then configured StartDate and EndDate report parameters (datatype - DateTime, non-queried values with 'null' default values). When I preview the report, it prompts for the start and end date (which I select using the date picker). When I try to view the report, it errors with the following:
An error occurred during local report processing.
An error has occurred during report processing.
Cannot read the next data row for the data set DataWarehouse.
Conversion failed when converting datetime from character string.
Is this because the source data is in yyyy-mm-dd format where as when querying using the report parameter it it in dd/mm/yyyy?
The query the dataset is based on is:
SELECT nkCustomer, nkAccount, Currency, DateValueApplixFormat, ReversalIndicator, Amount, Narrative1, Narrative2, BeneficiaryName, nkTransaction
WHERE (DateValueApplixFormat >= @StartDate) AND (DateValueApplixFormat < @EndDate + N'1')