Can not change filter on model report - UPDATED 7th June

I have a very simple report based on a test model.

The report is filtered via a date field and runs ok.

If I try to change the filter I get the following error message:

Operation Failed

Short time pattern does not include hours and minutes

Does anyone know how I can correct this

Regards

John

UPDATE

This only happens if I try to filter between two dates, a single date works correctly!

Answer this question

Can not change filter on model report - UPDATED 7th June

Oliver
The report executes with no problems, I only get the error if I try to change the filter.

What is strange, I can get around the error by apply to separate date filters, one using Greater Than Or Equal and the other Less Than or Equal.

This gets around the problem but I would like to offer my end users the option of From and To as this is what the use in other parts of SSRS.

Regards

John
bigd68412
No committed dates yet, but I believe it's on track for later this year.
bernie_e

Is this error happening when you are editing report or when you are running this report Can you save this report to report server and run it from Report Manager (http://rshost/reports)

Thanks!
stephQc
Alexandre

This is the rdl for the failing report.

< 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">

<Language>en-US</Language>

<BottomMargin>0.5in</BottomMargin>

<RightMargin>0.5in</RightMargin>

<DataSets>

<DataSet Name="dataSet">

<Query>

<DataSourceName>dataSource1</DataSourceName>

<CommandText><SemanticQuery xmlns="http://schemas.microsoft.com/sqlserver/2004/10/semanticmodeling" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:rb="http://schemas.microsoft.com/sqlserver/2004/11/reportbuilder" xmlns:qd="http://schemas.microsoft.com/sqlserver/2004/11/semanticquerydesign">

<Hierarchies>

<Hierarchy>

<BaseEntity>



<EntityID>G066c76f8-9856-4acd-8b54-d734971da2c8</EntityID>

</BaseEntity>

<Groupings>

<Grouping Name="Epex Referral">

<Expression Name="Epex Referral">

<EntityRef>



<EntityID>G066c76f8-9856-4acd-8b54-d734971da2c8</EntityID>

</EntityRef>

</Expression>

<Details>

<Expression Name="Created">

<AttributeRef>



<AttributeID>G9a9689b0-64a7-49de-abae-58b8d30322e9</AttributeID>

</AttributeRef>

</Expression>

<Expression Name="Close Date">

<AttributeRef>



<AttributeID>G36ac2614-9e79-4c60-9c3e-fc45de7c67ec</AttributeID>

</AttributeRef>

</Expression>

<Expression Name="Patient">

<Path>

<RolePathItem>



<RoleID>G036974d5-33a0-4096-bc96-166d82853818</RoleID>

</RolePathItem>

</Path>

<AttributeRef>



<AttributeID>Gd6622d3c-8a4f-4273-a190-c8834bf90031</AttributeID>

</AttributeRef>

</Expression>

<Expression Name="expr1">

<AttributeRef>



<AttributeID>Gfee3c95d-eb72-48c3-b372-0e2865ac44c8</AttributeID>

</AttributeRef>

</Expression>

</Details>

</Grouping>

</Groupings>

<Filter>

<Expression Name="expr2">

<Function>

<FunctionName>And</FunctionName>

<Arguments>

<Expression>

<Function>

<FunctionName>GreaterThanOrEquals</FunctionName>

<Arguments>

<Expression>

<AttributeRef>



<AttributeID>G9a9689b0-64a7-49de-abae-58b8d30322e9</AttributeID>

</AttributeRef>

</Expression>

<Expression>

<Literal>

<DataType>DateTime</DataType>

<Value>2005-01-01T00:00:00</Value>

</Literal>

</Expression>

</Arguments>

</Function>

</Expression>

<Expression>

<Function>

<FunctionName>LessThanOrEquals</FunctionName>

<Arguments>

<Expression>

<AttributeRef>



<AttributeID>G9a9689b0-64a7-49de-abae-58b8d30322e9</AttributeID>

</AttributeRef>

</Expression>

<Expression>

<Literal>

<DataType>DateTime</DataType>

<Value>2006-01-01T00:00:00</Value>

</Literal>

</Expression>

</Arguments>

</Function>

</Expression>

</Arguments>

</Function>

<CustomProperties>

<CustomProperty Name="qd:FilterCondition" />

<CustomProperty Name="qd:Filter" />

<CustomProperty Name="qd:ContextEntityID">

<Value xsi:type="xsd:string">G066c76f8-9856-4acd-8b54-d734971da2c8</Value>

</CustomProperty>

<CustomProperty Name="qd:AutoChangeBaseEntity" />

<CustomProperty Name="qd:Design">

<Value xsi:type="xsd:string">expr3</Value>

</CustomProperty>

</CustomProperties>

</Expression>

</Filter>

</Hierarchy>

</Hierarchies>

<CalculatedAttributes>

<Expression Name="expr3">

<Function>

<FunctionName>And</FunctionName>

<Arguments>

<Expression>

<Function>

<FunctionName>And</FunctionName>

<Arguments>

<Expression>

<Function>

<FunctionName>GreaterThanOrEquals</FunctionName>

<Arguments>

<Expression>

<AttributeRef>



<AttributeID>G9a9689b0-64a7-49de-abae-58b8d30322e9</AttributeID>

</AttributeRef>

</Expression>

<Expression>

<Literal>

<DataType>DateTime</DataType>

<Value>2005-01-01T00:00:00</Value>

</Literal>

</Expression>

</Arguments>

</Function>

</Expression>

<Expression>

<Function>

<FunctionName>LessThanOrEquals</FunctionName>

<Arguments>

<Expression>

<AttributeRef>



<AttributeID>G9a9689b0-64a7-49de-abae-58b8d30322e9</AttributeID>

</AttributeRef>

</Expression>

<Expression>

<Literal>

<DataType>DateTime</DataType>

<Value>2006-01-01T00:00:00</Value>

</Literal>

</Expression>

</Arguments>

</Function>

</Expression>

</Arguments>

</Function>

<CustomProperties>

<CustomProperty Name="qd:FilterCondition" />

</CustomProperties>

</Expression>

<Expression>

<Null />

<CustomPropertes>

<CustomProperty Name="qd:Unspecified" />

</CustomProperties>

</Expression>

</Arguments>

</Function>

<CustomProperties>

<CustomProperty Name="qd:Filter" />

<CustomProperty Name="qd:ContextEntityID">

<Value xsi:type="xsd:string">G066c76f8-9856-4acd-8b54-d734971da2c8</Value>

</CustomProperty>

<CustomProperty Name="qd:AutoChangeBaseEntity" />

</CustomProperties>

</Expression>

</CalculatedAttributes>

</SemanticQuery></CommandText>

</Query>

<Fields>

<Field Name="EpexReferral">

<DataField>Epex Referral</DataField>

</Field>

<Field Name="Created">

<DataField>Created</DataField>

</Field>

<Field Name="CloseDate">

<DataField>Close Date</DataField>

</Field>

<Field Name="Patient">

<DataField>Patient</DataField>

</Field>

<Field Name="expr1">

<DataField>expr1</DataField>

</Field>

</Fields>

</DataSet>

</DataSets>

<DataSources>

<DataSource Name="dataSource1">

<DataSourceReference>/Models/Mars Cutdown</DataSourceReference>

<rd:DataSourceID>4d1edcd6-3c78-4e68-9566-ef952e495be4</rd:DataSourceID>

</DataSource>

</DataSources>

<PageHeight>11in</PageHeight>

<LeftMargin>0.5in</LeftMargin>

<TopMargin>0.5in</TopMargin>

<Width>7.5in</Width>

<Body>

<Height>0in</Height>

<ReportItems>

<Textbox Name="Title">

<Left>0.5in</Left>

<Top>0.5in</Top>

<CanGrow>true</CanGrow>

<Width>6.5in</Width>

<Value />

<Style>

<FontFamily>Tahoma</FontFamily>

<TextAlign>Left</TextAlign>

<PaddingLeft>2pt</PaddingLeft>

<PaddingBottom>2pt</PaddingBottom>

<FontWeight>Normal</FontWeight>

<FontSize>18pt</FontSize>

<PaddingRight>2pt</PaddingRight>

<PaddingTop>2pt</PaddingTop>

</Style>

<CustomProperties>

<CustomProperty>

<Name>rb:Watermark</Name>

<Value>Click to add title</Value>

</CustomProperty>

</CustomProperties>

<Height>0.375in</Height>

</Textbox>

<Table Name="table">

<Top>1.25in</Top>

<Details>

<TableRows>

<TableRow>

<TableCells>

<TableCell>

<ReportItems>

<Textbox Name="Created_Value">

<DataElementOutput>Output</DataElementOutput>

<CanGrow>true</CanGrow>

<Action>

<Drillthrough>

<ReportName>=DataSources!dataSource1.DataSourceReference</ReportName>

<Parameters>

<Parameter Name="rs:EntityID">

<Value>G066c76f8-9856-4acd-8b54-d734971da2c8</Value>

</Parameter>

<Parameter Name="rs:DrillType">

<Value>Detail</Value>

</Parameter>

<Parameter Name="rs:Command">

<Value>Drillthrough</Value>

</Parameter>

<Parameter Name="DrillthroughSourceQuery">

<Value>=DataSets!dataSet.RewrittenCommandText</Value>

</Parameter>

<Parameter Name="DrillthroughContext">

<Value>=CreateDrillthroughContext()</Value>

</Parameter>

</Parameters>

</Drillthrough>

</Action>

<Value>=Fields!Created.Value</Value>

<Styl>

<Language>en-US</Language>

<BorderColor>

<Default>LightGrey</Default>

</BorderColor>

<BorderStyle>

<Default>Solid</Default>

</BorderStyle>

<FontFamily>Tahoma</FontFamily>

<PaddingLeft>2pt</PaddingLeft>

<PaddingBottom>2pt</PaddingBottom>

<FontWeight>Normal</FontWeight>

<FontSize>8pt</FontSize>

<Format>d</Format>

<BackgroundColor>White</BackgroundColor>

<PaddingRight>2pt</PaddingRight>

<PaddingTop>2pt</PaddingTop>

</Style>

</Textbox>

</ReportItems>

</TableCell>

<TableCell>

<ReportItems>

<Textbox Name="CloseDate_Value">

<DataElementOutput>Output</DataElementOutput>

<CanGrow>true</CanGrow>

<Value>=Fields!CloseDate.Value</Value>

<Style>

<Language>en-US</Language>

<BorderColor>

<Default>LightGrey</Default>

</BorderColor>

<BorderStyle>

<Default>Solid</Default>

</BorderStyle>

<FontFamily>Tahoma</FontFamily>

<PaddingLeft>2pt</PaddingLeft>

<PaddingBottom>2pt</PaddingBottom>

<FontWeight>Normal</FontWeight>

<FontSize>8pt</FontSize>

<Format>d</Format>

<BackgroundColor>White</BackgroundColor>

<PaddingRight>2pt</PaddingRight>

<PaddingTop>2pt</PaddingTop>

</Style>

</Textbox>

</ReportItems>

</TableCell>

<TableCell>

<ReportItems>

<Textbox Name="Patient_Value">

<DataElementOutput>Output</DataElementOutput>

<CanGrow>true</CanGrow>

<Action>

<Drillthrough>

<ReportName>=DataSources!dataSource1.DataSourceReference</ReportName>

<Parameters>

<Parameter Name="rs:EntityID">

<Value>G2ceef5ce-4e09-4806-8e8c-9a4e4e8f35fa</Value>

</Parameter>

<Parameter Name="rs:DrillType">

<Value>Detail</Value>

</Parameter>

<Parameter Name="rs:Command">

<Value>Drillthrough</Value>

</Parameter>

<Parameter Name="DrillthroughSourceQuery">

<Value>=DataSets!dataSet.RewrittenCommandText</Value>

</Parameter>

<Parameter Name="DrillthroughContext">

<Value>=CreateDrillthroughContext()</Value>

</Parameter>

</Parameters>

</Drillthrough>

</Action>

<Value>=Fields!Patient.Value</Value>

<Style>

<Language>en-US</Language>

<BorderColor>

<Default>LightGrey</Default>

</BorderColor>

<BorderStyle>

<Default>Solid</Default>

</BorderStyle>

<FontFamily>Tahoma</FontFamily>

<PaddingLeft>2pt</PaddingLeft>

<PaddingBottom>2pt</PaddingBottom>

<FontWeight>Normal</FontWeight>

<FontSize>8pt</FontSize>

<BackgroundColor>White</BackgroundColor>

<PaddingRight>2pt</PaddingRight>

<PaddingTop>2pt</PaddingTop>

</Style>

</Textbox>

</ReportItems>

</TableCell>

</TableCells>

<Height>0.25in</Height>

</TableRow>

</TableRows>

<Grouping Name="table_EpexReferral">

<GroupExpressions>

<GroupExpression>=Fields!EpexReferral.Value</GroupExpression>

</GroupExpressions>

</Grouping>

<Sorting>

<SortBy>

<SortExpression>=IIf(True, Fields!expr1.Value, Nothing)</SortExpression>

<Direction>Descending</Direction>

</SortBy>

<SortBy>

<SortExpression>=IIf(True, Fields!Created.Value, Nothing)</SortExpression>

<Direction>Descending</Direction>

</SortBy>

</Sorting>

</Details>

<Style />

<Width>0in</Width>

<Height>0in</Height>

<DataSetName>dataSet</DataSetName>

<TableColumns>

<TableColumn>

<Width>0.90092in</Width>

<Visibility>

<Hidden>=Fields!Created.IsMissing</Hidden>

</Visibility>

</TableColumn>

<TableColumn>

<Width>1.06361in</Width>

<Visibility>

<Hidden>=Fields!CloseDate.IsMissing</Hidden>

</Visibility>

</TableColumn>

<TableColumn>

<Width>1.62189in</Width>

<Visibility>

<Hidden>=Fields!Patient.IsMissing</Hidden>

</Visibility>

</TableColumn>

</TableColumns>

<Header>

<TableRows>

<TableRow>

<TableCells>

<TableCell>

<ReportItems>

<Textbox Name="Created_Header">

<CanGrow>true</CanGrow>

<UserSort>

<SortExpression>=Fields!Created.Value</SortExpression>

<SortExpressionScope>table_EpexReferral</SortExpressionScope>

</UserSort>

<Value>Created</Value>

<Style>

<Language>en-US</Language>

<BorderColor>

<Default>LightGrey</Default>

</BorderColor>

<BorderStyle>

<Default>Solid</Default>

</BorderStyle>

<FontFamily>Tahoma</FontFamily>

<PaddingLeft>2pt</PaddingLeft>

<PaddingBottom>2pt</PaddingBottom>

<FontWeight>Bold</FontWeight>

<FontSize>8pt</FontSize>

<BackgroundColor>#518ae5</BackgroundColor>

<Color>White</Color>

<PaddingRight>2pt</PaddingRight>

<PaddingTop>2pt</PaddingTop>

</Style>

</Textbox>

</ReportItems>

</TableCell>

<TableCell>

<ReportItems>

<Textbox Name="CloseDate_Header">

<CanGrow>true</CanGrow>

<UserSort>

<SortExpression>=Fields!CloseDate.Value</SortExpression>

<SortExpressionScope>table_EpexReferral</SortExpressionScope>

</UserSort>

<Value>Close Date</Value>

<Style

<Language>en-US</Language>

<BorderColor>

<Default>LightGrey</Default>

</BorderColor>

<BorderStyle>

<Default>Solid</Default>

</BorderStyle>

<FontFamily>Tahoma</FontFamily>

<PaddingLeft>2pt</PaddingLeft>

<PaddingBottom>2pt</PaddingBottom>

<FontWeight>Bold</FontWeight>

<FontSize>8pt</FontSize>

<BackgroundColor>#518ae5</BackgroundColor>

<Color>White</Color>

<PaddingRight>2pt</PaddingRight>

<PaddingTop>2pt</PaddingTop>

</Style>

</Textbox>

</ReportItems>

</TableCell>

<TableCell>

<ReportItems>

<Textbox Name="Patient_Header">

<CanGrow>true</CanGrow>

<UserSort>

<SortExpression>=Fields!Patient.Value</SortExpression>

<SortExpressionScope>table_EpexReferral</SortExpressionScope>

</UserSort>

<Value>Patient</Value>

<Style>

<Language>en-US</Language>

<BorderColor>

<Default>LightGrey</Default>

</BorderColor>

<BorderStyle>

<Default>Solid</Default>

</BorderStyle>

<FontFamily>Tahoma</FontFamily>

<PaddingLeft>2pt</PaddingLeft>

<PaddingBottom>2pt</PaddingBottom>

<FontWeight>Bold</FontWeight>

<FontSize>8pt</FontSize>

<BackgroundColor>#518ae5</BackgroundColor>

<Color>White</Color>

<PaddingRight>2pt</PaddingRight>

<PaddingTop>2pt</PaddingTop>

</Style>

</Textbox>

</ReportItems>

</TableCell>

</TableCells>

<Height>0.25in</Height>

</TableRow>

</TableRows>

<RepeatOnNewPage>true</RepeatOnNewPage>

<FixedHeader>true</FixedHeader>

</Header>

<Left>0.5in</Left>

</Table>

<Textbox Name="TotalRows">

<Left>0.5in</Left>

<Top>3in</Top>

<CanGrow>true</CanGrow>

<Width>6.5in</Width>

<Value>=String.Format("Total Epex Referrals" & Chr(58) & " {0}", COUNTROWS("dataSet"))</Value>

<Style>

<PaddingLeft>3pt</PaddingLeft>

<PaddingBottom>3pt</PaddingBottom>

<FontWeight>Normal</FontWeight>

<FontSize>8pt</FontSize>

<PaddingRight>3pt</PaddingRight>

<PaddingTop>3pt</PaddingTop>

</Style>

<CustomProperties>

<CustomProperty>

<Name>rb:SpecialContent</Name>

<Value>TotalRows</Value>

</CustomProperty>

</CustomProperties>

<Height>0.25in</Height>

</Textbox>

<Textbox Name="FilterDescription">

<Left>0.5in</Left>

<Top>3.5in</Top>

<CanGrow>true</CanGrow>

<Width>6.5in</Width>

<Value>Filter: Epex Referrals with: Created from 01/01/2005 to 01/01/2006</Value>

<Style>

<PaddingLeft>3pt</PaddingLeft>

<PaddingBottom>3pt</PaddingBottom>

<FontWeight>Normal</FontWeight>

<FontSize>8pt</FontSize>

<PaddingRight>3pt</PaddingRight>

<PaddingTop>3pt</PaddingTop>

</Style>

<CustomProperties>

<CustomProperty>

<Name>rb:SpecialContent</Name>

Value>FilterDescription</Value>

</CustomProperty>

</CustomProperties>

<Height>0.25in</Height>

</Textbox>

</ReportItems>

<Style />

</Body>

<PageWidth>8.5in</PageWidth>

</Report>
Vizi

We have the same problem even after installing SP1 and the hotfixes. Has anybody a solution/answer

Regards

Werner
sohaibi

Bob,

thanks for the immediate answer, when can we approx. expect SP2

Regards

Werner
BeFa
This is a known issue that will be fixed in SP2. Unfortunately the only way to avoid this error for now is to change your Windows regional settings to use a 12-hour clock.
Rioch Mulhall
Can you please save report that fails to file and post it
Kal-EL_

Bob,

i tried to change the format in the regional settings like you said but no changes. We use a german W2003 server... When i select a date field in the report builder filter, select a date from the datetime picker (22.06.2006) and try to save it with the ok button i get a message box saying 'no valid date'... !

Can i change anything in the model to solve the problem Is this filter problem caused by SP1 (in this case we could think about setting up the SQL Server again)

Regards

Werner
Can not change filter on model report - UPDATED 7th June

Answer this question