Saturday, February 25, 2012

Report and Subreport

Ok, I am attempting to create my first report in SQL Reporting services. I
am using SQL Enterprise 2000 sp3, VS.NET 2003, and IIS5.
I have created a report that has several columns of general information.
One of the columns is "employee_id" This report worked fine up to this
point.
I then created a subreport that pulls some employee information based on the
employee_id. This report uses a stored procedure to get the data, and the
stored proc parameter is called @.employeeID.
I have placed the subreport on the master report, set its report name to the
name of the sub report, and set it's passed parameter to the "employee_id"
field of the master report.
In the subreport, I have set the dataset's employee_id parameter to the
subreport's employee_id parameter.
When I run the master report I receive an error stating that the stored proc
used by the sub report did not receive it's value. So, it appears that the
report parameters are not making it to the stored proc... What am I
missing? Is there something special that I need to do to make the
subreports parameter pass on to the stored proc used by the subreport?
any help would be appreciated.
thanks in advance.Did you specify the subreport in hyperlink action (navigation) for
employee_id in main report, if so, this should work. Here's an example.
You'll need the following stored procedure in local pubs database:
// +++++++++++++++++++++++++++++++++++++++++++
create proc GetPublisherDetails (@.pubid_param int) As
begin
select * from publishers where pub_id = @.pubid_param
end
// +++++++++++++++++++++++++++++++++++++++++++
Here are the RDLs for main report and child report. They go against the
local pubs database. Please let me know if this works for you.
Main report:
<?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">
<RightMargin>1in</RightMargin>
<Body>
<ReportItems>
<Table Name="table1">
<Height>0.75in</Height>
<Style />
<Header>
<TableRows>
<TableRow>
<Height>0.25in</Height>
<TableCells>
<TableCell>
<ReportItems>
�Textbox Name="textbox5">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<FontSize>8pt</FontSize>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
<FontWeight>700</FontWeight>
</Style>
<ZIndex>5</ZIndex>
<rd:DefaultName>textbox5</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value>pub id</Value>
�/Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
�Textbox Name="textbox1">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<FontSize>8pt</FontSize>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
<FontWeight>700</FontWeight>
</Style>
<ZIndex>4</ZIndex>
<rd:DefaultName>textbox1</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value>title</Value>
�/Textbox>
</ReportItems>
</TableCell>
</TableCells>
</TableRow>
</TableRows>
</Header>
<Details>
<TableRows>
<TableRow>
<Height>0.25in</Height>
<TableCells>
<TableCell>
<ReportItems>
�Textbox Name="textbox4">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<FontSize>8pt</FontSize>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>1</ZIndex>
<rd:DefaultName>textbox4</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value />
�/Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
�Textbox Name="title">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<FontSize>8pt</FontSize>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<rd:DefaultName>title</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value>=Fields!title.Value</Value>
�/Textbox>
</ReportItems>
</TableCell>
</TableCells>
</TableRow>
</TableRows>
</Details>
<DataSetName>pubs</DataSetName>
<Top>0.125in</Top>
<Width>4.33334in</Width>
<TableGroups>
<TableGroup>
<Header>
<TableRows>
<TableRow>
<Height>0.25in</Height>
<TableCells>
<TableCell>
<ReportItems>
<Textbox Name="pub_id">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<TextDecoratiok>Underline</TextDecoration>
<FontSize>8pt</FontSize>
<Color>Crimson</Color>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
<FontWeight>700</FontWeight>
</Style>
<ZIndex>3</ZIndex>
<rd:DefaultName>pub_id</rd:DefaultName>
<Action>
<Drillthrough>
<Parameters>
<Parameter Name="pubid_param">
<Value>= Fields!pub_id.Value</Value>
</Parameter>
</Parameters>
<ReportName>Child</ReportName>
</Drillthrough>
</Action>
<CanGrow>true</CanGrow>
<Value>=Fields!pub_id.Value</Value>
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox6">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<FontSize>8pt</FontSize>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>2</ZIndex>
<rd:DefaultName>textbox6</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value />
</Textbox>
</ReportItems>
</TableCell>
</TableCells>
</TableRow>
</TableRows>
</Header>
<Grouping Name="table1_Group1">
<GroupExpressions>
<GroupExpression>=Fields!pub_id.Value</GroupExpression>
</GroupExpressions>
</Grouping>
</TableGroup>
</TableGroups>
<Left>0.125in</Left>
<TableColumns>
<TableColumn>
<Width>2.16667in</Width>
</TableColumn>
<TableColumn>
<Width>2.16667in</Width>
</TableColumn>
</TableColumns>
</Table>
</ReportItems>
<Style />
<Height>3.125in</Height>
</Body>
<TopMargin>1in</TopMargin>
<DataSources>
<DataSource Name="pubs">
<rd:DataSourceID>d9a7bb6a-bc81-4f36-96e6-e27dffdfd71c</rd:DataSourceID>
<ConnectionProperties>
<DataProvider>SQL</DataProvider>
<ConnectString>initial catalog=pubs</ConnectString>
<IntegratedSecurity>true</IntegratedSecurity>
</ConnectionProperties>
</DataSource>
</DataSources>
<Width>6.5in</Width>
<DataSets>
<DataSet Name="pubs">
<Fields>
<Field Name="title">
<DataField>title</DataField>
<rd:TypeName>System.String</rd:TypeName>
</Field>
<Field Name="type">
<DataField>type</DataField>
<rd:TypeName>System.String</rd:TypeName>
</Field>
<Field Name="pub_id">
<DataField>pub_id</DataField>
<rd:TypeName>System.String</rd:TypeName>
</Field>
</Fields>
<Query>
<DataSourceName>pubs</DataSourceName>
<CommandText>select title, type, pub_id from titles</CommandText>
<rd:UseGenericDesigner>true</rd:UseGenericDesigner>
</Query>
</DataSet>
</DataSets>
<LeftMargin>1in</LeftMargin>
<rd:SnapToGrid>true</rd:SnapToGrid>
<rd:DrawGrid>true</rd:DrawGrid>
<rd:ReportID>608a77f9-a9e7-41db-ac50-c156ff9bd82e</rd:ReportID>
<BottomMargin>1in</BottomMargin>
<Language>en-US</Language>
</Report>
Child report:
<?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">
<RightMargin>1in</RightMargin>
<Body>
<ReportItems>
<Table Name="table1">
<Height>0.5in</Height>
<Style />
<Header>
<TableRows>
<TableRow>
<Height>0.25in</Height>
<TableCells>
<TableCell>
<ReportItems>
�Textbox Name="textbox1">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<FontSize>8pt</FontSize>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
<FontWeight>700</FontWeight>
</Style>
<ZIndex>7</ZIndex>
<rd:DefaultName>textbox1</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value>pub name</Value>
�/Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
�Textbox Name="textbox2">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<FontSize>8pt</FontSize>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
<FontWeight>700</FontWeight>
</Style>
<ZIndex>6</ZIndex>
<rd:DefaultName>textbox2</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value>city</Value>
�/Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
�Textbox Name="textboxk">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<FontSize>8pt</FontSize>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
<FontWeight>700</FontWeight>
</Style>
<ZIndex>5</ZIndex>
<rd:DefaultName>textbox3</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value>state</Value>
�/Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
�Textbox Name="textbox4">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<FontSize>8pt</FontSize>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
<FontWeight>700</FontWeight>
</Style>
<ZIndex>4</ZIndex>
<rd:DefaultName>textbox4</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value>country</Value>
�/Textbox>
</ReportItems>
</TableCell>
</TableCells>
</TableRow>
</TableRows>
</Header>
<Details>
<TableRows>
<TableRow>
<Height>0.25in</Height>
<TableCells>
<TableCell>
<ReportItems>
�Textbox Name="pub_name">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<FontSize>8pt</FontSize>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>3</ZIndex>
<rd:DefaultName>pub_name</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value>=Fields!pub_name.Value</Value>
�/Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
�Textbox Name="city">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<FontSize>8pt</FonkSize>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>2</ZIndex>
<rd:DefaultName>city</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value>=Fields!city.Value</Value>
�/Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
�Textbox Name="state">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<FontSize>8pt</FontSize>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>1</ZIndex>
<rd:DefaultName>state</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value>=Fields!state.Value</Value>
�/Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
�Textbox Name="country">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<FontSize>8pt</FontSize>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<rd:DefaultName>country</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value>=Fields!country.Value</Value>
�/Textbox>
</ReportItems>
</TableCell>
</TableCells>
</TableRow>
</TableRows>
</Details>
<DataSetName>pubs</DataSetName>
<TableColumns>
<TableColumn>
<Width>2.16667in</Width>
</TableColumn>
<TableColumn>
<Width>2.16667in</Width>
</TableColumn>
<TableColumn>
<Width>2.16667in</Width>
</TableColumn>
<TableColumn>
<Width>2.16667in</Width>
</TableColumn>
</TableColumns>
</Table>
</ReportItems>
<Style />
<Height>2in</Height>
</Body>
<TopMargin>1in</TopMargin>
<DataSources>
<DataSource Name="pubs">
<rd:DataSourceID>d6f77576-e36c-45dc-877c-a28464b055e8</rd:DataSourceID>
<ConnectionProperties>
<DataProvider>SQL</DataProvider>
<ConnectString>initial catalog=pubs</ConnectString>
<IntegratedSecurity>true</IntegratedSecurity>
</ConnectionProperties>
</DataSource>
</DataSources>
<Width>8.66667in</Width>
<DataSets>
<DataSet Name="pubs">
<Fields>
<Field Name="pub_id">
<DataField>pub_id</DataField>
<rd:TypeName>System.String</rd:TypeName>
</Field>
<Field Name="pub_name">
<DataField>pub_name</DataField>
<rd:TypeName>System.String</rd:TypeName>
</Field>
<Field Name="city">
<DataField>city</DataField>
<rd:TypeName>System.String</rd:TypeName>
</Field>
<Field Name="state">
<DataField>state</DataField>
<rd:TypeName>System.String</rd:TypeName>
</Field>
<Field Name="country">
<DataField>country</DataField>
<rd:TypeName>System.String</rd:TypeName>
</Field>
</Fields>
<Query>
<DataSourceName>pubs</DataSourceName>
<CommandType>StoredProcedure</CommandType>
<CommandText>GetPublisherDetails</CommandText>
<QueryParameters>
<QueryParameter Name="@.pubid_param">
<Value>=Parameters!pubid_param.Value</Value>
</QueryParameter>
</QueryParameters>
</Query>
</DataSet>
</DataSets>
<LeftMargin>1in</LeftMargin>
<rd:SnapToGrid>true</rd:SnapToGrid>
<rd:DrawGrid>true</rd:DrawGrid>
<rd:ReportID>ab635794-d5b4-4ac8-adc6-4891acac8879</rd:ReportID>
<BottomMargin>1in</BottomMargin>
<ReportParameters>
<ReportParameter Name="pubid_param">
<DataType>Integer</DataType>
<Prompt>pubid_param</Prompt>
</ReportParameter>
</ReportParameters>
<Language>en-US</Language>
</Report>
--
Ravi Mumulla (Microsoft)
SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"Johnny Fugazzi" <abillmeier@.ldmkusa.com> wrote in message
news:eAR8Ma8fEHA.216@.tk2msftngp13.phx.gbl...
> Ok, I am attempting to create my first report in SQL Reporting services.
I
> am using SQL Enterprise 2000 sp3, VS.NET 2003, and IIS5.
> I have created a report that has several columns of general information.
> One of the columns is "employee_id" This report worked fine up to this
> point.
> I then created a subreport that pulls some employee information based on
the
> employee_id. This report uses a stored procedure to get the data, and the
> stored proc parameter is called @.employeeID.
> I have placed the subreport on the master report, set its report name to
the
> name of the sub report, and set it's passed parameter to the "employee_id"
> field of the master report.
> In the subreport, I have set the dataset's employee_id parameter to the
> subreport's employee_id parameter.
> When I run the master report I receive an error stating that the stored
proc
> used by the sub report did not receive it's value. So, it appears that
the
> report parameters are not making it to the stored proc... What am I
> missing? Is there something special that I need to do to make the
> subreports parameter pass on to the stored proc used by the subreport?
> any help would be appreciated.
> thanks in advance.
>
>|||got it working.
thanks
"Ravi Mumulla (Microsoft)" <ravimu@.online.microsoft.com> wrote in message
news:O3ZduO9fEHA.904@.TK2MSFTNGP09.phx.gbl...
> Did you specify the subreport in hyperlink action (navigation) for
> employee_id in main report, if so, this should work. Here's an example.
> You'll need the following stored procedure in local pubs database:
> // +++++++++++++++++++++++++++++++++++++++++++
> create proc GetPublisherDetails (@.pubid_param int) As
> begin
> select * from publishers where pub_id = @.pubid_param
> end
> // +++++++++++++++++++++++++++++++++++++++++++
> Here are the RDLs for main report and child report. They go against the
> local pubs database. Please let me know if this works for you.
> Main report:
> <?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">
> <RightMargin>1in</RightMargin>
> <Body>
> <ReportItems>
> <Table Name="table1">
> <Height>0.75in</Height>
> <Style />
> <Header>
> <TableRows>
> <TableRow>
> <Height>0.25in</Height>
> <TableCells>
> <TableCell>
> <ReportItems>
> �Textbox Name="textbox5">
> <Style>
> <PaddingLeft>2pt</PaddingLeft>
> <FontSize>8pt</FontSize>
> <PaddingBottom>2pt</PaddingBottom>
> <PaddingTop>2pt</PaddingTop>
> <PaddingRight>2pt</PaddingRight>
> <FontWeight>700</FontWeight>
> </Style>
> <ZIndex>5</ZIndex>
> <rd:DefaultName>textbox5</rd:DefaultName>
> <CanGrow>true</CanGrow>
> <Value>pub id</Value>
> �/Textbox>
> </ReportItems>
> </TableCell>
> <TableCell>
> <ReportItems>
> �Textbox Name="textbox1">
> <Style>
> <PaddingLeft>2pt</PaddingLeft>
> <FontSize>8pt</FontSize>
> <PaddingBottom>2pt</PaddingBottom>
> <PaddingTop>2pt</PaddingTop>
> <PaddingRight>2pt</PaddingRight>
> <FontWeight>700</FontWeight>
> </Style>
> <ZIndex>4</ZIndex>
> <rd:DefaultName>textbox1</rd:DefaultName>
> <CanGrow>true</CanGrow>
> <Value>title</Value>
> �/Textbox>
> </ReportItems>
> </TableCell>
> </TableCells>
> </TableRow>
> </TableRows>
> </Header>
> <Details>
> <TableRows>
> <TableRow>
> <Height>0.25in</Height>
> <TableCells>
> <TableCell>
> <ReportItems>
> �Textbox Name="textbox4">
> <Style>
> <PaddingLeft>2pt</PaddingLeft>
> <FontSize>8pt</FontSize>
> <PaddingBottom>2pt</PaddingBottom>
> <PaddingTop>2pt</PaddingTop>
> <PaddingRight>2pt</PaddingRight>
> </Style>
> <ZIndex>1</ZIndex>
> <rd:DefaultName>textbox4</rd:DefaultName>
> <CanGrow>true</CanGrow>
> <Value />
> �/Textbox>
> </ReportItems>
> </TableCell>
> <TableCell>
> <ReportItems>
> �Textbox Name="title">
> <Style>
> <PaddingLeft>2pt</PaddingLeft>
> <FontSize>8pt</FontSize>
> <PaddingBottom>2pt</PaddingBottom>
> <PaddingTop>2pt</PaddingTop>
> <PaddingRight>2pt</PaddingRight>
> </Style>
> <rd:DefaultName>title</rd:DefaultName>
> <CanGrow>true</CanGrow>
> <Value>=Fields!title.Value</Value>
> �/Textbox>
> </ReportItems>
> </TableCell>
> </TableCells>
> </TableRow>
> </TableRows>
> </Details>
> <DataSetName>pubs</DataSetName>
> <Top>0.125in</Top>
> <Width>4.33334in</Width>
> <TableGroups>
> <TableGroup>
> <Header>
> <TableRows>
> <TableRow>
> <Height>0.25in</Height>
> <TableCells>
> <TableCell>
> <ReportItems>
> <Textbox Name="pub_id">
> <Style>
> <PaddingLeft>2pt</PaddingLeft>
> <TextDecoratiok>Underline</TextDecoration>
> <FontSize>8pt</FontSize>
> <Color>Crimson</Color>
> <PaddingBottom>2pt</PaddingBottom>
> <PaddingTop>2pt</PaddingTop>
> <PaddingRight>2pt</PaddingRight>
> <FontWeight>700</FontWeight>
> </Style>
> <ZIndex>3</ZIndex>
> <rd:DefaultName>pub_id</rd:DefaultName>
> <Action>
> <Drillthrough>
> <Parameters>
> <Parameter Name="pubid_param">
> <Value>= Fields!pub_id.Value</Value>
> </Parameter>
> </Parameters>
> <ReportName>Child</ReportName>
> </Drillthrough>
> </Action>
> <CanGrow>true</CanGrow>
> <Value>=Fields!pub_id.Value</Value>
> </Textbox>
> </ReportItems>
> </TableCell>
> <TableCell>
> <ReportItems>
> <Textbox Name="textbox6">
> <Style>
> <PaddingLeft>2pt</PaddingLeft>
> <FontSize>8pt</FontSize>
> <PaddingBottom>2pt</PaddingBottom>
> <PaddingTop>2pt</PaddingTop>
> <PaddingRight>2pt</PaddingRight>
> </Style>
> <ZIndex>2</ZIndex>
> <rd:DefaultName>textbox6</rd:DefaultName>
> <CanGrow>true</CanGrow>
> <Value />
> </Textbox>
> </ReportItems>
> </TableCell>
> </TableCells>
> </TableRow>
> </TableRows>
> </Header>
> <Grouping Name="table1_Group1">
> <GroupExpressions>
> <GroupExpression>=Fields!pub_id.Value</GroupExpression>
> </GroupExpressions>
> </Grouping>
> </TableGroup>
> </TableGroups>
> <Left>0.125in</Left>
> <TableColumns>
> <TableColumn>
> <Width>2.16667in</Width>
> </TableColumn>
> <TableColumn>
> <Width>2.16667in</Width>
> </TableColumn>
> </TableColumns>
> </Table>
> </ReportItems>
> <Style />
> <Height>3.125in</Height>
> </Body>
> <TopMargin>1in</TopMargin>
> <DataSources>
> <DataSource Name="pubs">
> <rd:DataSourceID>d9a7bb6a-bc81-4f36-96e6-e27dffdfd71c</rd:DataSourceID>
> <ConnectionProperties>
> <DataProvider>SQL</DataProvider>
> <ConnectString>initial catalog=pubs</ConnectString>
> <IntegratedSecurity>true</IntegratedSecurity>
> </ConnectionProperties>
> </DataSource>
> </DataSources>
> <Width>6.5in</Width>
> <DataSets>
> <DataSet Name="pubs">
> <Fields>
> <Field Name="title">
> <DataField>title</DataField>
> <rd:TypeName>System.String</rd:TypeName>
> </Field>
> <Field Name="type">
> <DataField>type</DataField>
> <rd:TypeName>System.String</rd:TypeName>
> </Field>
> <Field Name="pub_id">
> <DataField>pub_id</DataField>
> <rd:TypeName>System.String</rd:TypeName>
> </Field>
> </Fields>
> <Query>
> <DataSourceName>pubs</DataSourceName>
> <CommandText>select title, type, pub_id from titles</CommandText>
> <rd:UseGenericDesigner>true</rd:UseGenericDesigner>
> </Query>
> </DataSet>
> </DataSets>
> <LeftMargin>1in</LeftMargin>
> <rd:SnapToGrid>true</rd:SnapToGrid>
> <rd:DrawGrid>true</rd:DrawGrid>
> <rd:ReportID>608a77f9-a9e7-41db-ac50-c156ff9bd82e</rd:ReportID>
> <BottomMargin>1in</BottomMargin>
> <Language>en-US</Language>
> </Report>
>
> Child report:
> <?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">
> <RightMargin>1in</RightMargin>
> <Body>
> <ReportItems>
> <Table Name="table1">
> <Height>0.5in</Height>
> <Style />
> <Header>
> <TableRows>
> <TableRow>
> <Height>0.25in</Height>
> <TableCells>
> <TableCell>
> <ReportItems>
> �Textbox Name="textbox1">
> <Style>
> <PaddingLeft>2pt</PaddingLeft>
> <FontSize>8pt</FontSize>
> <PaddingBottom>2pt</PaddingBottom>
> <PaddingTop>2pt</PaddingTop>
> <PaddingRight>2pt</PaddingRight>
> <FontWeight>700</FontWeight>
> </Style>
> <ZIndex>7</ZIndex>
> <rd:DefaultName>textbox1</rd:DefaultName>
> <CanGrow>true</CanGrow>
> <Value>pub name</Value>
> �/Textbox>
> </ReportItems>
> </TableCell>
> <TableCell>
> <ReportItems>
> �Textbox Name="textbox2">
> <Style>
> <PaddingLeft>2pt</PaddingLeft>
> <FontSize>8pt</FontSize>
> <PaddingBottom>2pt</PaddingBottom>
> <PaddingTop>2pt</PaddingTop>
> <PaddingRight>2pt</PaddingRight>
> <FontWeight>700</FontWeight>
> </Style>
> <ZIndex>6</ZIndex>
> <rd:DefaultName>textbox2</rd:DefaultName>
> <CanGrow>true</CanGrow>
> <Value>city</Value>
> �/Textbox>
> </ReportItems>
> </TableCell>
> <TableCell>
> <ReportItems>
> �Textbox Name="textboxk">
> <Style>
> <PaddingLeft>2pt</PaddingLeft>
> <FontSize>8pt</FontSize>
> <PaddingBottom>2pt</PaddingBottom>
> <PaddingTop>2pt</PaddingTop>
> <PaddingRight>2pt</PaddingRight>
> <FontWeight>700</FontWeight>
> </Style>
> <ZIndex>5</ZIndex>
> <rd:DefaultName>textbox3</rd:DefaultName>
> <CanGrow>true</CanGrow>
> <Value>state</Value>
> �/Textbox>
> </ReportItems>
> </TableCell>
> <TableCell>
> <ReportItems>
> �Textbox Name="textbox4">
> <Style>
> <PaddingLeft>2pt</PaddingLeft>
> <FontSize>8pt</FontSize>
> <PaddingBottom>2pt</PaddingBottom>
> <PaddingTop>2pt</PaddingTop>
> <PaddingRight>2pt</PaddingRight>
> <FontWeight>700</FontWeight>
> </Style>
> <ZIndex>4</ZIndex>
> <rd:DefaultName>textbox4</rd:DefaultName>
> <CanGrow>true</CanGrow>
> <Value>country</Value>
> �/Textbox>
> </ReportItems>
> </TableCell>
> </TableCells>
> </TableRow>
> </TableRows>
> </Header>
> <Details>
> <TableRows>
> <TableRow>
> <Height>0.25in</Height>
> <TableCells>
> <TableCell>
> <ReportItems>
> �Textbox Name="pub_name">
> <Style>
> <PaddingLeft>2pt</PaddingLeft>
> <FontSize>8pt</FontSize>
> <PaddingBottom>2pt</PaddingBottom>
> <PaddingTop>2pt</PaddingTop>
> <PaddingRight>2pt</PaddingRight>
> </Style>
> <ZIndex>3</ZIndex>
> <rd:DefaultName>pub_name</rd:DefaultName>
> <CanGrow>true</CanGrow>
> <Value>=Fields!pub_name.Value</Value>
> �/Textbox>
> </ReportItems>
> </TableCell>
> <TableCell>
> <ReportItems>
> �Textbox Name="city">
> <Style>
> <PaddingLeft>2pt</PaddingLeft>
> <FontSize>8pt</FonkSize>
> <PaddingBottom>2pt</PaddingBottom>
> <PaddingTop>2pt</PaddingTop>
> <PaddingRight>2pt</PaddingRight>
> </Style>
> <ZIndex>2</ZIndex>
> <rd:DefaultName>city</rd:DefaultName>
> <CanGrow>true</CanGrow>
> <Value>=Fields!city.Value</Value>
> �/Textbox>
> </ReportItems>
> </TableCell>
> <TableCell>
> <ReportItems>
> �Textbox Name="state">
> <Style>
> <PaddingLeft>2pt</PaddingLeft>
> <FontSize>8pt</FontSize>
> <PaddingBottom>2pt</PaddingBottom>
> <PaddingTop>2pt</PaddingTop>
> <PaddingRight>2pt</PaddingRight>
> </Style>
> <ZIndex>1</ZIndex>
> <rd:DefaultName>state</rd:DefaultName>
> <CanGrow>true</CanGrow>
> <Value>=Fields!state.Value</Value>
> �/Textbox>
> </ReportItems>
> </TableCell>
> <TableCell>
> <ReportItems>
> �Textbox Name="country">
> <Style>
> <PaddingLeft>2pt</PaddingLeft>
> <FontSize>8pt</FontSize>
> <PaddingBottom>2pt</PaddingBottom>
> <PaddingTop>2pt</PaddingTop>
> <PaddingRight>2pt</PaddingRight>
> </Style>
> <rd:DefaultName>country</rd:DefaultName>
> <CanGrow>true</CanGrow>
> <Value>=Fields!country.Value</Value>
> �/Textbox>
> </ReportItems>
> </TableCell>
> </TableCells>
> </TableRow>
> </TableRows>
> </Details>
> <DataSetName>pubs</DataSetName>
> <TableColumns>
> <TableColumn>
> <Width>2.16667in</Width>
> </TableColumn>
> <TableColumn>
> <Width>2.16667in</Width>
> </TableColumn>
> <TableColumn>
> <Width>2.16667in</Width>
> </TableColumn>
> <TableColumn>
> <Width>2.16667in</Width>
> </TableColumn>
> </TableColumns>
> </Table>
> </ReportItems>
> <Style />
> <Height>2in</Height>
> </Body>
> <TopMargin>1in</TopMargin>
> <DataSources>
> <DataSource Name="pubs">
> <rd:DataSourceID>d6f77576-e36c-45dc-877c-a28464b055e8</rd:DataSourceID>
> <ConnectionProperties>
> <DataProvider>SQL</DataProvider>
> <ConnectString>initial catalog=pubs</ConnectString>
> <IntegratedSecurity>true</IntegratedSecurity>
> </ConnectionProperties>
> </DataSource>
> </DataSources>
> <Width>8.66667in</Width>
> <DataSets>
> <DataSet Name="pubs">
> <Fields>
> <Field Name="pub_id">
> <DataField>pub_id</DataField>
> <rd:TypeName>System.String</rd:TypeName>
> </Field>
> <Field Name="pub_name">
> <DataField>pub_name</DataField>
> <rd:TypeName>System.String</rd:TypeName>
> </Field>
> <Field Name="city">
> <DataField>city</DataField>
> <rd:TypeName>System.String</rd:TypeName>
> </Field>
> <Field Name="state">
> <DataField>state</DataField>
> <rd:TypeName>System.String</rd:TypeName>
> </Field>
> <Field Name="country">
> <DataField>country</DataField>
> <rd:TypeName>System.String</rd:TypeName>
> </Field>
> </Fields>
> <Query>
> <DataSourceName>pubs</DataSourceName>
> <CommandType>StoredProcedure</CommandType>
> <CommandText>GetPublisherDetails</CommandText>
> <QueryParameters>
> <QueryParameter Name="@.pubid_param">
> <Value>=Parameters!pubid_param.Value</Value>
> </QueryParameter>
> </QueryParameters>
> </Query>
> </DataSet>
> </DataSets>
> <LeftMargin>1in</LeftMargin>
> <rd:SnapToGrid>true</rd:SnapToGrid>
> <rd:DrawGrid>true</rd:DrawGrid>
> <rd:ReportID>ab635794-d5b4-4ac8-adc6-4891acac8879</rd:ReportID>
> <BottomMargin>1in</BottomMargin>
> <ReportParameters>
> <ReportParameter Name="pubid_param">
> <DataType>Integer</DataType>
> <Prompt>pubid_param</Prompt>
> </ReportParameter>
> </ReportParameters>
> <Language>en-US</Language>
> </Report>
> --
> Ravi Mumulla (Microsoft)
> SQL Server Reporting Services
> This posting is provided "AS IS" with no warranties, and confers no
rights.
> "Johnny Fugazzi" <abillmeier@.ldmkusa.com> wrote in message
> news:eAR8Ma8fEHA.216@.tk2msftngp13.phx.gbl...
> > Ok, I am attempting to create my first report in SQL Reporting services.
> I
> > am using SQL Enterprise 2000 sp3, VS.NET 2003, and IIS5.
> >
> > I have created a report that has several columns of general information.
> > One of the columns is "employee_id" This report worked fine up to this
> > point.
> >
> > I then created a subreport that pulls some employee information based on
> the
> > employee_id. This report uses a stored procedure to get the data, and
the
> > stored proc parameter is called @.employeeID.
> >
> > I have placed the subreport on the master report, set its report name to
> the
> > name of the sub report, and set it's passed parameter to the
"employee_id"
> > field of the master report.
> >
> > In the subreport, I have set the dataset's employee_id parameter to the
> > subreport's employee_id parameter.
> >
> > When I run the master report I receive an error stating that the stored
> proc
> > used by the sub report did not receive it's value. So, it appears that
> the
> > report parameters are not making it to the stored proc... What am I
> > missing? Is there something special that I need to do to make the
> > subreports parameter pass on to the stored proc used by the subreport?
> >
> > any help would be appreciated.
> >
> > thanks in advance.
> >
> >
> >
>
Posted by squeakqwes at 4:48 AM
Labels: , , , , , , , , , , , , , , , ,

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)