redgreenred
asked on
SSRS - Applying multiple Parameters (date) to Reports
I am trying to create a report that takes Product Name & the start and the end date as a parameter...
Product name apply parameters @ report level...
Start and the end date apply parameters @ query level
The problem is that when I select date from the report, it changes the value (replaces Month with the date) before applying query
as an example... when I selected May 1, 2008 from the calender... it got changed to Jan 5, 2008 before running the query...
in my data set, I am using following parameters:
WHERE
convert(datetime,trans_dt, 103) BETWEEN convert(datetime,@FROM_DAT E,103) and convert(datetime,@TO_DATE, 103)
in report parameters, I ve used dateTime as the data type...
Please advice
Product name apply parameters @ report level...
Start and the end date apply parameters @ query level
The problem is that when I select date from the report, it changes the value (replaces Month with the date) before applying query
as an example... when I selected May 1, 2008 from the calender... it got changed to Jan 5, 2008 before running the query...
in my data set, I am using following parameters:
WHERE
convert(datetime,trans_dt,
in report parameters, I ve used dateTime as the data type...
Please advice
ASKER
I am running the query directly in the data set
. Passing one report parameter... which is working fine
. Passing two parameters (start and the end dates) to the query in data set... This is where I am facing problems...
I've converted to type 121 but issue remains the same:
WHEN I SELECT DATES FROM THE CALENDER, DAY AND MONTH GET SWITCHED... BUT WHEN I APPLY THE FOLLOWING FOMAT MANNUALLY IT WORKS FINE (DD/MM/YYY)
Please advice...
Thanks
. Passing one report parameter... which is working fine
. Passing two parameters (start and the end dates) to the query in data set... This is where I am facing problems...
I've converted to type 121 but issue remains the same:
WHEN I SELECT DATES FROM THE CALENDER, DAY AND MONTH GET SWITCHED... BUT WHEN I APPLY THE FOLLOWING FOMAT MANNUALLY IT WORKS FINE (DD/MM/YYY)
Please advice...
Thanks
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
I haven't deployed it to the server yet,... still using BIDS...
Thanks
Thanks
OK, might be worth deploying the report as it stands, and then you may have more luck
ASKER
I've deployed it to the server
Good news: It's working fine for me
Challenge: other users are getting an error message when they select dates from the calender or enter dates in the format (mm/dd/YYY)
here is the message that they are getting:
The value provided for the report parameter 'FROM_DATE' is not valid for its type. (rsReportParameterTypeMism atch)
but when they change the format to dd/mm/yyy it works for them too...
Please advice...
Good news: It's working fine for me
Challenge: other users are getting an error message when they select dates from the calender or enter dates in the format (mm/dd/YYY)
here is the message that they are getting:
The value provided for the report parameter 'FROM_DATE' is not valid for its type. (rsReportParameterTypeMism
but when they change the format to dd/mm/yyy it works for them too...
Please advice...
ASKER
I am using following to filter in the dataset query
trans_dt BETWEEN convert(datetime,@FROM_DAT E,121) and convert(datetime,@TO_DATE, 121)
Thanks
trans_dt BETWEEN convert(datetime,@FROM_DAT
Thanks
OK, a bit closer anyway. Does it now work for you from both BIDS and the server?
This is likely down to a regional setting in the Users internet browsers, which means the default date sent is in the format mm/dd/YYYY, and this is used in the calendar too as it's the default.
It sounds like the users are set up for US, but the report server is expecting a British/European date?
Or I guess it could be the conversion, but I think you would get a different error in that case.
Steps to Reproduce:
1.) Create a report with a date time parameter. Set the initial value to =Today()
2.) Configure the Internet Explorer Language Settings to default US
3.) Open the Report through the Report Viewer (http://servername:port/reportserver)
4.) Change the date value through the date time picker
See if this reproduces (rsReportParameterTypeMism atch), and then try with British default on the browser.
This is likely down to a regional setting in the Users internet browsers, which means the default date sent is in the format mm/dd/YYYY, and this is used in the calendar too as it's the default.
It sounds like the users are set up for US, but the report server is expecting a British/European date?
Or I guess it could be the conversion, but I think you would get a different error in that case.
Steps to Reproduce:
1.) Create a report with a date time parameter. Set the initial value to =Today()
2.) Configure the Internet Explorer Language Settings to default US
3.) Open the Report through the Report Viewer (http://servername:port/reportserver)
4.) Change the date value through the date time picker
See if this reproduces (rsReportParameterTypeMism
ASKER
it worked on the server
OK great, I have been caught out by this one before, and worked on it all day before deploying it on the server!
ASKER
Thanks again
Where are you based, Convert 103 is British/French date, have you tried without the convert as it should try to match your server environment? Could explain why the day and month is backwards as these would be reversed in British. Still strange as you are converting both sides to get consistency.
Maybe try converting as type 121 is this is a more generic format yyyy-mm-dd hh:mi:ss.mmm