I'm not having fun anymore... I have done this in the past and now I am
unable to get this to work.
I have a report that accepts a date as a parameter. When called through the
web-page or in a subscription with static values for the date, this works. It
also works if the default value for the parameter is used. The default value
is a call to a datasource that simply calls "SELECT GETDATE()".
When I set up a data-driven subscription, give it the same datasource, give
the query of "SELECT GETDATE() AS REPORTDATE", and set the parameter to use
the REPORTDATE value; an error occurs stating that a type mismatch has been
encountered.
I have created a test report (attached below) that exhibits the same issue.
Set up a data-driven subscription with the "SELECT GETDATE() AS REPORTDATE"
query on any datasource.
<?xml version="1.0" encoding="utf-8"?>
<Report
xmlns="http://schemas.microsoft.com/sqlserver/reporting/2003/10/reportdefinition"
xmlns:rd="http://schemas.microsoft.com/SQLServer/reporting/reportdesigner">
<PageHeader>
<PrintOnLastPage>true</PrintOnLastPage>
<PrintOnFirstPage>true</PrintOnFirstPage>
<Style />
<Height>0.25in</Height>
</PageHeader>
<RightMargin>1in</RightMargin>
<Body>
<ReportItems>
<Textbox Name="textbox1">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<rd:DefaultName>textbox1</rd:DefaultName>
<Width>2.6875in</Width>
<CanGrow>true</CanGrow>
<Value>=Parameters!STARTDATE.Value</Value>
<Left>1.625in</Left>
</Textbox>
</ReportItems>
<Style />
<Height>0.375in</Height>
</Body>
<TopMargin>1in</TopMargin>
<DataSources>
<DataSource Name="CPR_commerce">
<rd:DataSourceID>ec9dccfe-38c9-45ca-a0c0-7364bfc665e8</rd:DataSourceID>
<DataSourceReference>CPR_commerce</DataSourceReference>
</DataSource>
</DataSources>
<Width>6.5in</Width>
<DataSets>
<DataSet Name="DEFAULTDATE">
<Fields>
<Field Name="ID">
<DataField />
<rd:TypeName>System.DateTime</rd:TypeName>
</Field>
</Fields>
<Query>
<DataSourceName>CPR_commerce</DataSourceName>
<CommandText>SELECT GETDATE()</CommandText>
<rd:UseGenericDesigner>true</rd:UseGenericDesigner>
</Query>
</DataSet>
</DataSets>
<LeftMargin>1in</LeftMargin>
<rd:SnapToGrid>true</rd:SnapToGrid>
<rd:DrawGrid>true</rd:DrawGrid>
<rd:ReportID>2549c4c5-9946-4a66-abda-7784310a7b58</rd:ReportID>
<PageFooter>
<PrintOnLastPage>true</PrintOnLastPage>
<PrintOnFirstPage>true</PrintOnFirstPage>
<Style />
<Height>0.25in</Height>
</PageFooter>
<BottomMargin>1in</BottomMargin>
<ReportParameters>
<ReportParameter Name="STARTDATE">
<DataType>DateTime</DataType>
<DefaultValue>
<DataSetReference>
<DataSetName>DEFAULTDATE</DataSetName>
<ValueField>ID</ValueField>
</DataSetReference>
</DefaultValue>
<AllowBlank>true</AllowBlank>
<Prompt>STARTDATE</Prompt>
</ReportParameter>
</ReportParameters>
<Language>en-US</Language>
</Report>Well... I've solved it. It seems that the locale setting was set to en-CA and
it was messing up the times. Not sure why or how this got set to that but I'm
sure I'll be able to figure it out or avoid it in the future... man, what a
pain.
"Rixmann" wrote:
> I'm not having fun anymore... I have done this in the past and now I am
> unable to get this to work.
> I have a report that accepts a date as a parameter. When called through the
> web-page or in a subscription with static values for the date, this works. It
> also works if the default value for the parameter is used. The default value
> is a call to a datasource that simply calls "SELECT GETDATE()".
> When I set up a data-driven subscription, give it the same datasource, give
> the query of "SELECT GETDATE() AS REPORTDATE", and set the parameter to use
> the REPORTDATE value; an error occurs stating that a type mismatch has been
> encountered.
> I have created a test report (attached below) that exhibits the same issue.
> Set up a data-driven subscription with the "SELECT GETDATE() AS REPORTDATE"
> query on any datasource.
> <?xml version="1.0" encoding="utf-8"?>
> <Report
> xmlns="http://schemas.microsoft.com/sqlserver/reporting/2003/10/reportdefinition"
> xmlns:rd="">http://schemas.microsoft.com/SQLServer/reporting/reportdesigner">
> <PageHeader>
> <PrintOnLastPage>true</PrintOnLastPage>
> <PrintOnFirstPage>true</PrintOnFirstPage>
> <Style />
> <Height>0.25in</Height>
> </PageHeader>
> <RightMargin>1in</RightMargin>
> <Body>
> <ReportItems>
> <Textbox Name="textbox1">
> <Style>
> <PaddingLeft>2pt</PaddingLeft>
> <PaddingBottom>2pt</PaddingBottom>
> <PaddingTop>2pt</PaddingTop>
> <PaddingRight>2pt</PaddingRight>
> </Style>
> <rd:DefaultName>textbox1</rd:DefaultName>
> <Width>2.6875in</Width>
> <CanGrow>true</CanGrow>
> <Value>=Parameters!STARTDATE.Value</Value>
> <Left>1.625in</Left>
> </Textbox>
> </ReportItems>
> <Style />
> <Height>0.375in</Height>
> </Body>
> <TopMargin>1in</TopMargin>
> <DataSources>
> <DataSource Name="CPR_commerce">
> <rd:DataSourceID>ec9dccfe-38c9-45ca-a0c0-7364bfc665e8</rd:DataSourceID>
> <DataSourceReference>CPR_commerce</DataSourceReference>
> </DataSource>
> </DataSources>
> <Width>6.5in</Width>
> <DataSets>
> <DataSet Name="DEFAULTDATE">
> <Fields>
> <Field Name="ID">
> <DataField />
> <rd:TypeName>System.DateTime</rd:TypeName>
> </Field>
> </Fields>
> <Query>
> <DataSourceName>CPR_commerce</DataSourceName>
> <CommandText>SELECT GETDATE()</CommandText>
> <rd:UseGenericDesigner>true</rd:UseGenericDesigner>
> </Query>
> </DataSet>
> </DataSets>
> <LeftMargin>1in</LeftMargin>
> <rd:SnapToGrid>true</rd:SnapToGrid>
> <rd:DrawGrid>true</rd:DrawGrid>
> <rd:ReportID>2549c4c5-9946-4a66-abda-7784310a7b58</rd:ReportID>
> <PageFooter>
> <PrintOnLastPage>true</PrintOnLastPage>
> <PrintOnFirstPage>true</PrintOnFirstPage>
> <Style />
> <Height>0.25in</Height>
> </PageFooter>
> <BottomMargin>1in</BottomMargin>
> <ReportParameters>
> <ReportParameter Name="STARTDATE">
> <DataType>DateTime</DataType>
> <DefaultValue>
> <DataSetReference>
> <DataSetName>DEFAULTDATE</DataSetName>
> <ValueField>ID</ValueField>
> </DataSetReference>
> </DefaultValue>
> <AllowBlank>true</AllowBlank>
> <Prompt>STARTDATE</Prompt>
> </ReportParameter>
> </ReportParameters>
> <Language>en-US</Language>
> </Report>
