Hi,
I am having a problem with the DateTime parameter option in SQL 2005 reporting services.
Note that Server, workstation and database are set to British/English (UK) date/time localisation (dd/MM/yyyy) for example 25th December 2005=25/12/2005.
I create a simple query with a parameter on a datetime column and set the report parameter to Data type: DateTime in the report parameters dialog.
On preview or after deployment the following behaviour is observed.
Selecting 1st February 2006 in the datetime picker control populates the date parameter entry field with
01/02/2006 = 1st February 2006 (the correct UK format as expected)
When View Report is clicked the datetime parameter field magically changes to
02/01/2006
So forcing the datetime values to US format MM/dd/yyyy, the report runs OK but does not return data restricted to the required date.
If we select 25th February 2006 the datetime parameter field populates with
25/02/2006 = 25th February 2006 (the correct UK format as expected)
When View Report is clicked the datetime parameter changes to 02/01/2006 and an error is displayed
An error occurred during local report processing.
The value provided for the report parameter X is not valid for its type.
Is there a solution to this
Thanks,
Neil
UK Datetime parameter problem SQL 2005 RS
Mirsad
Further testing today shows the problem now only manifests itself in the preview tab of the development environment as the freshly deployed report is now behaving as expected. Strange- maybe there was some sort of caching going on with the deployed report showing us an older version
Regarding the problem continuing within the IDE, perhaps the international settings within the development environment aren't taking effect in the preview tab
Note that Tools>Options>International Settings are set to English
Thanks,
Carlillos
Similar here. Except with an extra twist!
Two date parameters, start & end (datetime), reports work fine in both VS2003 and deployed to the web server. However, I have a hyperlink from report A to run report B, passing date parameters to report B. The date format is switched in the process from UK to US and I either get the error above (type mismatch) or the selection period is wrong (day/month transposed).
It's not a native SQL thing, the raw sql is fine in Query Analyzer.
It's not the individual reports, they both work independantly in both the IDE and deployed.
I've checked all regional settings and they are set to English-UK, except VS2003 which is English-US (I can't change it to same as Windows, there is no option to do so). However, each individual report IS set to English-UK. Don't really want to re-install VS2003 but if it's the only way... Does anyone know for sure I would have thought that changing the report to English-UK is enough because, as I say, each report runs fine when run directly in the browser or in VS2003.
I've even tried forcing the order of the components of the date to the order in a UK date using DAY(Parameters!Start_date.Value), MONTH, YEAR functions but still no joy. Combining with Dateserial also makes no difference.
Seems that someone has left English-US hardcoded somewhere in RS!!! Or is there a true reason Answers anyone please Thanx.
Old_newb
Hi,
The problem persists for us in the Visual Studio IDE preview tab but as the deployed reports are OK it's not a major problem for us.
As I said in an earlier post I do not think the international settings within the development environment are taking effect in the preview tab- hence the problem when previewing reports in the IDE.
Daya Anand
Hello Neil,
I'm trying to repro this issue, but am not seeing it. For me, the date picker always behaves according to the browser localization, and the date format never "magically changes".
Do you have any Language tags in the RDL for which you see the problem
What is the most favored browser localization (Tools >> Internet Options >> Languages)
Sergey_12345
Hi
we are having the same problem with the date picker when used to select for parameters for report in SQL Server Reporting Services using Visual Studio 2005.
The date picker shows the current date in UK format but when you select a date with the day greater than 12 the same error occurs as it has been converted into a US date.
We have tried changing the Globalization settings for culturein the web.config file to en-GB ie culture="en-GB".
However this didn't seem to work.
Regards
Arnold
HelloHello
When report server interprets the date parameter, it is interpreted using the report language. The default report language (added by report designer) is based on the language of VS you installed. Since the English version of VS is en-us when using an English report designer. In the RDL for the report you'll see a property called <Language>en-US</Language>. It is available at the following path Report\Language.
If you don't provide a value, the value assumed is the same as the report server.
If you want your reports to operate on UK dates, always, then you should set the value of the Language element to en-UK. If you want your reports to always be determined by the end-user context, then set it to User!Language. However this can cause problems when resolving currency symbols (same value but pounds vs dollars makes a big difference in interpretation ;-)).
If when you use a particular date, you want to use a particular culture (say the user's input language) you can use the .Net framework functions for handling dates. See the System.Globalization namespace. You can use these in report expressions.
http://msdn2.microsoft.com/en-us/library/system.globalization.datetimeformatinfo(VS.80).aspx
Hope that helps,
-Lukasz
Wojtek P
On the server side it is possible that your Browser cached the output of the report. In IE you can refresh using Ctrl+F5, which by passes the cache.
In RD - you need to set your VS to 'Same as Windows' under Tools --> Options --> International Settings. Then in the Regional And Language Settings control panel, change you locale to your desired one. Now when you preview the report, it should just work for you.
-Lukasz
Gerald_Schinagl
I'm having this problem too. Yesterday everything ran fine but then the month and day were the same digit, 6! So now I have to run reports for 6th June to make sure they run properly.
Couldn't see this bug mentioned in the SS2005 SP1 release notes.
Anr
Hi Mike,
Language setting is en-GB in the rdl so looks OK.
I'm using IE 6 and the language setting is English(United Kingdom) [en-gb]
When you are trying to replicate make sure you got you're whole environment - server, worksation and database with English GB settings.
I've been googling and I've found someone with the same problem on another forum
http://forums.asp.net/1190731/ShowPost.aspx
Thanks for your help,
Neil
vibha g panse
Just in case it may be relevant the platform is Server 2003 Standard x64 Edition and database is also Standard Edition (x64).
Rob Hammond
PS I could try passing the date as a string but then of course when I run the receiving report standalone I won't have the date picker... and then there's all the validation of what the user types etc etc But why should I have to do this anyway C'mon, let's have a fix for this obvious BUG please!
Christan
Hmmm ...
I can't get the parameter value to flip to the wrong format. I'll keep working on this, and will post if I find anything.
Jym
Here's the code:
< xml version="1.0" encoding="utf-8" >
<Report xmlns="http://schemas.microsoft.com/sqlserver/reporting/2005/01/reportdefinition" xmlns:rd="http://schemas.microsoft.com/SQLServer/reporting/reportdesigner">
<DataSources>
<DataSource Name="EDI11">
<DataSourceReference>EDI1</DataSourceReference>
<rd:DataSourceID>61785b91-6af2-4535-aa33-e08e947808d8</rd:DataSourceID>
</DataSource>
</DataSources>
<BottomMargin>2.5cm</BottomMargin>
<RightMargin>2.5cm</RightMargin>
<PageWidth>21cm</PageWidth>
<ReportParameters>
<ReportParameter Name="LAST_DATE">
<DataType>DateTime</DataType>
<Prompt>LAST_DATE</Prompt>
</ReportParameter>
</ReportParameters>
<rd:DrawGrid>true</rd:DrawGrid>
<InteractiveWidth>8.5in</InteractiveWidth>
<rd:GridSpacing>0.25cm</rd:GridSpacing>
<rd:SnapToGrid>true</rd:SnapToGrid>
<Body>
<ColumnSpacing>1cm</ColumnSpacing>
<ReportItems>
<Table Name="table1">
<Footer>
<TableRows>
<TableRow>
<TableCells>
<TableCell>
<ReportItems>
<Textbox Name="textbox8">
<rd:DefaultName>textbox8</rd:DefaultName>
<ZIndex>9</ZIndex>
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value />
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox9">
<rd:DefaultName>textbox9</rd:DefaultName>
<ZIndex>8</ZIndex>
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value />
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox10">
<rd:DefaultName>textbox10</rd:DefaultName>
<ZIndex>7</ZIndex>
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value />
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox7">
<rd:DefaultName>textbox7</rd:DefaultName>
<ZIndex>6</ZIndex>
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value />
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox13">
<rd:DefaultName>textbox13</rd:DefaultName>
<ZIndex>5</ZIndex>
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value />
</Textbox>
</ReportItems>
</TableCell>
</TableCells>
<Height>0.58201cm</Height>
</TableRow>
</TableRows>
</Footer>
<Left>0.25cm</Left>
<DataSetName>ftpstatus_ds</DataSetName>
<Top>1.25cm</Top>
<ZIndex>1</ZIndex>
<Width>26.5cm</Width>
<Details>
<TableRows>
<TableRow>
<TableCells>
<TableCell>
<ReportItems>
<Textbox Name="EDI_AC_CODE">
<rd:DefaultName>EDI_AC_CODE</rd:DefaultName>
<ZIndex>4</ZIndex>
<Style>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value>=Fields!EDI_AC_CODE.Value</Value>
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="FTP_HOST">
<rd:DefaultName>FTP_HOST</rd:DefaultName>
<ZIndex>3</ZIndex>
<Style>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value>=Fields!FTP_HOST.Value</Value>
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="LAST_ERR_MSG">
<rd:DefaultName>LAST_ERR_MSG</rd:DefaultName>
<ZIndex>2</ZIndex>
<Style>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value>=Fields!LAST_ERR_MSG.Value</Value>
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="LAST_ERR_DATE">
<rd:DefaultName>LAST_ERR_DATE</rd:DefaultName>
<ZIndex>1</ZIndex>
<Style>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<Format>dd/MM/yyyy HH:mm</Format>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value>=Fields!LAST_ERR_DATE.Value</Value>
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="ERR_LOCKED">
<rd:DefaultName>ERR_LOCKED</rd:DefaultName>
<Style>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
<TextAlign>Right</TextAlign>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value>=Fields!ERR_LOCKED.Value</Value>
</Textbox>
</ReportItems>
</TableCell>
</TableCells>
<Height>0.58201cm</Height>
</TableRow>
</TableRows>
</Details>
<Header>
<TableRows>
<TableRow>
<TableCells>
<TableCell>
<ReportItems>
<Textbox Name="textbox2">
<rd:DefaultName>textbox2</rd:DefaultName>
<ZIndex>14</ZIndex>
<Style>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<BackgroundColor>LightGreen</BackgroundColor>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value>Code</Value>
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox3">
<rd:DefaultName>textbox3</rd:DefaultName>
<ZIndex>13</ZIndex>
<Style>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<BackgroundColor>LightGreen</BackgroundColor>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value>Host</Value>
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox4">
<rd:DefaultName>textbox4</rd:DefaultName>
<ZIndex>12</ZIndex>
<Style>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<BackgroundColor>LightGreen</BackgroundColor>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value>Last Error</Value>
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox5">
<rd:DefaultName>textbox5</rd:DefaultName>
<ZIndex>11</ZIndex>
<Style>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<BackgroundColor>LightGreen</BackgroundColor>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value>Occured</Value>
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox11">
<rd:DefaultName>textbox11</rd:DefaultName>
<ZIndex>10</ZIndex>
<Style>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
<TextAlign>Right</TextAlign>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<BackgroundColor>LightGreen</BackgroundColor>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value>Lock Flag</Value>
</Textbox>
</ReportItems>
</TableCell>
</TableCells>
<Height>0.58201cm</Height>
</TableRow>
</TableRows>
</Header>
<TableColumns>
<TableColumn>
<Width>3.25cm</Width>
</TableColumn>
<TableColumn>
<Width>6.25cm</Width>
</TableColumn>
<TableColumn>
<Width>11.25cm</Width>
</TableColumn>
<TableColumn>
<Width>3.25cm</Width>
</TableColumn>
<TableColumn>
<Width>2.5cm</Width>
</TableColumn>
</TableColumns>
<Height>1.74603cm</Height>
</Table>
<Textbox Name="textbox1">
<rd:DefaultName>textbox1</rd:DefaultName>
<Width>12.69841cm</Width>
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<FontFamily>Times New Roman</FontFamily>
<FontSize>18pt</FontSize>
<Color>#365838</Color>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Height>0.8381cm</Height>
<Value>FTP Status</Value>
</Textbox>
</ReportItems>
<Height>3.75cm</Height>
</Body>
<rd:ReportID>cb3e8c2e-f280-4506-a458-0e85cd1cd107</rd:ReportID>
<LeftMargin>2.5cm</LeftMargin>
<DataSets>
<DataSet Name="ftpstatus_ds">
<Query>
<rd:UseGenericDesigner>true</rd:UseGenericDesigner>
<CommandText>SELECT EDI_AC_CODE, FTP_HOST, LAST_ERR_MSG, LAST_ERR_DATE, ERR_LOCKED
from tblEDI_TPCFG_IO
WHERE EDI_STATUS_FLAG='Y' AND FTP_ACTIVE_FLAG='Y'
AND LAST_ERR_DATE>=(@LAST_DATE)</CommandText>
<QueryParameters>
<QueryParameter Name="@LAST_DATE">
<Value>=Parameters!LAST_DATE.Value</Value>
</QueryParameter>
</QueryParameters>
<DataSourceName>EDI11</DataSourceName>
</Query>
<Fields>
<Field Name="EDI_AC_CODE">
<rd:TypeName>System.String</rd:TypeName>
<DataField>EDI_AC_CODE</DataField>
</Field>
<Field Name="FTP_HOST">
<rd:TypeName>System.String</rd:TypeName>
<DataField>FTP_HOST</DataField>
</Field>
<Field Name="LAST_ERR_MSG">
<rd:TypeName>System.String</rd:TypeName>
<DataField>LAST_ERR_MSG</DataField>
</Field>
<Field Name="LAST_ERR_DATE">
<rd:TypeName>System.String</rd:TypeName>
<DataField>LAST_ERR_DATE</DataField>
</Field>
<Field Name="ERR_LOCKED">
<rd:TypeName>System.Int32</rd:TypeName>
<DataField>ERR_LOCKED</DataField>
</Field>
</Fields>
</DataSet>
</DataSets>
<Width>27.17019cm</Width>
<InteractiveHeight>11in</InteractiveHeight>
<Language>en-GB</Language>
<TopMargin>2.5cm</TopMargin>
<PageHeight>29.7cm</PageHeight>
</Report>
Thanks
mici
Neil,
Could you kindly post a sample RDL for which you see the problem