This is a migrated thread and some comments may be shown as answers.

Logical Date field in Stored Procedure is showing null value in report

3 Answers 63 Views
General Discussions
This is a migrated thread and some comments may be shown as answers.
Savan
Top achievements
Rank 1
Savan asked on 19 Feb 2017, 02:04 PM

Hello,

I have created a report which gets data from SQL Server using SP. in my sp i have created few logical date fields. 

Example:

DATEADD(Day,-6,'2017-02-17') As [Mon Date],

DATEADD(Day,-5,'2017-02-17') As [Tue Date], 
DATEADD(Day,-4,'2017-02-17') As [Wed Date],

DATEADD(Day,-3,'2017-02-17') As [Thu Date],

DATEADD(Day,-2,'2017-02-17') As [Fri Date], 
DATEADD(Day,-1,'2017-02-17') As [Sat Date]

this SP is having few parameters so in report i have created same report parameters to map them with parameters of SP. i executed the SP in SQL Server and it displays all data properly but when i run the report, the logical date fields are showing null value. i don't understand why it is showing null value.

3 Answers, 1 is accepted

Sort by
0
Stef
Telerik team
answered on 20 Feb 2017, 02:34 PM
Hi Savan,

You can use SQL Profiler to check the queries executed on previewing a report and if the string values are cast correctly into date values on the server. Test casting the string to date before executing the DATEADD function (T-SQL); otherwise, strings are cast to dates with the culture settings of the server machine:
select dateadd(day,2,CONVERT(VARCHAR(24),GETDATE(),6))


In addition, check if the SqlDataSource component is configured correctly to map SQL parameters (based on their type) to report parameters - How to: Connect to a SQL Database.

Regards,
Stef
Telerik by Progress
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
0
Savan
Top achievements
Rank 1
answered on 20 Feb 2017, 03:02 PM

Hi Stef,

Thanks for the quick reply.

as i mentioned in above question that there are multiple parameters in the SP one of that parameter is of Date Type.

name of that parameter is @StartDate

so my Date Add is like this in the SP

DATEADD(Day,-6,@StartDate) As [Mon Date]

when user selects the parameters, Date is selected from Date Picker and passed as parameter to @StartDate in the SP.

i checked the profiler and it is executing properly and SqlDataSource is also properly connected and mapped with parameters.

 

Regards,

Savan

0
Stef
Telerik team
answered on 21 Feb 2017, 02:03 PM
Hello Savan,

Please check if the SQL @StartDate parameter is mapped to a String report parameter, and if the passed string is in valid date format for the SQL server.
If you use a string report parameter, the DateTime object from the Date Picker will be cast to string based on the server machine's culture. this date format may differ from the SQL server date format.


In order to provide you more accurate suggestions, please post the SQL query used by the report, and the report's code allowing us to test locally its settings.

Regards,
Stef
Telerik by Progress
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
Tags
General Discussions
Asked by
Savan
Top achievements
Rank 1
Answers by
Stef
Telerik team
Savan
Top achievements
Rank 1
Share this question
or