Report parameter with datetime

Hi,

Anyone can help me?

I created a store proc with @.startdate and @.enddate

in query analyser - I run it as Exec SP_Admission '2006/01/01','2006/01/25' and it is ok.

Then I created a report in VS2005 and set the parameter as datetime in report parameter.

if I manually force to fill startdate and enddate with ‘yyyy/mm/dd’ format in preview, it works.

However if I choose the date from the given calendar(Date Time Picker), it doesn’t work

it is said that "The value provided the report parameter 'enddate' is not valid for its type.

Of course it not valid because it always comes with ‘dd/mm/yyyy’ format

I didn't setup the date time picker, it will automatically when I choose datetime as datatype.

any idea what should I do?

thanks,

Susan

Hi Susan,

not exactly brilliant, but

Exec SP_Admission convert(datetime,'12/10/2006',103),convert(datetime,'12/11/2006',103)
should work in your case, so put your parameter instead of '12/10/2006'
|||

Thanks...

someone suggested that in my SP -

I declare as varchar then in where statement I convert it as you mention.

however in report para - I can't set it as datetime but set to string.

it works ok but I can't use the date time picker .

well at least works that way...

Thanks so much

Report Parameter Toolbar

Good day everyone...

I created a simple report in SQL Server 2005 Reporting Services. Its about getting the sales from specific date and store. My parameters are date and storename, the report works fine as I view it in the reportserver. But, when we incorporate the report in a custom webpage using a reportviewer, the parameter toolbar occupies half the space, now that's my problem. Is there anyway I can create a custom Toolbar in Visual Studio so I can just set the parameter property to hidden so the default parameter toolbar wont show?

Thanks in advance guys... c",)

Yes. There's nothing extra (like controls) supplied to allow this. Just create the parameters using standard controls (dropdowns, dat pickers, whatever you like) and then in code pass those parameters to the report viewer control.sql

Report Group Header and Footer

