Good day,
I would like to know if it is possible to do the follwoing.
I have a report that has 3 parameters. What I would like to do is when the
user seects the first parameter the second parameter drop box will be
populated with options available according to what was selected in parameter
1, and then once parameter 2 has been selected parameter 3 drop down box will
be populated with the options available to it based on parameter 1 and 2.
Thanks in advance.try to see this link. the part you are interessted in it is called cascading
parameter
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/rscreate/htm/rcr_creating_interactive_v1_50fn.asp
best regards, Mirela
"PLSH" wrote:
> Good day,
> I would like to know if it is possible to do the follwoing.
> I have a report that has 3 parameters. What I would like to do is when the
> user seects the first parameter the second parameter drop box will be
> populated with options available according to what was selected in parameter
> 1, and then once parameter 2 has been selected parameter 3 drop down box will
> be populated with the options available to it based on parameter 1 and 2.
> Thanks in advance.|||At the end of this report is a sample of how to use cascading parameters.
--
Bruce Johnson [MSFT]
Microsoft SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"PLSH" <PLSH@.discussions.microsoft.com> wrote in message
news:181419DA-37DA-469C-8F1E-1B684F1F8F05@.microsoft.com...
> Good day,
> I would like to know if it is possible to do the follwoing.
> I have a report that has 3 parameters. What I would like to do is when the
> user seects the first parameter the second parameter drop box will be
> populated with options available according to what was selected in
parameter
> 1, and then once parameter 2 has been selected parameter 3 drop down box
will
> be populated with the options available to it based on parameter 1 and 2.
> Thanks in advance.
CascadingParameters.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">
<RightMargin>1in</RightMargin>
<Body>
<ReportItems>
<Table Name="table1">
<Style />
<Header>
<TableRows>
<TableRow>
<Height>0.25in</Height>
<TableCells>
<TableCell>
<ReportItems>
<Textbox Name="textbox1">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<TextAlign>Right</TextAlign>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>8</ZIndex>
<rd:DefaultName>textbox1</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value>Employee ID</Value>
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox2">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>7</ZIndex>
<rd:DefaultName>textbox2</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value>First Name</Value>
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox3">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>6</ZIndex>
<rd:DefaultName>textbox3</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value>LastName</Value>
</Textbox>
</ReportItems>
</TableCell>
</TableCells>
</TableRow>
</TableRows>
</Header>
<Details>
<TableRows>
<TableRow>
<Height>0.25in</Height>
<TableCells>
<TableCell>
<ReportItems>
<Textbox Name="EmployeeID">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<TextAlign>Right</TextAlign>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>2</ZIndex>
<rd:DefaultName>EmployeeID</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value>=Fields!EmployeeID.Value</Value>
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="FirstName">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>1</ZIndex>
<rd:DefaultName>FirstName</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value>=Fields!FirstName.Value</Value>
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="LastName">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<rd:DefaultName>LastName</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value>=Fields!LastName.Value</Value>
</Textbox>
</ReportItems>
</TableCell>
</TableCells>
</TableRow>
</TableRows>
</Details>
<DataSetName>EmpInfo</DataSetName>
<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>5</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>4</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>3</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>0.75in</Height>
</Body>
<TopMargin>1in</TopMargin>
<DataSources>
<DataSource Name="Northwind">
<rd:DataSourceID>a9041117-655b-4d91-b2ee-0c0326bce962</rd:DataSourceID>
<ConnectionProperties>
<DataProvider>SQL</DataProvider>
<ConnectString>data source=localhost;initial
catalog=Northwind</ConnectString>
<IntegratedSecurity>true</IntegratedSecurity>
</ConnectionProperties>
</DataSource>
</DataSources>
<Width>6.50001in</Width>
<DataSets>
<DataSet Name="EmpCountry">
<Fields>
<Field Name="country">
<DataField>country</DataField>
<rd:TypeName>System.String</rd:TypeName>
</Field>
</Fields>
<Query>
<DataSourceName>Northwind</DataSourceName>
<CommandText>select Distinct country from employees order by
country</CommandText>
<rd:UseGenericDesigner>true</rd:UseGenericDesigner>
</Query>
</DataSet>
<DataSet Name="EmpCity">
<Fields>
<Field Name="city">
<DataField>City</DataField>
<rd:TypeName>System.String</rd:TypeName>
</Field>
</Fields>
<Query>
<DataSourceName>Northwind</DataSourceName>
<CommandText>SELECT DISTINCT City
FROM Employees
WHERE (Country = @.Country)
ORDER BY City</CommandText>
<QueryParameters>
<QueryParameter Name="@.Country">
<Value>=Parameters!Country.Value</Value>
</QueryParameter>
</QueryParameters>
</Query>
</DataSet>
<DataSet Name="EmpHireDate">
<Fields>
<Field Name="HireYear">
<DataField>HireYear</DataField>
<rd:TypeName>System.Int32</rd:TypeName>
</Field>
</Fields>
<Query>
<DataSourceName>Northwind</DataSourceName>
<CommandText>SELECT DISTINCT DATEPART(year, HireDate) AS HireYear
FROM Employees
ORDER BY DATEPART(year, HireDate)</CommandText>
</Query>
</DataSet>
<DataSet Name="EmpInfo">
<Fields>
<Field Name="EmployeeID">
<DataField>EmployeeID</DataField>
<rd:TypeName>System.Int32</rd:TypeName>
</Field>
<Field Name="LastName">
<DataField>LastName</DataField>
<rd:TypeName>System.String</rd:TypeName>
</Field>
<Field Name="FirstName">
<DataField>FirstName</DataField>
<rd:TypeName>System.String</rd:TypeName>
</Field>
<Field Name="Country">
<DataField>Country</DataField>
<rd:TypeName>System.String</rd:TypeName>
</Field>
<Field Name="City">
<DataField>City</DataField>
<rd:TypeName>System.String</rd:TypeName>
</Field>
<Field Name="HireDate">
<DataField>HireDate</DataField>
<rd:TypeName>System.Int32</rd:TypeName>
</Field>
</Fields>
<Query>
<DataSourceName>Northwind</DataSourceName>
<CommandText>SELECT EmployeeID, LastName, FirstName, Country,
City, DATEPART(year, HireDate) AS HireDate
FROM Employees
WHERE (Country = @.Country) AND (City = @.City) AND (DATEPART(year,
HireDate) = @.HireDate)</CommandText>
<QueryParameters>
<QueryParameter Name="@.Country">
<Value>=Parameters!Country.Value</Value>
</QueryParameter>
<QueryParameter Name="@.City">
<Value>=Parameters!City.Value</Value>
</QueryParameter>
<QueryParameter Name="@.HireDate">
<Value>=Parameters!HireDate.Value</Value>
</QueryParameter>
</QueryParameters>
</Query>
</DataSet>
</DataSets>
<LeftMargin>1in</LeftMargin>
<rd:SnapToGrid>true</rd:SnapToGrid>
<rd:DrawGrid>true</rd:DrawGrid>
<rd:ReportID>c15c3bb5-93d3-4dd9-a14b-ebac4a86966a</rd:ReportID>
<BottomMargin>1in</BottomMargin>
<ReportParameters>
<ReportParameter Name="Country">
<DataType>String</DataType>
<Prompt>Country</Prompt>
<ValidValues>
<DataSetReference>
<DataSetName>EmpCountry</DataSetName>
<ValueField>country</ValueField>
<LabelField>country</LabelField>
</DataSetReference>
</ValidValues>
</ReportParameter>
<ReportParameter Name="City">
<DataType>String</DataType>
<Prompt>City</Prompt>
<ValidValues>
<DataSetReference>
<DataSetName>EmpCity</DataSetName>
<ValueField>city</ValueField>
<LabelField>city</LabelField>
</DataSetReference>
</ValidValues>
</ReportParameter>
<ReportParameter Name="HireDate">
<DataType>String</DataType>
<Prompt>HireDate</Prompt>
<ValidValues>
<DataSetReference>
<DataSetName>EmpHireDate</DataSetName>
<ValueField>HireYear</ValueField>
<LabelField>HireYear</LabelField>
</DataSetReference>
</ValidValues>
</ReportParameter>
</ReportParameters>
<Language>en-US</Language>
</Report>sql
Subscribe to:
Post Comments (Atom)
No comments:
Post a Comment