Hello,
I have some Reporting Services users who want to setup subscriptions for
reports and enter the current month, trailing 7 days, this week, etc. in the
start and end date parameter fields. Can they do this with code (i.e.
currentdate(-7)) or does this have to be setup as a schedule on the server?
Any assistance is appreciated
Thank you
David Mahler
dmahler@.superwarehouse.comDear David,
I am not sure if I understand your question well, but if you need to
address last week, you may use sql like
select ...
from ...
where date between dateadd(day,-7,getdate()) and getdate().
getdate() will give you current date-time, and dateadd will shift back
7 days.
See dateadd() for more help.
Best Regards,
leecz
On 1=D4=C24=C8=D5, =C9=CF=CE=E71=CA=B153=B7=D6, David Mahler <David
Mah...@.discussions.microsoft.com> wrote:
> Hello,
> I have some Reporting Services users who want to setup subscriptions for
> reports and enter the current month, trailing 7 days, this week, etc. in t=he
> start and end date parameter fields. Can they do this with code (i.e.
> currentdate(-7)) or does this have to be setup as a schedule on the server=?
> Any assistance is appreciated
> Thank you
> David Mahler
> dmah...@.superwarehouse.com|||give an exampl as how you would like to see the subscription..
On Jan 4, 11:41 am, leecz <li.cheng...@.gmail.com> wrote:
> Dear David,
> I am not sure if I understand your question well, but if you need to
> address last week, you may use sql like
> select ...
> from ...
> where date between dateadd(day,-7,getdate()) and getdate().
> getdate() will give you current date-time, and dateadd will shift back
> 7 days.
> See dateadd() for more help.
> Best Regards,
> leecz
> On 1=D4=C24=C8=D5, =C9=CF=CE=E71=CA=B153=B7=D6, David Mahler <David
>
> Mah...@.discussions.microsoft.com> wrote:
> > Hello,
> > I have some Reporting Services users who want to setup subscriptions for=
> > reports and enter the current month, trailing 7 days, this week, etc. in= the
> > start and end date parameter fields. Can they do this with code (i.e.
> > currentdate(-7)) or does this have to be setup as a schedule on the serv=er?
> > Any assistance is appreciated
> > Thank you
> > David Mahler
> > dmah...@.superwarehouse.com- Hide quoted text -
> - Show quoted text -|||Thanks for your replies. I apologize for not being clear in my description.
I am referrring to the front end web interface where users can setup their
own subscriptions.
The user creates a new subscription for a report. The select the delivery
method, how often the report runs, and other parrameters. When they enter
the Start Date and End Date (might be with the calendar control), could they
enter a getdate(-7) or something like that to run the report for the current
month, trailing week, etc.
My thought is that this may have to be done on the back end. I would hope
that the user can set the report to run every week and get the current week's
data on an automated basis.
Thanks for your help
David
"Sridar K" wrote:
> give an exampl as how you would like to see the subscription..
> On Jan 4, 11:41 am, leecz <li.cheng...@.gmail.com> wrote:
> > Dear David,
> > I am not sure if I understand your question well, but if you need to
> > address last week, you may use sql like
> > select ...
> > from ...
> > where date between dateadd(day,-7,getdate()) and getdate().
> > getdate() will give you current date-time, and dateadd will shift back
> > 7 days.
> > See dateadd() for more help.
> >
> > Best Regards,
> > leecz
> >
> > On 1æ'4æ?¥, ä¸?å'1æ?¶53å', David Mahler <David
> >
> >
> >
> > Mah...@.discussions.microsoft.com> wrote:
> > > Hello,
> >
> > > I have some Reporting Services users who want to setup subscriptions for
> > > reports and enter the current month, trailing 7 days, this week, etc. in the
> > > start and end date parameter fields. Can they do this with code (i.e.
> > > currentdate(-7)) or does this have to be setup as a schedule on the server?
> >
> > > Any assistance is appreciated
> >
> > > Thank you
> >
> > > David Mahler
> > > dmah...@.superwarehouse.com- Hide quoted text -
> >
> > - Show quoted text -
>
Showing posts with label setup. Show all posts
Showing posts with label setup. Show all posts
Thursday, March 22, 2012
Date parameter format incorrectly interpreted
Hi,
I am using SQL Server 2005 Reporting Services. I have a report with a Startdate and Enddate parameters setup through the report parameters box. The datatype is datetime, so I have the date pickers. My machine regional settings are set to Ireland for datetime format dd/mm/yyyy. The database field being queried is storing the date as dd/mm/yyyy.
I am using SQL Server 2005 Reporting Services. I have a report with a Startdate and Enddate parameters setup through the report parameters box. The datatype is datetime, so I have the date pickers. My machine regional settings are set to Ireland for datetime format dd/mm/yyyy. The database field being queried is storing the date as dd/mm/yyyy.
My problem is when I type in the values Startdate = 01 aug 2005 and Enddate = 31 aug 2005. The report runs correctly, but it refreshes the parameter boxes and if I try to run it again I get the following message
"An error occured during local report processing. The value provided for the report parameter "Enddate" is not valid for its type"
If I choose the same dates from the datepickers I get the same error message. If I choose larger dates for Startdate like 17/08/2005, I get the same message. It is as though the parameter text box is expecting a US formatted date mm/dd/yyyy.
If I enter the values in US format mm/dd/yyyy. The report will run but the date is then changed to Irish format dd/mm/yyyy which if run again will generate the above error. I have tried doing a CONVERT on the parameters in the SQL of the dataset behind the report, but this does not fix the issue, nor does CAST, see below:
" and p.paymentdate >= Convert(datetime, '" & Parameters!Startdate.Value & "', 103) " &
" and p.paymentdate <=Convert(datetime, '" & Parameters!Enddate.Value & "', 103) "
Users will expect to be able to enter dates in their local regional format which is dd/mm/yyyy. Can you suggest a solution for this behaviour?
Thanks and Regards,
RenaHi guys,
I'm having the exact same problem. Any suggestions yet?
My reports worked fine under SQL2000 ... only started this issue once migrated to SQL2005. My Regional settings are English UK but it seems as if the dates passes by the datepickers are being interpreted as US.
Tks ... Richard|||
I have been having the same issues - unless the details in the datetime parameter box are in American format (and numeric dates only) then the failure occurs in Preview mode.
I have managed to get two (semi-workable) fixes - although I cannot stand being forced into this on my machine. Clearly its a bug in Reporting Services 2005 Designer.
Firstly, ensure that the language for the report itself is set to your country (Layout view, Report Properties) It defaults to English US. Changing this appears to fix the issue within the deployed report.
Secondly, change the Regional Settings for your Development Machine. Customize the short date format to "yyyy-MM-dd" (Control Panel / Regional and Language Options / Regional Options / Customize.../ Date) This assists the report Preview
Do not use any option which has textual dates (Jan/Feb, etc) or you will receive an Index was outside the bounds of the array error/failure when you try to preview.
Note, Visual Studio 2005 RS does not implement the configuration change message, and it must be restarted for any new date settings to take effect.
I know that this is an awful way to run your machine (which can also break some apps) but at least you dont need to restart everything each time. ;-)
See if it works for you.
Ian
Hi,
Thanks for the advice Ian. I had exactly the same problem and have since managed to get things working again. This seems to be an issue with the release version of Visual Studion since the Beta 2 version worked fine (in this respect anyway!). However it is still unacceptable to expect users to change their date settings simply for Reporting Services. This could lead to all sorts of confusion/conflicts with different apps. When the service pack comes along this would need to feature. Surely dates are a key component of reports!
Thanks again
John
|||Exactly the same issue, but with a few twists. The report has a start date and end date and both are set to datetime parameters. Run this in preview mode gives an incorrect parameter value when a date of 13th Feb is selected. But if you type in 13 Feb 2006, then the date is accepted OK. Checked all the report settings as UK & deployed expecting to be able to run this on the report server. However the same message appeared on the server. So logged onto the server as myself and altered my international settings to UK (from US) and reran the report. This then worked correctly.
So, I guess the question is how can I ensure that this will work for all users of the report without having to have them logon to the server and set up their profile (which ain't going to happen) ? Do I need to mod the default profile on the server to set this to UK. Ideas anyone ?
|||If this problem is occuring when Previewing reports in SQL Server Business Intelligence Development Studio or in Visual Studio 2005, try applying SP1 of SQL Server 2005. There was a bug where we'd incorrectly change the parameter language when you clicked the View Report button in preview.
Hope that helps,
-Lukasz
|||
Installing SP1 did not resolve this problem for me. Here are the steps to re-create the problem:
1. In the Windows Control Panel, customize your regional settings - set the short date format to MMM d, yyyy (for example).
2. Close Visual Studio 2005 and open it again.
3. Try to preview a report that includes a parameter of type DateTime - it errors with "Index was outside the bounds of the array." It makes no difference whether you use the datepicker control to provide a parameter value, or whether you enter the value manually (in the custom format or in the standard English US format MM/dd/yyyy)
4. To work around this, close Visual Studio, return to the Windows Control Panel and set the short date format to MM/dd/yyyy. Restart Visual Studio and the report previews without any error.
Is this indeed a bug in SSRS2005, or have I made a mistake somewhere in the configuration of RS (or in the design of the report itself)?
Thanks,
Dan
|||Hi Lukasz
I have just applied SP1 to Visual Studio 2005 and have re-tested whether the Business Intelligence Development Studio is now fixed, but unfortunately I have to report that it is not - the View Report button in preview mode still reports UK formatted dates as incorrect formats.
In your message you talk about applying SP1 to SQL Server 2005 - are you meaning apply an SP1 to SQL Server Management Studio (i.e. the client tool on my PC), OR to the SQL Server 2005 which is hosting Reporting Services (which, by the way, is not the same box where most of my data is, because the data server is an older version of SQL Server).
In fact the Data Source connection string is sometimes ORACLE but I still get the same error message about UK dates being incorrectly formatted.
Many thanks,
Mike.
|||In IE, Under tools/Internet Options/Languages make sure you have the correct language selected.
One of the users was complaining that the dates were around in American format (instead of Australia) and this fixed the problem.
|||Yes, Browser settings are set to UK English.
But browser settings shouldn't be part of the equation as the problem's in Visual Studio.
Anyway, thanks for your suggestion.
|||Dear Lukasz,
After all this time I'm still unable to solve this issue. I have installed SP2 on RS2005 and after I press the preview report button the language settings seem to go back to the IE option.
I need ENG UK and have put it in the report properties (also checked in .rdl file for any other language tags that have different values from the one specified above).
I'm feeling desperate and can't seem to find a way out of this... not having a datepicked is not a good option.
Best regards and thanks in advance,
Jo?o Costa
Date parameter format incorrectly interpreted
Hi,
I am using SQL Server 2005 Reporting Services. I have a report with a Startdate and Enddate parameters setup through the report parameters box. The datatype is datetime, so I have the date pickers. My machine regional settings are set to Ireland for datetime format dd/mm/yyyy. The database field being queried is storing the date as dd/mm/yyyy.
I am using SQL Server 2005 Reporting Services. I have a report with a Startdate and Enddate parameters setup through the report parameters box. The datatype is datetime, so I have the date pickers. My machine regional settings are set to Ireland for datetime format dd/mm/yyyy. The database field being queried is storing the date as dd/mm/yyyy.
My problem is when I type in the values Startdate = 01 aug 2005 and Enddate = 31 aug 2005. The report runs correctly, but it refreshes the parameter boxes and if I try to run it again I get the following message
"An error occured during local report processing. The value provided for the report parameter "Enddate" is not valid for its type"
If I choose the same dates from the datepickers I get the same error message. If I choose larger dates for Startdate like 17/08/2005, I get the same message. It is as though the parameter text box is expecting a US formatted date mm/dd/yyyy.
If I enter the values in US format mm/dd/yyyy. The report will run but the date is then changed to Irish format dd/mm/yyyy which if run again will generate the above error. I have tried doing a CONVERT on the parameters in the SQL of the dataset behind the report, but this does not fix the issue, nor does CAST, see below:
" and p.paymentdate >= Convert(datetime, '" & Parameters!Startdate.Value & "', 103) " &
" and p.paymentdate <=Convert(datetime, '" & Parameters!Enddate.Value & "', 103) "
Users will expect to be able to enter dates in their local regional format which is dd/mm/yyyy. Can you suggest a solution for this behaviour?
Thanks and Regards,
RenaHi guys,
I'm having the exact same problem. Any suggestions yet?
My reports worked fine under SQL2000 ... only started this issue once migrated to SQL2005. My Regional settings are English UK but it seems as if the dates passes by the datepickers are being interpreted as US.
Tks ... Richard|||
I have been having the same issues - unless the details in the datetime parameter box are in American format (and numeric dates only) then the failure occurs in Preview mode.
I have managed to get two (semi-workable) fixes - although I cannot stand being forced into this on my machine. Clearly its a bug in Reporting Services 2005 Designer.
Firstly, ensure that the language for the report itself is set to your country (Layout view, Report Properties) It defaults to English US. Changing this appears to fix the issue within the deployed report.
Secondly, change the Regional Settings for your Development Machine. Customize the short date format to "yyyy-MM-dd" (Control Panel / Regional and Language Options / Regional Options / Customize.../ Date) This assists the report Preview
Do not use any option which has textual dates (Jan/Feb, etc) or you will receive an Index was outside the bounds of the array error/failure when you try to preview.
Note, Visual Studio 2005 RS does not implement the configuration change message, and it must be restarted for any new date settings to take effect.
I know that this is an awful way to run your machine (which can also break some apps) but at least you dont need to restart everything each time. ;-)
See if it works for you.
Ian
Hi,
Thanks for the advice Ian. I had exactly the same problem and have since managed to get things working again. This seems to be an issue with the release version of Visual Studion since the Beta 2 version worked fine (in this respect anyway!). However it is still unacceptable to expect users to change their date settings simply for Reporting Services. This could lead to all sorts of confusion/conflicts with different apps. When the service pack comes along this would need to feature. Surely dates are a key component of reports!
Thanks again
John
|||Exactly the same issue, but with a few twists. The report has a start date and end date and both are set to datetime parameters. Run this in preview mode gives an incorrect parameter value when a date of 13th Feb is selected. But if you type in 13 Feb 2006, then the date is accepted OK. Checked all the report settings as UK & deployed expecting to be able to run this on the report server. However the same message appeared on the server. So logged onto the server as myself and altered my international settings to UK (from US) and reran the report. This then worked correctly.
So, I guess the question is how can I ensure that this will work for all users of the report without having to have them logon to the server and set up their profile (which ain't going to happen) ? Do I need to mod the default profile on the server to set this to UK. Ideas anyone ?
|||If this problem is occuring when Previewing reports in SQL Server Business Intelligence Development Studio or in Visual Studio 2005, try applying SP1 of SQL Server 2005. There was a bug where we'd incorrectly change the parameter language when you clicked the View Report button in preview.
Hope that helps,
-Lukasz
|||
Installing SP1 did not resolve this problem for me. Here are the steps to re-create the problem:
1. In the Windows Control Panel, customize your regional settings - set the short date format to MMM d, yyyy (for example).
2. Close Visual Studio 2005 and open it again.
3. Try to preview a report that includes a parameter of type DateTime - it errors with "Index was outside the bounds of the array." It makes no difference whether you use the datepicker control to provide a parameter value, or whether you enter the value manually (in the custom format or in the standard English US format MM/dd/yyyy)
4. To work around this, close Visual Studio, return to the Windows Control Panel and set the short date format to MM/dd/yyyy. Restart Visual Studio and the report previews without any error.
Is this indeed a bug in SSRS2005, or have I made a mistake somewhere in the configuration of RS (or in the design of the report itself)?
Thanks,
Dan
|||Hi Lukasz
I have just applied SP1 to Visual Studio 2005 and have re-tested whether the Business Intelligence Development Studio is now fixed, but unfortunately I have to report that it is not - the View Report button in preview mode still reports UK formatted dates as incorrect formats.
In your message you talk about applying SP1 to SQL Server 2005 - are you meaning apply an SP1 to SQL Server Management Studio (i.e. the client tool on my PC), OR to the SQL Server 2005 which is hosting Reporting Services (which, by the way, is not the same box where most of my data is, because the data server is an older version of SQL Server).
In fact the Data Source connection string is sometimes ORACLE but I still get the same error message about UK dates being incorrectly formatted.
Many thanks,
Mike.
|||In IE, Under tools/Internet Options/Languages make sure you have the correct language selected.
One of the users was complaining that the dates were around in American format (instead of Australia) and this fixed the problem.
|||Yes, Browser settings are set to UK English.
But browser settings shouldn't be part of the equation as the problem's in Visual Studio.
Anyway, thanks for your suggestion.
|||Dear Lukasz,
After all this time I'm still unable to solve this issue. I have installed SP2 on RS2005 and after I press the preview report button the language settings seem to go back to the IE option.
I need ENG UK and have put it in the report properties (also checked in .rdl file for any other language tags that have different values from the one specified above).
I'm feeling desperate and can't seem to find a way out of this... not having a datepicked is not a good option.
Best regards and thanks in advance,
Jo?o Costa
Date parameter format incorrectly interpreted
Hi,
I am using SQL Server 2005 Reporting Services. I have a report with a Startdate and Enddate parameters setup through the report parameters box. The datatype is datetime, so I have the date pickers. My machine regional settings are set to Ireland for datetime format dd/mm/yyyy. The database field being queried is storing the date as dd/mm/yyyy.
I am using SQL Server 2005 Reporting Services. I have a report with a Startdate and Enddate parameters setup through the report parameters box. The datatype is datetime, so I have the date pickers. My machine regional settings are set to Ireland for datetime format dd/mm/yyyy. The database field being queried is storing the date as dd/mm/yyyy.
My problem is when I type in the values Startdate = 01 aug 2005 and Enddate = 31 aug 2005. The report runs correctly, but it refreshes the parameter boxes and if I try to run it again I get the following message
"An error occured during local report processing. The value provided for the report parameter "Enddate" is not valid for its type"
If I choose the same dates from the datepickers I get the same error message. If I choose larger dates for Startdate like 17/08/2005, I get the same message. It is as though the parameter text box is expecting a US formatted date mm/dd/yyyy.
If I enter the values in US format mm/dd/yyyy. The report will run but the date is then changed to Irish format dd/mm/yyyy which if run again will generate the above error. I have tried doing a CONVERT on the parameters in the SQL of the dataset behind the report, but this does not fix the issue, nor does CAST, see below:
" and p.paymentdate >= Convert(datetime, '" & Parameters!Startdate.Value & "', 103) " &
" and p.paymentdate <=Convert(datetime, '" & Parameters!Enddate.Value & "', 103) "
Users will expect to be able to enter dates in their local regional format which is dd/mm/yyyy. Can you suggest a solution for this behaviour?
Thanks and Regards,
RenaHi guys,
I'm having the exact same problem. Any suggestions yet?
My reports worked fine under SQL2000 ... only started this issue once migrated to SQL2005. My Regional settings are English UK but it seems as if the dates passes by the datepickers are being interpreted as US.
Tks ... Richard|||
I have been having the same issues - unless the details in the datetime parameter box are in American format (and numeric dates only) then the failure occurs in Preview mode.
I have managed to get two (semi-workable) fixes - although I cannot stand being forced into this on my machine. Clearly its a bug in Reporting Services 2005 Designer.
Firstly, ensure that the language for the report itself is set to your country (Layout view, Report Properties) It defaults to English US. Changing this appears to fix the issue within the deployed report.
Secondly, change the Regional Settings for your Development Machine. Customize the short date format to "yyyy-MM-dd" (Control Panel / Regional and Language Options / Regional Options / Customize.../ Date) This assists the report Preview
Do not use any option which has textual dates (Jan/Feb, etc) or you will receive an Index was outside the bounds of the array error/failure when you try to preview.
Note, Visual Studio 2005 RS does not implement the configuration change message, and it must be restarted for any new date settings to take effect.
I know that this is an awful way to run your machine (which can also break some apps) but at least you dont need to restart everything each time. ;-)
See if it works for you.
Ian
Hi,
Thanks for the advice Ian. I had exactly the same problem and have since managed to get things working again. This seems to be an issue with the release version of Visual Studion since the Beta 2 version worked fine (in this respect anyway!). However it is still unacceptable to expect users to change their date settings simply for Reporting Services. This could lead to all sorts of confusion/conflicts with different apps. When the service pack comes along this would need to feature. Surely dates are a key component of reports!
Thanks again
John
|||Exactly the same issue, but with a few twists. The report has a start date and end date and both are set to datetime parameters. Run this in preview mode gives an incorrect parameter value when a date of 13th Feb is selected. But if you type in 13 Feb 2006, then the date is accepted OK. Checked all the report settings as UK & deployed expecting to be able to run this on the report server. However the same message appeared on the server. So logged onto the server as myself and altered my international settings to UK (from US) and reran the report. This then worked correctly.
So, I guess the question is how can I ensure that this will work for all users of the report without having to have them logon to the server and set up their profile (which ain't going to happen) ? Do I need to mod the default profile on the server to set this to UK. Ideas anyone ?
|||If this problem is occuring when Previewing reports in SQL Server Business Intelligence Development Studio or in Visual Studio 2005, try applying SP1 of SQL Server 2005. There was a bug where we'd incorrectly change the parameter language when you clicked the View Report button in preview.
Hope that helps,
-Lukasz
|||
Installing SP1 did not resolve this problem for me. Here are the steps to re-create the problem:
1. In the Windows Control Panel, customize your regional settings - set the short date format to MMM d, yyyy (for example).
2. Close Visual Studio 2005 and open it again.
3. Try to preview a report that includes a parameter of type DateTime - it errors with "Index was outside the bounds of the array." It makes no difference whether you use the datepicker control to provide a parameter value, or whether you enter the value manually (in the custom format or in the standard English US format MM/dd/yyyy)
4. To work around this, close Visual Studio, return to the Windows Control Panel and set the short date format to MM/dd/yyyy. Restart Visual Studio and the report previews without any error.
Is this indeed a bug in SSRS2005, or have I made a mistake somewhere in the configuration of RS (or in the design of the report itself)?
Thanks,
Dan
|||Hi Lukasz
I have just applied SP1 to Visual Studio 2005 and have re-tested whether the Business Intelligence Development Studio is now fixed, but unfortunately I have to report that it is not - the View Report button in preview mode still reports UK formatted dates as incorrect formats.
In your message you talk about applying SP1 to SQL Server 2005 - are you meaning apply an SP1 to SQL Server Management Studio (i.e. the client tool on my PC), OR to the SQL Server 2005 which is hosting Reporting Services (which, by the way, is not the same box where most of my data is, because the data server is an older version of SQL Server).
In fact the Data Source connection string is sometimes ORACLE but I still get the same error message about UK dates being incorrectly formatted.
Many thanks,
Mike.
|||In IE, Under tools/Internet Options/Languages make sure you have the correct language selected.
One of the users was complaining that the dates were around in American format (instead of Australia) and this fixed the problem.
|||Yes, Browser settings are set to UK English.
But browser settings shouldn't be part of the equation as the problem's in Visual Studio.
Anyway, thanks for your suggestion.
|||Dear Lukasz,
After all this time I'm still unable to solve this issue. I have installed SP2 on RS2005 and after I press the preview report button the language settings seem to go back to the IE option.
I need ENG UK and have put it in the report properties (also checked in .rdl file for any other language tags that have different values from the one specified above).
I'm feeling desperate and can't seem to find a way out of this... not having a datepicked is not a good option.
Best regards and thanks in advance,
Jo?o Costa
Date parameter format incorrectly interpreted
Hi,
I am using SQL Server 2005 Reporting Services. I have a report with a Startdate and Enddate parameters setup through the report parameters box. The datatype is datetime, so I have the date pickers. My machine regional settings are set to Ireland for datetime format dd/mm/yyyy. The database field being queried is storing the date as dd/mm/yyyy.
I am using SQL Server 2005 Reporting Services. I have a report with a Startdate and Enddate parameters setup through the report parameters box. The datatype is datetime, so I have the date pickers. My machine regional settings are set to Ireland for datetime format dd/mm/yyyy. The database field being queried is storing the date as dd/mm/yyyy.
My problem is when I type in the values Startdate = 01 aug 2005 and Enddate = 31 aug 2005. The report runs correctly, but it refreshes the parameter boxes and if I try to run it again I get the following message
"An error occured during local report processing. The value provided for the report parameter "Enddate" is not valid for its type"
If I choose the same dates from the datepickers I get the same error message. If I choose larger dates for Startdate like 17/08/2005, I get the same message. It is as though the parameter text box is expecting a US formatted date mm/dd/yyyy.
If I enter the values in US format mm/dd/yyyy. The report will run but the date is then changed to Irish format dd/mm/yyyy which if run again will generate the above error. I have tried doing a CONVERT on the parameters in the SQL of the dataset behind the report, but this does not fix the issue, nor does CAST, see below:
" and p.paymentdate >= Convert(datetime, '" & Parameters!Startdate.Value & "', 103) " &
" and p.paymentdate <=Convert(datetime, '" & Parameters!Enddate.Value & "', 103) "
Users will expect to be able to enter dates in their local regional format which is dd/mm/yyyy. Can you suggest a solution for this behaviour?
Thanks and Regards,
RenaHi guys,
I'm having the exact same problem. Any suggestions yet?
My reports worked fine under SQL2000 ... only started this issue once migrated to SQL2005. My Regional settings are English UK but it seems as if the dates passes by the datepickers are being interpreted as US.
Tks ... Richard|||
I have been having the same issues - unless the details in the datetime parameter box are in American format (and numeric dates only) then the failure occurs in Preview mode.
I have managed to get two (semi-workable) fixes - although I cannot stand being forced into this on my machine. Clearly its a bug in Reporting Services 2005 Designer.
Firstly, ensure that the language for the report itself is set to your country (Layout view, Report Properties) It defaults to English US. Changing this appears to fix the issue within the deployed report.
Secondly, change the Regional Settings for your Development Machine. Customize the short date format to "yyyy-MM-dd" (Control Panel / Regional and Language Options / Regional Options / Customize.../ Date) This assists the report Preview
Do not use any option which has textual dates (Jan/Feb, etc) or you will receive an Index was outside the bounds of the array error/failure when you try to preview.
Note, Visual Studio 2005 RS does not implement the configuration change message, and it must be restarted for any new date settings to take effect.
I know that this is an awful way to run your machine (which can also break some apps) but at least you dont need to restart everything each time. ;-)
See if it works for you.
Ian
Hi,
Thanks for the advice Ian. I had exactly the same problem and have since managed to get things working again. This seems to be an issue with the release version of Visual Studion since the Beta 2 version worked fine (in this respect anyway!). However it is still unacceptable to expect users to change their date settings simply for Reporting Services. This could lead to all sorts of confusion/conflicts with different apps. When the service pack comes along this would need to feature. Surely dates are a key component of reports!
Thanks again
John
|||Exactly the same issue, but with a few twists. The report has a start date and end date and both are set to datetime parameters. Run this in preview mode gives an incorrect parameter value when a date of 13th Feb is selected. But if you type in 13 Feb 2006, then the date is accepted OK. Checked all the report settings as UK & deployed expecting to be able to run this on the report server. However the same message appeared on the server. So logged onto the server as myself and altered my international settings to UK (from US) and reran the report. This then worked correctly.
So, I guess the question is how can I ensure that this will work for all users of the report without having to have them logon to the server and set up their profile (which ain't going to happen) ? Do I need to mod the default profile on the server to set this to UK. Ideas anyone ?
|||If this problem is occuring when Previewing reports in SQL Server Business Intelligence Development Studio or in Visual Studio 2005, try applying SP1 of SQL Server 2005. There was a bug where we'd incorrectly change the parameter language when you clicked the View Report button in preview.
Hope that helps,
-Lukasz
|||
Installing SP1 did not resolve this problem for me. Here are the steps to re-create the problem:
1. In the Windows Control Panel, customize your regional settings - set the short date format to MMM d, yyyy (for example).
2. Close Visual Studio 2005 and open it again.
3. Try to preview a report that includes a parameter of type DateTime - it errors with "Index was outside the bounds of the array." It makes no difference whether you use the datepicker control to provide a parameter value, or whether you enter the value manually (in the custom format or in the standard English US format MM/dd/yyyy)
4. To work around this, close Visual Studio, return to the Windows Control Panel and set the short date format to MM/dd/yyyy. Restart Visual Studio and the report previews without any error.
Is this indeed a bug in SSRS2005, or have I made a mistake somewhere in the configuration of RS (or in the design of the report itself)?
Thanks,
Dan
|||Hi Lukasz
I have just applied SP1 to Visual Studio 2005 and have re-tested whether the Business Intelligence Development Studio is now fixed, but unfortunately I have to report that it is not - the View Report button in preview mode still reports UK formatted dates as incorrect formats.
In your message you talk about applying SP1 to SQL Server 2005 - are you meaning apply an SP1 to SQL Server Management Studio (i.e. the client tool on my PC), OR to the SQL Server 2005 which is hosting Reporting Services (which, by the way, is not the same box where most of my data is, because the data server is an older version of SQL Server).
In fact the Data Source connection string is sometimes ORACLE but I still get the same error message about UK dates being incorrectly formatted.
Many thanks,
Mike.
|||In IE, Under tools/Internet Options/Languages make sure you have the correct language selected.
One of the users was complaining that the dates were around in American format (instead of Australia) and this fixed the problem.
|||Yes, Browser settings are set to UK English.
But browser settings shouldn't be part of the equation as the problem's in Visual Studio.
Anyway, thanks for your suggestion.
|||Dear Lukasz,
After all this time I'm still unable to solve this issue. I have installed SP2 on RS2005 and after I press the preview report button the language settings seem to go back to the IE option.
I need ENG UK and have put it in the report properties (also checked in .rdl file for any other language tags that have different values from the one specified above).
I'm feeling desperate and can't seem to find a way out of this... not having a datepicked is not a good option.
Best regards and thanks in advance,
Jo?o Costa
sqlDate parameter format incorrectly interpreted
Hi,
I am using SQL Server 2005 Reporting Services. I have a report with a Startdate and Enddate parameters setup through the report parameters box. The datatype is datetime, so I have the date pickers. My machine regional settings are set to Ireland for datetime format dd/mm/yyyy. The database field being queried is storing the date as dd/mm/yyyy.
I am using SQL Server 2005 Reporting Services. I have a report with a Startdate and Enddate parameters setup through the report parameters box. The datatype is datetime, so I have the date pickers. My machine regional settings are set to Ireland for datetime format dd/mm/yyyy. The database field being queried is storing the date as dd/mm/yyyy.
My problem is when I type in the values Startdate = 01 aug 2005 and Enddate = 31 aug 2005. The report runs correctly, but it refreshes the parameter boxes and if I try to run it again I get the following message
"An error occured during local report processing. The value provided for the report parameter "Enddate" is not valid for its type"
If I choose the same dates from the datepickers I get the same error message. If I choose larger dates for Startdate like 17/08/2005, I get the same message. It is as though the parameter text box is expecting a US formatted date mm/dd/yyyy.
If I enter the values in US format mm/dd/yyyy. The report will run but the date is then changed to Irish format dd/mm/yyyy which if run again will generate the above error. I have tried doing a CONVERT on the parameters in the SQL of the dataset behind the report, but this does not fix the issue, nor does CAST, see below:
" and p.paymentdate >= Convert(datetime, '" & Parameters!Startdate.Value & "', 103) " &
" and p.paymentdate <=Convert(datetime, '" & Parameters!Enddate.Value & "', 103) "
Users will expect to be able to enter dates in their local regional format which is dd/mm/yyyy. Can you suggest a solution for this behaviour?
Thanks and Regards,
RenaHi guys,
I'm having the exact same problem. Any suggestions yet?
My reports worked fine under SQL2000 ... only started this issue once migrated to SQL2005. My Regional settings are English UK but it seems as if the dates passes by the datepickers are being interpreted as US.
Tks ... Richard|||
I have been having the same issues - unless the details in the datetime parameter box are in American format (and numeric dates only) then the failure occurs in Preview mode.
I have managed to get two (semi-workable) fixes - although I cannot stand being forced into this on my machine. Clearly its a bug in Reporting Services 2005 Designer.
Firstly, ensure that the language for the report itself is set to your country (Layout view, Report Properties) It defaults to English US. Changing this appears to fix the issue within the deployed report.
Secondly, change the Regional Settings for your Development Machine. Customize the short date format to "yyyy-MM-dd" (Control Panel / Regional and Language Options / Regional Options / Customize.../ Date) This assists the report Preview
Do not use any option which has textual dates (Jan/Feb, etc) or you will receive an Index was outside the bounds of the array error/failure when you try to preview.
Note, Visual Studio 2005 RS does not implement the configuration change message, and it must be restarted for any new date settings to take effect.
I know that this is an awful way to run your machine (which can also break some apps) but at least you dont need to restart everything each time. ;-)
See if it works for you.
Ian
Hi,
Thanks for the advice Ian. I had exactly the same problem and have since managed to get things working again. This seems to be an issue with the release version of Visual Studion since the Beta 2 version worked fine (in this respect anyway!). However it is still unacceptable to expect users to change their date settings simply for Reporting Services. This could lead to all sorts of confusion/conflicts with different apps. When the service pack comes along this would need to feature. Surely dates are a key component of reports!
Thanks again
John
|||Exactly the same issue, but with a few twists. The report has a start date and end date and both are set to datetime parameters. Run this in preview mode gives an incorrect parameter value when a date of 13th Feb is selected. But if you type in 13 Feb 2006, then the date is accepted OK. Checked all the report settings as UK & deployed expecting to be able to run this on the report server. However the same message appeared on the server. So logged onto the server as myself and altered my international settings to UK (from US) and reran the report. This then worked correctly.
So, I guess the question is how can I ensure that this will work for all users of the report without having to have them logon to the server and set up their profile (which ain't going to happen) ? Do I need to mod the default profile on the server to set this to UK. Ideas anyone ?
|||If this problem is occuring when Previewing reports in SQL Server Business Intelligence Development Studio or in Visual Studio 2005, try applying SP1 of SQL Server 2005. There was a bug where we'd incorrectly change the parameter language when you clicked the View Report button in preview.
Hope that helps,
-Lukasz
|||
Installing SP1 did not resolve this problem for me. Here are the steps to re-create the problem:
1. In the Windows Control Panel, customize your regional settings - set the short date format to MMM d, yyyy (for example).
2. Close Visual Studio 2005 and open it again.
3. Try to preview a report that includes a parameter of type DateTime - it errors with "Index was outside the bounds of the array." It makes no difference whether you use the datepicker control to provide a parameter value, or whether you enter the value manually (in the custom format or in the standard English US format MM/dd/yyyy)
4. To work around this, close Visual Studio, return to the Windows Control Panel and set the short date format to MM/dd/yyyy. Restart Visual Studio and the report previews without any error.
Is this indeed a bug in SSRS2005, or have I made a mistake somewhere in the configuration of RS (or in the design of the report itself)?
Thanks,
Dan
|||Hi Lukasz
I have just applied SP1 to Visual Studio 2005 and have re-tested whether the Business Intelligence Development Studio is now fixed, but unfortunately I have to report that it is not - the View Report button in preview mode still reports UK formatted dates as incorrect formats.
In your message you talk about applying SP1 to SQL Server 2005 - are you meaning apply an SP1 to SQL Server Management Studio (i.e. the client tool on my PC), OR to the SQL Server 2005 which is hosting Reporting Services (which, by the way, is not the same box where most of my data is, because the data server is an older version of SQL Server).
In fact the Data Source connection string is sometimes ORACLE but I still get the same error message about UK dates being incorrectly formatted.
Many thanks,
Mike.
|||In IE, Under tools/Internet Options/Languages make sure you have the correct language selected.
One of the users was complaining that the dates were around in American format (instead of Australia) and this fixed the problem.
|||Yes, Browser settings are set to UK English.
But browser settings shouldn't be part of the equation as the problem's in Visual Studio.
Anyway, thanks for your suggestion.
|||Dear Lukasz,
After all this time I'm still unable to solve this issue. I have installed SP2 on RS2005 and after I press the preview report button the language settings seem to go back to the IE option.
I need ENG UK and have put it in the report properties (also checked in .rdl file for any other language tags that have different values from the one specified above).
I'm feeling desperate and can't seem to find a way out of this... not having a datepicked is not a good option.
Best regards and thanks in advance,
Jo?o Costa
Date parameter format incorrectly interpreted
Hi,
I am using SQL Server 2005 Reporting Services. I have a report with a Startdate and Enddate parameters setup through the report parameters box. The datatype is datetime, so I have the date pickers. My machine regional settings are set to Ireland for datetime format dd/mm/yyyy. The database field being queried is storing the date as dd/mm/yyyy.
I am using SQL Server 2005 Reporting Services. I have a report with a Startdate and Enddate parameters setup through the report parameters box. The datatype is datetime, so I have the date pickers. My machine regional settings are set to Ireland for datetime format dd/mm/yyyy. The database field being queried is storing the date as dd/mm/yyyy.
My problem is when I type in the values Startdate = 01 aug 2005 and Enddate = 31 aug 2005. The report runs correctly, but it refreshes the parameter boxes and if I try to run it again I get the following message
"An error occured during local report processing. The value provided for the report parameter "Enddate" is not valid for its type"
If I choose the same dates from the datepickers I get the same error message. If I choose larger dates for Startdate like 17/08/2005, I get the same message. It is as though the parameter text box is expecting a US formatted date mm/dd/yyyy.
If I enter the values in US format mm/dd/yyyy. The report will run but the date is then changed to Irish format dd/mm/yyyy which if run again will generate the above error. I have tried doing a CONVERT on the parameters in the SQL of the dataset behind the report, but this does not fix the issue, nor does CAST, see below:
" and p.paymentdate >= Convert(datetime, '" & Parameters!Startdate.Value & "', 103) " &
" and p.paymentdate <=Convert(datetime, '" & Parameters!Enddate.Value & "', 103) "
Users will expect to be able to enter dates in their local regional format which is dd/mm/yyyy. Can you suggest a solution for this behaviour?
Thanks and Regards,
RenaHi guys,
I'm having the exact same problem. Any suggestions yet?
My reports worked fine under SQL2000 ... only started this issue once migrated to SQL2005. My Regional settings are English UK but it seems as if the dates passes by the datepickers are being interpreted as US.
Tks ... Richard|||
I have been having the same issues - unless the details in the datetime parameter box are in American format (and numeric dates only) then the failure occurs in Preview mode.
I have managed to get two (semi-workable) fixes - although I cannot stand being forced into this on my machine. Clearly its a bug in Reporting Services 2005 Designer.
Firstly, ensure that the language for the report itself is set to your country (Layout view, Report Properties) It defaults to English US. Changing this appears to fix the issue within the deployed report.
Secondly, change the Regional Settings for your Development Machine. Customize the short date format to "yyyy-MM-dd" (Control Panel / Regional and Language Options / Regional Options / Customize.../ Date) This assists the report Preview
Do not use any option which has textual dates (Jan/Feb, etc) or you will receive an Index was outside the bounds of the array error/failure when you try to preview.
Note, Visual Studio 2005 RS does not implement the configuration change message, and it must be restarted for any new date settings to take effect.
I know that this is an awful way to run your machine (which can also break some apps) but at least you dont need to restart everything each time. ;-)
See if it works for you.
Ian
Hi,
Thanks for the advice Ian. I had exactly the same problem and have since managed to get things working again. This seems to be an issue with the release version of Visual Studion since the Beta 2 version worked fine (in this respect anyway!). However it is still unacceptable to expect users to change their date settings simply for Reporting Services. This could lead to all sorts of confusion/conflicts with different apps. When the service pack comes along this would need to feature. Surely dates are a key component of reports!
Thanks again
John
|||Exactly the same issue, but with a few twists. The report has a start date and end date and both are set to datetime parameters. Run this in preview mode gives an incorrect parameter value when a date of 13th Feb is selected. But if you type in 13 Feb 2006, then the date is accepted OK. Checked all the report settings as UK & deployed expecting to be able to run this on the report server. However the same message appeared on the server. So logged onto the server as myself and altered my international settings to UK (from US) and reran the report. This then worked correctly.
So, I guess the question is how can I ensure that this will work for all users of the report without having to have them logon to the server and set up their profile (which ain't going to happen) ? Do I need to mod the default profile on the server to set this to UK. Ideas anyone ?
|||If this problem is occuring when Previewing reports in SQL Server Business Intelligence Development Studio or in Visual Studio 2005, try applying SP1 of SQL Server 2005. There was a bug where we'd incorrectly change the parameter language when you clicked the View Report button in preview.
Hope that helps,
-Lukasz
|||
Installing SP1 did not resolve this problem for me. Here are the steps to re-create the problem:
1. In the Windows Control Panel, customize your regional settings - set the short date format to MMM d, yyyy (for example).
2. Close Visual Studio 2005 and open it again.
3. Try to preview a report that includes a parameter of type DateTime - it errors with "Index was outside the bounds of the array." It makes no difference whether you use the datepicker control to provide a parameter value, or whether you enter the value manually (in the custom format or in the standard English US format MM/dd/yyyy)
4. To work around this, close Visual Studio, return to the Windows Control Panel and set the short date format to MM/dd/yyyy. Restart Visual Studio and the report previews without any error.
Is this indeed a bug in SSRS2005, or have I made a mistake somewhere in the configuration of RS (or in the design of the report itself)?
Thanks,
Dan
|||Hi Lukasz
I have just applied SP1 to Visual Studio 2005 and have re-tested whether the Business Intelligence Development Studio is now fixed, but unfortunately I have to report that it is not - the View Report button in preview mode still reports UK formatted dates as incorrect formats.
In your message you talk about applying SP1 to SQL Server 2005 - are you meaning apply an SP1 to SQL Server Management Studio (i.e. the client tool on my PC), OR to the SQL Server 2005 which is hosting Reporting Services (which, by the way, is not the same box where most of my data is, because the data server is an older version of SQL Server).
In fact the Data Source connection string is sometimes ORACLE but I still get the same error message about UK dates being incorrectly formatted.
Many thanks,
Mike.
|||In IE, Under tools/Internet Options/Languages make sure you have the correct language selected.
One of the users was complaining that the dates were around in American format (instead of Australia) and this fixed the problem.
|||Yes, Browser settings are set to UK English.
But browser settings shouldn't be part of the equation as the problem's in Visual Studio.
Anyway, thanks for your suggestion.
|||Dear Lukasz,
After all this time I'm still unable to solve this issue. I have installed SP2 on RS2005 and after I press the preview report button the language settings seem to go back to the IE option.
I need ENG UK and have put it in the report properties (also checked in .rdl file for any other language tags that have different values from the one specified above).
I'm feeling desperate and can't seem to find a way out of this... not having a datepicked is not a good option.
Best regards and thanks in advance,
Jo?o Costa
Date parameter format incorrectly interpreted
Hi,
I am using SQL Server 2005 Reporting Services. I have a report with a Startdate and Enddate parameters setup through the report parameters box. The datatype is datetime, so I have the date pickers. My machine regional settings are set to Ireland for datetime format dd/mm/yyyy. The database field being queried is storing the date as dd/mm/yyyy.
I am using SQL Server 2005 Reporting Services. I have a report with a Startdate and Enddate parameters setup through the report parameters box. The datatype is datetime, so I have the date pickers. My machine regional settings are set to Ireland for datetime format dd/mm/yyyy. The database field being queried is storing the date as dd/mm/yyyy.
My problem is when I type in the values Startdate = 01 aug 2005 and Enddate = 31 aug 2005. The report runs correctly, but it refreshes the parameter boxes and if I try to run it again I get the following message
"An error occured during local report processing. The value provided for the report parameter "Enddate" is not valid for its type"
If I choose the same dates from the datepickers I get the same error message. If I choose larger dates for Startdate like 17/08/2005, I get the same message. It is as though the parameter text box is expecting a US formatted date mm/dd/yyyy.
If I enter the values in US format mm/dd/yyyy. The report will run but the date is then changed to Irish format dd/mm/yyyy which if run again will generate the above error. I have tried doing a CONVERT on the parameters in the SQL of the dataset behind the report, but this does not fix the issue, nor does CAST, see below:
" and p.paymentdate >= Convert(datetime, '" & Parameters!Startdate.Value & "', 103) " &
" and p.paymentdate <=Convert(datetime, '" & Parameters!Enddate.Value & "', 103) "
Users will expect to be able to enter dates in their local regional format which is dd/mm/yyyy. Can you suggest a solution for this behaviour?
Thanks and Regards,
RenaHi guys,
I'm having the exact same problem. Any suggestions yet?
My reports worked fine under SQL2000 ... only started this issue once migrated to SQL2005. My Regional settings are English UK but it seems as if the dates passes by the datepickers are being interpreted as US.
Tks ... Richard|||
I have been having the same issues - unless the details in the datetime parameter box are in American format (and numeric dates only) then the failure occurs in Preview mode.
I have managed to get two (semi-workable) fixes - although I cannot stand being forced into this on my machine. Clearly its a bug in Reporting Services 2005 Designer.
Firstly, ensure that the language for the report itself is set to your country (Layout view, Report Properties) It defaults to English US. Changing this appears to fix the issue within the deployed report.
Secondly, change the Regional Settings for your Development Machine. Customize the short date format to "yyyy-MM-dd" (Control Panel / Regional and Language Options / Regional Options / Customize.../ Date) This assists the report Preview
Do not use any option which has textual dates (Jan/Feb, etc) or you will receive an Index was outside the bounds of the array error/failure when you try to preview.
Note, Visual Studio 2005 RS does not implement the configuration change message, and it must be restarted for any new date settings to take effect.
I know that this is an awful way to run your machine (which can also break some apps) but at least you dont need to restart everything each time. ;-)
See if it works for you.
Ian
Hi,
Thanks for the advice Ian. I had exactly the same problem and have since managed to get things working again. This seems to be an issue with the release version of Visual Studion since the Beta 2 version worked fine (in this respect anyway!). However it is still unacceptable to expect users to change their date settings simply for Reporting Services. This could lead to all sorts of confusion/conflicts with different apps. When the service pack comes along this would need to feature. Surely dates are a key component of reports!
Thanks again
John
|||Exactly the same issue, but with a few twists. The report has a start date and end date and both are set to datetime parameters. Run this in preview mode gives an incorrect parameter value when a date of 13th Feb is selected. But if you type in 13 Feb 2006, then the date is accepted OK. Checked all the report settings as UK & deployed expecting to be able to run this on the report server. However the same message appeared on the server. So logged onto the server as myself and altered my international settings to UK (from US) and reran the report. This then worked correctly.
So, I guess the question is how can I ensure that this will work for all users of the report without having to have them logon to the server and set up their profile (which ain't going to happen) ? Do I need to mod the default profile on the server to set this to UK. Ideas anyone ?
|||If this problem is occuring when Previewing reports in SQL Server Business Intelligence Development Studio or in Visual Studio 2005, try applying SP1 of SQL Server 2005. There was a bug where we'd incorrectly change the parameter language when you clicked the View Report button in preview.
Hope that helps,
-Lukasz
|||
Installing SP1 did not resolve this problem for me. Here are the steps to re-create the problem:
1. In the Windows Control Panel, customize your regional settings - set the short date format to MMM d, yyyy (for example).
2. Close Visual Studio 2005 and open it again.
3. Try to preview a report that includes a parameter of type DateTime - it errors with "Index was outside the bounds of the array." It makes no difference whether you use the datepicker control to provide a parameter value, or whether you enter the value manually (in the custom format or in the standard English US format MM/dd/yyyy)
4. To work around this, close Visual Studio, return to the Windows Control Panel and set the short date format to MM/dd/yyyy. Restart Visual Studio and the report previews without any error.
Is this indeed a bug in SSRS2005, or have I made a mistake somewhere in the configuration of RS (or in the design of the report itself)?
Thanks,
Dan
|||Hi Lukasz
I have just applied SP1 to Visual Studio 2005 and have re-tested whether the Business Intelligence Development Studio is now fixed, but unfortunately I have to report that it is not - the View Report button in preview mode still reports UK formatted dates as incorrect formats.
In your message you talk about applying SP1 to SQL Server 2005 - are you meaning apply an SP1 to SQL Server Management Studio (i.e. the client tool on my PC), OR to the SQL Server 2005 which is hosting Reporting Services (which, by the way, is not the same box where most of my data is, because the data server is an older version of SQL Server).
In fact the Data Source connection string is sometimes ORACLE but I still get the same error message about UK dates being incorrectly formatted.
Many thanks,
Mike.
|||In IE, Under tools/Internet Options/Languages make sure you have the correct language selected.
One of the users was complaining that the dates were around in American format (instead of Australia) and this fixed the problem.
|||Yes, Browser settings are set to UK English.
But browser settings shouldn't be part of the equation as the problem's in Visual Studio.
Anyway, thanks for your suggestion.
|||Dear Lukasz,
After all this time I'm still unable to solve this issue. I have installed SP2 on RS2005 and after I press the preview report button the language settings seem to go back to the IE option.
I need ENG UK and have put it in the report properties (also checked in .rdl file for any other language tags that have different values from the one specified above).
I'm feeling desperate and can't seem to find a way out of this... not having a datepicked is not a good option.
Best regards and thanks in advance,
Jo?o Costa
Date parameter format incorrectly interpreted
Hi,
I am using SQL Server 2005 Reporting Services. I have a report with a Startdate and Enddate parameters setup through the report parameters box. The datatype is datetime, so I have the date pickers. My machine regional settings are set to Ireland for datetime format dd/mm/yyyy. The database field being queried is storing the date as dd/mm/yyyy.
I am using SQL Server 2005 Reporting Services. I have a report with a Startdate and Enddate parameters setup through the report parameters box. The datatype is datetime, so I have the date pickers. My machine regional settings are set to Ireland for datetime format dd/mm/yyyy. The database field being queried is storing the date as dd/mm/yyyy.
My problem is when I type in the values Startdate = 01 aug 2005 and Enddate = 31 aug 2005. The report runs correctly, but it refreshes the parameter boxes and if I try to run it again I get the following message
"An error occured during local report processing. The value provided for the report parameter "Enddate" is not valid for its type"
If I choose the same dates from the datepickers I get the same error message. If I choose larger dates for Startdate like 17/08/2005, I get the same message. It is as though the parameter text box is expecting a US formatted date mm/dd/yyyy.
If I enter the values in US format mm/dd/yyyy. The report will run but the date is then changed to Irish format dd/mm/yyyy which if run again will generate the above error. I have tried doing a CONVERT on the parameters in the SQL of the dataset behind the report, but this does not fix the issue, nor does CAST, see below:
" and p.paymentdate >= Convert(datetime, '" & Parameters!Startdate.Value & "', 103) " &
" and p.paymentdate <=Convert(datetime, '" & Parameters!Enddate.Value & "', 103) "
Users will expect to be able to enter dates in their local regional format which is dd/mm/yyyy. Can you suggest a solution for this behaviour?
Thanks and Regards,
RenaHi guys,
I'm having the exact same problem. Any suggestions yet?
My reports worked fine under SQL2000 ... only started this issue once migrated to SQL2005. My Regional settings are English UK but it seems as if the dates passes by the datepickers are being interpreted as US.
Tks ... Richard|||
I have been having the same issues - unless the details in the datetime parameter box are in American format (and numeric dates only) then the failure occurs in Preview mode.
I have managed to get two (semi-workable) fixes - although I cannot stand being forced into this on my machine. Clearly its a bug in Reporting Services 2005 Designer.
Firstly, ensure that the language for the report itself is set to your country (Layout view, Report Properties) It defaults to English US. Changing this appears to fix the issue within the deployed report.
Secondly, change the Regional Settings for your Development Machine. Customize the short date format to "yyyy-MM-dd" (Control Panel / Regional and Language Options / Regional Options / Customize.../ Date) This assists the report Preview
Do not use any option which has textual dates (Jan/Feb, etc) or you will receive an Index was outside the bounds of the array error/failure when you try to preview.
Note, Visual Studio 2005 RS does not implement the configuration change message, and it must be restarted for any new date settings to take effect.
I know that this is an awful way to run your machine (which can also break some apps) but at least you dont need to restart everything each time. ;-)
See if it works for you.
Ian
Hi,
Thanks for the advice Ian. I had exactly the same problem and have since managed to get things working again. This seems to be an issue with the release version of Visual Studion since the Beta 2 version worked fine (in this respect anyway!). However it is still unacceptable to expect users to change their date settings simply for Reporting Services. This could lead to all sorts of confusion/conflicts with different apps. When the service pack comes along this would need to feature. Surely dates are a key component of reports!
Thanks again
John
|||Exactly the same issue, but with a few twists. The report has a start date and end date and both are set to datetime parameters. Run this in preview mode gives an incorrect parameter value when a date of 13th Feb is selected. But if you type in 13 Feb 2006, then the date is accepted OK. Checked all the report settings as UK & deployed expecting to be able to run this on the report server. However the same message appeared on the server. So logged onto the server as myself and altered my international settings to UK (from US) and reran the report. This then worked correctly.
So, I guess the question is how can I ensure that this will work for all users of the report without having to have them logon to the server and set up their profile (which ain't going to happen) ? Do I need to mod the default profile on the server to set this to UK. Ideas anyone ?
|||If this problem is occuring when Previewing reports in SQL Server Business Intelligence Development Studio or in Visual Studio 2005, try applying SP1 of SQL Server 2005. There was a bug where we'd incorrectly change the parameter language when you clicked the View Report button in preview.
Hope that helps,
-Lukasz
|||
Installing SP1 did not resolve this problem for me. Here are the steps to re-create the problem:
1. In the Windows Control Panel, customize your regional settings - set the short date format to MMM d, yyyy (for example).
2. Close Visual Studio 2005 and open it again.
3. Try to preview a report that includes a parameter of type DateTime - it errors with "Index was outside the bounds of the array." It makes no difference whether you use the datepicker control to provide a parameter value, or whether you enter the value manually (in the custom format or in the standard English US format MM/dd/yyyy)
4. To work around this, close Visual Studio, return to the Windows Control Panel and set the short date format to MM/dd/yyyy. Restart Visual Studio and the report previews without any error.
Is this indeed a bug in SSRS2005, or have I made a mistake somewhere in the configuration of RS (or in the design of the report itself)?
Thanks,
Dan
|||Hi Lukasz
I have just applied SP1 to Visual Studio 2005 and have re-tested whether the Business Intelligence Development Studio is now fixed, but unfortunately I have to report that it is not - the View Report button in preview mode still reports UK formatted dates as incorrect formats.
In your message you talk about applying SP1 to SQL Server 2005 - are you meaning apply an SP1 to SQL Server Management Studio (i.e. the client tool on my PC), OR to the SQL Server 2005 which is hosting Reporting Services (which, by the way, is not the same box where most of my data is, because the data server is an older version of SQL Server).
In fact the Data Source connection string is sometimes ORACLE but I still get the same error message about UK dates being incorrectly formatted.
Many thanks,
Mike.
|||In IE, Under tools/Internet Options/Languages make sure you have the correct language selected.
One of the users was complaining that the dates were around in American format (instead of Australia) and this fixed the problem.
|||Yes, Browser settings are set to UK English.
But browser settings shouldn't be part of the equation as the problem's in Visual Studio.
Anyway, thanks for your suggestion.
|||Dear Lukasz,
After all this time I'm still unable to solve this issue. I have installed SP2 on RS2005 and after I press the preview report button the language settings seem to go back to the IE option.
I need ENG UK and have put it in the report properties (also checked in .rdl file for any other language tags that have different values from the one specified above).
I'm feeling desperate and can't seem to find a way out of this... not having a datepicked is not a good option.
Best regards and thanks in advance,
Jo?o Costa
Date Parameter ERROR
My RS has two date parameters setup as a date data type. If the user was to
key in 01102004 instead of 01/10/2004 they get this error;
Reporting Services Error
"The value provided for the report parameter 'P_FromDate' is not valid for
its type. (rsReportParameterTypeMismatch)"
How can set that parameter up so I can enter in either of the above dates
formats? I need to beable to do error handling on these two fields.Have the parameter be of string type and then base your query on a dynamic
sql (an expression). The expression can call code that parses the date. But
if it is a bad date it gets tricky to give an error message. To have more
control of the parameters you would need to have your own asp page that is
used to get the parameters and then use either URL integration or web
services to integrate with RS.
--
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"doug" <doug@.discussions.microsoft.com> wrote in message
news:44D70CDD-0EF9-4BA1-82F0-ADFD748E45D8@.microsoft.com...
> My RS has two date parameters setup as a date data type. If the user was
to
> key in 01102004 instead of 01/10/2004 they get this error;
> Reporting Services Error
> "The value provided for the report parameter 'P_FromDate' is not valid for
> its type. (rsReportParameterTypeMismatch)"
> How can set that parameter up so I can enter in either of the above dates
> formats? I need to beable to do error handling on these two fields.
key in 01102004 instead of 01/10/2004 they get this error;
Reporting Services Error
"The value provided for the report parameter 'P_FromDate' is not valid for
its type. (rsReportParameterTypeMismatch)"
How can set that parameter up so I can enter in either of the above dates
formats? I need to beable to do error handling on these two fields.Have the parameter be of string type and then base your query on a dynamic
sql (an expression). The expression can call code that parses the date. But
if it is a bad date it gets tricky to give an error message. To have more
control of the parameters you would need to have your own asp page that is
used to get the parameters and then use either URL integration or web
services to integrate with RS.
--
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"doug" <doug@.discussions.microsoft.com> wrote in message
news:44D70CDD-0EF9-4BA1-82F0-ADFD748E45D8@.microsoft.com...
> My RS has two date parameters setup as a date data type. If the user was
to
> key in 01102004 instead of 01/10/2004 they get this error;
> Reporting Services Error
> "The value provided for the report parameter 'P_FromDate' is not valid for
> its type. (rsReportParameterTypeMismatch)"
> How can set that parameter up so I can enter in either of the above dates
> formats? I need to beable to do error handling on these two fields.
Date Parameter and text box question
I have a report that runs transcripts for people. I have a date parameter setup along with last and first name. The date parameter field is used to specify what year (ex. 1/1/2006 - 12/31/2006) to pull the information for.
Now..I have a text box at the bottom (Footer) of my report that says something to the affect of "Official transcripts for 2006". There will be times when I have to run the report using a different year other then "2006" in the date parameter field. How do I have the text at the bottom change to reflect the year I'm reporting on? If I use dates say in 2004 I need the text at the bottom to reflect that year and not 2006. Can this be accomplished?
Thx,
Bill
Sounds like you need to parse the parameter value (Parameters!ParameterName.Value) to get to the year portion. You can add a code-behind VB.NET function to help with this as demonstrated at the beginning of this article.|||="Official transcripts for 2006" & Parameters!YourDateParameterName.Value
Try this. . .
Sunday, March 11, 2012
Date frustration
I screwed up when building my application, now I need to try to fix it...
Initially I setup my dates in a varchar field, now I need them in a datetime
field...
I have the following query that is acting funny,
UPDATE tblEmpPersonalInfo SET
SepDate = EmpSWVTCSepDate
This query will not successfully run, giving me this error,
The conversion of a char data type to a datetime data type resulted in an
out-of-range datetime value.
The statement has been terminated.
But, if I edit the query like this,
UPDATE tblEmpPersonalInfo SET
SepDate = EmpSWVTCSepDate
WHERE EmpID = 'aa381234'
then the query will work fine... I don't want to have to run the query for
every employee in the database, so how can I do this?
Thanks,
DrewUse ISDATE() function to identify if the data in the column are truly date
values. Check out SQL Server Books Online for more details on this function.
Anith|||Hi
Check that all the values in the EmpSWVTCSepDate column are actually valid
dates.
SELECT * FROM tblEmpPersonalInfo
WHERE ISDATE(EmpSWVTCSepDate) = 0
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Drew" <drew.laing@.NOswvtc.dmhmrsas.virginia.SPMgov> wrote in message
news:e6aUZElAFHA.2792@.TK2MSFTNGP15.phx.gbl...
> I screwed up when building my application, now I need to try to fix it...
> Initially I setup my dates in a varchar field, now I need them in a
datetime
> field...
> I have the following query that is acting funny,
> UPDATE tblEmpPersonalInfo SET
> SepDate = EmpSWVTCSepDate
> This query will not successfully run, giving me this error,
> The conversion of a char data type to a datetime data type resulted in an
> out-of-range datetime value.
> The statement has been terminated.
> But, if I edit the query like this,
> UPDATE tblEmpPersonalInfo SET
> SepDate = EmpSWVTCSepDate
> WHERE EmpID = 'aa381234'
> then the query will work fine... I don't want to have to run the query for
> every employee in the database, so how can I do this?
> Thanks,
> Drew
>|||Thanks... There was 1 invalid date in there, 10-10--2003
Drew
"Mike Epprecht (SQL MVP)" <mike@.epprecht.net> wrote in message
news:u4k2MPlAFHA.2604@.TK2MSFTNGP10.phx.gbl...
> Hi
> Check that all the values in the EmpSWVTCSepDate column are actually valid
> dates.
> SELECT * FROM tblEmpPersonalInfo
> WHERE ISDATE(EmpSWVTCSepDate) = 0
> Regards
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
> IM: mike@.epprecht.net
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
> "Drew" <drew.laing@.NOswvtc.dmhmrsas.virginia.SPMgov> wrote in message
> news:e6aUZElAFHA.2792@.TK2MSFTNGP15.phx.gbl...
> datetime
>|||Drew,
1 - Update those without problem
UPDATE tblEmpPersonalInfo SET
SepDate = EmpSWVTCSepDate
where isdate(EmpSWVTCSepDate) = 1
2 - Identify those with problem, correct the problem and update them again.
select * from tblEmpPersonalInfo
where isdate(EmpSWVTCSepDate) = 0
-- fix the problem
...
UPDATE tblEmpPersonalInfo SET
SepDate = EmpSWVTCSepDate
where isdate(EmpSWVTCSepDate) = 1 and SepDate is null
AMB
"Drew" wrote:
> I screwed up when building my application, now I need to try to fix it...
> Initially I setup my dates in a varchar field, now I need them in a dateti
me
> field...
> I have the following query that is acting funny,
> UPDATE tblEmpPersonalInfo SET
> SepDate = EmpSWVTCSepDate
> This query will not successfully run, giving me this error,
> The conversion of a char data type to a datetime data type resulted in an
> out-of-range datetime value.
> The statement has been terminated.
> But, if I edit the query like this,
> UPDATE tblEmpPersonalInfo SET
> SepDate = EmpSWVTCSepDate
> WHERE EmpID = 'aa381234'
> then the query will work fine... I don't want to have to run the query for
> every employee in the database, so how can I do this?
> Thanks,
> Drew
>
>|||Too bad we cannot set some sort of flashing attribute on this message :)
Glad you found your problem, and even more important, glad you are cutting
future problems off at the knee caps. The way a value displays can be dealt
with at runtime, the most important thing is that the value is true!
----
Louis Davidson - drsql@.hotmail.com
SQL Server MVP
Compass Technology Management - www.compass.net
Pro SQL Server 2000 Database Design -
http://www.apress.com/book/bookDisplay.html?bID=266
Note: Please reply to the newsgroups only unless you are interested in
consulting services. All other replies may be ignored :)
"Drew" <drew.laing@.NOswvtc.dmhmrsas.virginia.SPMgov> wrote in message
news:ewv0xWlAFHA.1388@.TK2MSFTNGP09.phx.gbl...
> Thanks... There was 1 invalid date in there, 10-10--2003
> Drew
> "Mike Epprecht (SQL MVP)" <mike@.epprecht.net> wrote in message
> news:u4k2MPlAFHA.2604@.TK2MSFTNGP10.phx.gbl...
>
Initially I setup my dates in a varchar field, now I need them in a datetime
field...
I have the following query that is acting funny,
UPDATE tblEmpPersonalInfo SET
SepDate = EmpSWVTCSepDate
This query will not successfully run, giving me this error,
The conversion of a char data type to a datetime data type resulted in an
out-of-range datetime value.
The statement has been terminated.
But, if I edit the query like this,
UPDATE tblEmpPersonalInfo SET
SepDate = EmpSWVTCSepDate
WHERE EmpID = 'aa381234'
then the query will work fine... I don't want to have to run the query for
every employee in the database, so how can I do this?
Thanks,
DrewUse ISDATE() function to identify if the data in the column are truly date
values. Check out SQL Server Books Online for more details on this function.
Anith|||Hi
Check that all the values in the EmpSWVTCSepDate column are actually valid
dates.
SELECT * FROM tblEmpPersonalInfo
WHERE ISDATE(EmpSWVTCSepDate) = 0
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Drew" <drew.laing@.NOswvtc.dmhmrsas.virginia.SPMgov> wrote in message
news:e6aUZElAFHA.2792@.TK2MSFTNGP15.phx.gbl...
> I screwed up when building my application, now I need to try to fix it...
> Initially I setup my dates in a varchar field, now I need them in a
datetime
> field...
> I have the following query that is acting funny,
> UPDATE tblEmpPersonalInfo SET
> SepDate = EmpSWVTCSepDate
> This query will not successfully run, giving me this error,
> The conversion of a char data type to a datetime data type resulted in an
> out-of-range datetime value.
> The statement has been terminated.
> But, if I edit the query like this,
> UPDATE tblEmpPersonalInfo SET
> SepDate = EmpSWVTCSepDate
> WHERE EmpID = 'aa381234'
> then the query will work fine... I don't want to have to run the query for
> every employee in the database, so how can I do this?
> Thanks,
> Drew
>|||Thanks... There was 1 invalid date in there, 10-10--2003
Drew
"Mike Epprecht (SQL MVP)" <mike@.epprecht.net> wrote in message
news:u4k2MPlAFHA.2604@.TK2MSFTNGP10.phx.gbl...
> Hi
> Check that all the values in the EmpSWVTCSepDate column are actually valid
> dates.
> SELECT * FROM tblEmpPersonalInfo
> WHERE ISDATE(EmpSWVTCSepDate) = 0
> Regards
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
> IM: mike@.epprecht.net
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
> "Drew" <drew.laing@.NOswvtc.dmhmrsas.virginia.SPMgov> wrote in message
> news:e6aUZElAFHA.2792@.TK2MSFTNGP15.phx.gbl...
> datetime
>|||Drew,
1 - Update those without problem
UPDATE tblEmpPersonalInfo SET
SepDate = EmpSWVTCSepDate
where isdate(EmpSWVTCSepDate) = 1
2 - Identify those with problem, correct the problem and update them again.
select * from tblEmpPersonalInfo
where isdate(EmpSWVTCSepDate) = 0
-- fix the problem
...
UPDATE tblEmpPersonalInfo SET
SepDate = EmpSWVTCSepDate
where isdate(EmpSWVTCSepDate) = 1 and SepDate is null
AMB
"Drew" wrote:
> I screwed up when building my application, now I need to try to fix it...
> Initially I setup my dates in a varchar field, now I need them in a dateti
me
> field...
> I have the following query that is acting funny,
> UPDATE tblEmpPersonalInfo SET
> SepDate = EmpSWVTCSepDate
> This query will not successfully run, giving me this error,
> The conversion of a char data type to a datetime data type resulted in an
> out-of-range datetime value.
> The statement has been terminated.
> But, if I edit the query like this,
> UPDATE tblEmpPersonalInfo SET
> SepDate = EmpSWVTCSepDate
> WHERE EmpID = 'aa381234'
> then the query will work fine... I don't want to have to run the query for
> every employee in the database, so how can I do this?
> Thanks,
> Drew
>
>|||Too bad we cannot set some sort of flashing attribute on this message :)
Glad you found your problem, and even more important, glad you are cutting
future problems off at the knee caps. The way a value displays can be dealt
with at runtime, the most important thing is that the value is true!
----
Louis Davidson - drsql@.hotmail.com
SQL Server MVP
Compass Technology Management - www.compass.net
Pro SQL Server 2000 Database Design -
http://www.apress.com/book/bookDisplay.html?bID=266
Note: Please reply to the newsgroups only unless you are interested in
consulting services. All other replies may be ignored :)
"Drew" <drew.laing@.NOswvtc.dmhmrsas.virginia.SPMgov> wrote in message
news:ewv0xWlAFHA.1388@.TK2MSFTNGP09.phx.gbl...
> Thanks... There was 1 invalid date in there, 10-10--2003
> Drew
> "Mike Epprecht (SQL MVP)" <mike@.epprecht.net> wrote in message
> news:u4k2MPlAFHA.2604@.TK2MSFTNGP10.phx.gbl...
>
Subscribe to:
Posts (Atom)