Hi,
I know group header has to be below table header. But my report output
is like this:
DA: 111 Wolfson
Store Num ABC
23 Y
24 Y
25 W
26 W
Total restaurants with ABC systems: 2
Total restaurants with No ABC systems: 0
Total restaurants with waivers for ABC systems: 2
DA: 121 Von
Store Num ABC
33 Y
34 N
35 W
Total restaurants with ABC systems: 1
Total restaurants with No ABC systems: 1
Total restaurants with waivers for ABC systems: 1
My questions are:
1. Is there anyway to show group information(in this case is DA
information) before table header?
2. Should I use the group footer or text box for the summary part?
3. How can I populate the values for the summary?
Thanks in advance.
DanniThe report at the end of this posting should demonstrate how to accomplish
how to do conditional summations in a table group footer. The key expression
is
=Count(iif(Fields!ABC.Value = "<Place Y, N, or W here",
Fields!ABC.Value, Nothing), "DistrictGroup").
Bruce Johnson [MSFT]
Microsoft SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"Danni Liao" <danniliao@.yahoo.com> wrote in message
news:9d716b4a.0408200942.1a399384@.posting.google.com...
> Hi,
> I know group header has to be below table header. But my report output
> is like this:
> DA: 111 Wolfson
> Store Num ABC
> 23 Y
> 24 Y
> 25 W
> 26 W
> Total restaurants with ABC systems: 2
> Total restaurants with No ABC systems: 0
> Total restaurants with waivers for ABC systems: 2
> DA: 121 Von
> Store Num ABC
> 33 Y
> 34 N
> 35 W
> Total restaurants with ABC systems: 1
> Total restaurants with No ABC systems: 1
> Total restaurants with waivers for ABC systems: 1
>
> My questions are:
> 1. Is there anyway to show group information(in this case is DA
> information) before table header?
> 2. Should I use the group footer or text box for the summary part?
> 3. How can I populate the values for the summary?
> Thanks in advance.
> Danni
ConditionalSummaryValues.rdl
----
<?xml version="1.0" encoding="utf-8"?>
<Report
xmlns="http://schemas.microsoft.com/sqlserver/reporting/2003/10/reportdefini
tion"
xmlns:rd="">http://schemas.microsoft.com/SQLServer/reporting/reportdesigner">
<PageHeader>
<PrintOnLastPage>true</PrintOnLastPage>
<PrintOnFirstPage>true</PrintOnFirstPage>
<Style />
<Height>0.375in</Height>
</PageHeader>
<RightMargin>1in</RightMargin>
<Body>
<ReportItems>
<Table Name="table1">
<Style />
<Header>
<TableRows>
<TableRow>
<Height>0.25in</Height>
<TableCells>
<TableCell>
<ReportItems>
<Textbox Name="textbox31">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>29</ZIndex>
<rd:DefaultName>textbox31</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value />
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox4">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>28</ZIndex>
<rd:DefaultName>textbox4</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value />
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox3">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>27</ZIndex>
<rd:DefaultName>textbox3</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value />
</Textbox>
</ReportItems>
</TableCell>
</TableCells>
</TableRow>
</TableRows>
</Header>
<Details>
<TableRows>
<TableRow>
<Height>0.25in</Height>
<TableCells>
<TableCell>
<ReportItems>
<Textbox Name="StoreNumber">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<TextAlign>Right</TextAlign>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>2</ZIndex>
<rd:DefaultName>StoreNumber</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value>=Fields!StoreNumber.Value</Value>
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="ABC">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>1</ZIndex>
<rd:DefaultName>ABC</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value>=Fields!ABC.Value</Value>
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox21">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<rd:DefaultName>textbox21</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value />
</Textbox>
</ReportItems>
</TableCell>
</TableCells>
</TableRow>
</TableRows>
</Details>
<DataSetName>ABCSystems</DataSetName>
<Width>6.50001in</Width>
<TableGroups>
<TableGroup>
<Header>
<TableRows>
<TableRow>
<Height>0.25in</Height>
<TableCells>
<TableCell>
<ReportItems>
<Textbox Name="textbox2">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>26</ZIndex>
<rd:DefaultName>textbox2</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value>="DA: " & Fields!DistrictNumber.Value
& " " & Fields!DistrictName.Value</Value>
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox11">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>25</ZIndex>
<rd:DefaultName>textbox11</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value />
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox12">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>24</ZIndex>
<rd:DefaultName>textbox12</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value />
</Textbox>
</ReportItems>
</TableCell>
</TableCells>
</TableRow>
<TableRow>
<Height>0.25in</Height>
<TableCells>
<TableCell>
<ReportItems>
<Textbox Name="textbox16">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>23</ZIndex>
<rd:DefaultName>textbox16</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value>Store Number</Value>
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox17">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>22</ZIndex>
<rd:DefaultName>textbox17</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value>ABC</Value>
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox18">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>21</ZIndex>
<rd:DefaultName>textbox18</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value />
</Textbox>
</ReportItems>
</TableCell>
</TableCells>
</TableRow>
</TableRows>
</Header>
<Grouping Name="DistrictGroup">
<GroupExpressions>
<GroupExpression>=Fields!DistrictNumber.Value</GroupExpression>
</GroupExpressions>
</Grouping>
<Footer>
<TableRows>
<TableRow>
<Height>0.25in</Height>
<TableCells>
<TableCell>
<ReportItems>
<Textbox Name="textbox13">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>5</ZIndex>
<rd:DefaultName>textbox13</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value />
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox14">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>4</ZIndex>
<rd:DefaultName>textbox14</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value />
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox15">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>3</ZIndex>
<rd:DefaultName>textbox15</rd:DefaultName>
<Value />
<CanGrow>true</CanGrow>
</Textbox>
</ReportItems>
</TableCell>
</TableCells>
</TableRow>
<TableRow>
<Height>0.25in</Height>
<TableCells>
<TableCell>
<ReportItems>
<Textbox Name="textbox1">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>8</ZIndex>
<rd:DefaultName>textbox1</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value>ABC System Summary</Value>
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox5">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>7</ZIndex>
<rd:DefaultName>textbox5</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value />
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox28">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>6</ZIndex>
<rd:DefaultName>textbox28</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value />
</Textbox>
</ReportItems>
</TableCell>
</TableCells>
</TableRow>
<TableRow>
<Height>0.25in</Height>
<TableCells>
<TableCell>
<ReportItems>
<Textbox Name="textbox20">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>11</ZIndex>
<rd:DefaultName>textbox20</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value>Total restaurants with</Value>
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox19">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>10</ZIndex>
<CanGrow>true</CanGrow>
<Value>=Count(iif(Fields!ABC.Value = "Y",
Fields!ABC.Value, Nothing), "DistrictGroup")</Value>
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox6">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>9</ZIndex>
<rd:DefaultName>textbox6</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value />
</Textbox>
</ReportItems>
</TableCell>
</TableCells>
</TableRow>
<TableRow>
<Height>0.25in</Height>
<TableCells>
<TableCell>
<ReportItems>
<Textbox Name="textbox26">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>14</ZIndex>
<CanGrow>true</CanGrow>
<Value>Total restaurants without</Value>
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox27">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>13</ZIndex>
<CanGrow>true</CanGrow>
<Value>=Count(iif(Fields!ABC.Value = "N",
Fields!ABC.Value, Nothing), "DistrictGroup")</Value>
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox22">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>12</ZIndex>
<rd:DefaultName>textbox22</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value />
</Textbox>
</ReportItems>
</TableCell>
</TableCells>
</TableRow>
<TableRow>
<Height>0.25in</Height>
<TableCells>
<TableCell>
<ReportItems>
<Textbox Name="textbox23">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>17</ZIndex>
<rd:DefaultName>textbox23</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value>Total restaurants with waviers</Value>
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox29">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>16</ZIndex>
<CanGrow>true</CanGrow>
<Value>=Count(iif(Fields!ABC.Value = "W",
Fields!ABC.Value, Nothing), "DistrictGroup")</Value>
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox25">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>15</ZIndex>
<rd:DefaultName>textbox25</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value />
</Textbox>
</ReportItems>
</TableCell>
</TableCells>
</TableRow>
</TableRows>
</Footer>
</TableGroup>
</TableGroups>
<Footer>
<TableRows>
<TableRow>
<Height>0.25in</Height>
<TableCells>
<TableCell>
<ReportItems>
<Textbox Name="textbox7">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>20</ZIndex>
<rd:DefaultName>textbox7</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value />
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox8">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>19</ZIndex>
<rd:DefaultName>textbox8</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value />
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox9">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>18</ZIndex>
<rd:DefaultName>textbox9</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value />
</Textbox>
</ReportItems>
</TableCell>
</TableCells>
</TableRow>
</TableRows>
</Footer>
<TableColumns>
<TableColumn>
<Width>2.16667in</Width>
</TableColumn>
<TableColumn>
<Width>2.16667in</Width>
</TableColumn>
<TableColumn>
<Width>2.16667in</Width>
</TableColumn>
</TableColumns>
</Table>
</ReportItems>
<Style />
<Height>2.5in</Height>
</Body>
<TopMargin>1in</TopMargin>
<DataSources>
<DataSource Name="TestData">
<rd:DataSourceID>125b03ad-d78e-4a50-ba57-e4d162f02d59</rd:DataSourceID>
<ConnectionProperties>
<DataProvider>SQL</DataProvider>
<ConnectString>data source=Localhost;initial
catalog=TestData</ConnectString>
<IntegratedSecurity>true</IntegratedSecurity>
</ConnectionProperties>
</DataSource>
</DataSources>
<Width>7.25001in</Width>
<DataSets>
<DataSet Name="ABCSystems">
<Fields>
<Field Name="DistrictName">
<DataField>DistrictName</DataField>
<rd:TypeName>System.String</rd:TypeName>
</Field>
<Field Name="DistrictNumber">
<DataField>DistrictNumber</DataField>
<rd:TypeName>System.Int32</rd:TypeName>
</Field>
<Field Name="StoreNumber">
<DataField>StoreNumber</DataField>
<rd:TypeName>System.Int32</rd:TypeName>
</Field>
<Field Name="ABC">
<DataField>ABC</DataField>
<rd:TypeName>System.String</rd:TypeName>
</Field>
</Fields>
<Query>
<DataSourceName>TestData</DataSourceName>
<CommandText>SELECT DistrictName, DistrictNumber, StoreNumber,
ABC
FROM ABCSystems</CommandText>
</Query>
</DataSet>
</DataSets>
<LeftMargin>1in</LeftMargin>
<rd:SnapToGrid>true</rd:SnapToGrid>
<rd:DrawGrid>true</rd:DrawGrid>
<rd:ReportID>fff9cc12-b0c8-4f96-81dc-db45f51fd131</rd:ReportID>
<BottomMargin>1in</BottomMargin>
<Language>en-US</Language>
</Report>|||Bruce,
Thanks very much. It works like a charm.
Danni