Report parameter for Date

Question

Post reply

Report parameter for Date

Susan S

SSCommitted

Points: 1771
More actions
May 17, 2006 at 12:02 am

#366202

Hi,
I have created a store proc with 2 parameters ( @startdate and @enddate)
From the report parameters dialog window, I can see these 2 parameters.
I set the data type to ‘DateTime

When I preview it and fill the start and end date then I click on view report.
BUT the error message appears telling me that
“ An error occurred during local report processing.
The value provided for the report parameter ‘Enddate’ is not valid for its type"

I check it and run it again through analyser everything is fine.

Any idea what should I do? Is there any workaround for this.

Thanks,
Susan

Viewing 13 posts - 1 through 12 (of 12 total)

You must be logged in to reply to this topic. Login to reply

Anil Kumar N Valued Member Points: 64 More actions · Answer 1

Hi,

Not sure about the exact cause for this problem, but if you deploy the report and check this will work fine.

Regards

Anil.N

Raj Deep Alapati Ten Centuries Points: 1118 More actions · Answer 2

If you can post the queries which you ran on query analyser and report .it would be helpful to us to evaluate the problem.Post the values you are trying to give also and some sample data in the dataset (result of query in the query analyser)

Thanks

Raj deep.A

Susan S SSCommitted Points: 1771 More actions · Answer 3

Hi,

Query I run in Analyser:

exec PR_Admission '2006/01/01','2006/01/25','444',null,null,null

Result:

8888

Donald

2006-01-01

DOM

DUM

444

BNE DOM

1245

Mickey

2006-01-09

DOM

DUM

444

BNE DOM

2458

Daisy

2006-01-11

DOM

DUM

444

BNE DOM

Hope you can help me

Thanks,

Susan

Susan S SSCommitted Points: 1771 More actions · Answer 4

Additional info :

if I manually force to fill startdate and enddate with ‘yyyy/mm/dd’ format in preview ( VS2005), it works.

However if I choose the date from the given calendar(Date Time Picker), it doesn’t work because it always comes with ‘dd/mm/yyyy’ format

I think the problem is date format

but I don't know which one I should change?

do I need to change in my view?

well actually I try to test it by convert the datetime format to dd/mm/yyyy as british default but it still has the same problem.

andy rose SSC Enthusiast Points: 109 More actions · Answer 5

I had this issue too and it was solved with SQL 2005 Service Pack 1 so I guess it's a bug. As stated before the problem doesn't occur if you deploy and view the report on the report server.

Martin N Ten Centuries Points: 1216 More actions · Answer 6

we're using RS2000 here which doesn't have a date picker... but I would try this...

I'm hoping that you can set the parameter type in your report as DATETIME but that the procedure params can be VARCHAR... Then you can convert the varchar to a SQL date your db can understand... follow me?

Declare your date params in your procedure as VARCHAR (10), as 10 chars is what you need to hold a date format of dd/mm/yyyy. Then in your WHERE clause insert the following...

MyDateField BETWEEN CONVERT(DATETIME,@Start_Date,103) AND CONVERT(DATETIME,@End_Date,103)

So here you're converting the VARCHAR to a SQL DATETIME using the convertion format 103 (dd/mm/yyyy). You need to think about time if your field contains time as well, as the above will truncate datetimes to dd/mm/yyyy 00:00:00.000.

To solve this you can change your @enddate convertion to the following

CONVERT (DATETIME, CONVERT (CHAR (11), @End_Date, 103) + '23:59:59.997', 103)

Let me know if it works.

Kindest Regards,

Martin

Susan S SSCommitted Points: 1771 More actions · Answer 7

Thanks Martin.

I follow your suggestion.

However I could not set my @startdate and @enddate as datetime type in report parameter only as ‘STRING’

It means that users need to manually fill the date.

I guess this is the only choice I have.

It should be alright though it is better if we can utilize the date time picker but not a major problem.

Thank you so much!

Unless someone has another workaround I really appreciate it.

or is it a bug in RS2005?

Susan

Martin N Ten Centuries Points: 1216 More actions · Answer 8

had another thought... create another version of your procedure, change the data params back to DATETIME

Then after the BEGIN, enter SET DATEFORMAT dmy. eg.

CREATE PROCEDURE dbo.MySProc

AS

BEGIN

SET DATEFORMAT dmy

Hopefully this will allow SQL to interpret the date from your report (you're sure that RS is formatting the date as dmy?). There are other options for date formats for SET DATEFORMAT, you can look it up in BOL.

Let me know if it works with the date picker.

Kindest Regards,

Martin

Chris Smith-266894 SSC Enthusiast Points: 161 More actions · Answer 9

Personally, I've had so many problems with datetimes, both coding-wise and human error wise (I'm from the UK, and for those who don't know - 01/02/06 means 1st Feb 2006 to us), And as we don't really have any need to look further than whole days, I just convert datetimes to the nearest whole integer in my fact tables (but look out for rounding before noon and after noon to different days).

I use FLOOR(CONVERT(DECIMAL (12,5), TimeID))

I then always use integers to pass into queries, from a date dimension table, populating the parameters selectbox with a 2 column query of the integer and the appropriate date format as varchar. This way the user can only choose dates which are actually available in the warehouse, and can never enter the wrong sort of date.

Biggest problem I've had with this is from VB which has a different integer value for dates.

Sarah M Young SSC Journeyman Points: 85 More actions · Answer 10

RE: Human error as Chris said...

Here's what I do w/ my date/time...we've created a web page with essentially 2 frames. The bottom frame is the report, the top is for parameters. So we've placed a calendar control in the reports that require date/time. Then we can deal with date/time conversion within out stored procedure as we pass the parms.

Chris Smith-266894 SSC Enthusiast Points: 161 More actions · Answer 11

Is this possible in reporting services? Or is this a 'normal' web page?

Susan S SSCommitted Points: 1771 More actions · Answer 12

Hi Martin,

I have tried your method before it still doesn't work.

I have even troed every possible way but still...

well I guess right now I will use your 1st advice and keep itu as varchar.

thanks,

Susan