Thursday, March 22, 2012

Dynamic Parameter

My DB has 3 tables Employee, Department, Job
I want to create a report with two parameters.
Parameter 1 Options: Employee, Department or Job
Parameter 2 Options: When user selects Employee from parameter 1, all
employee names will be made available to select in parameter 2, When user
selects department from parameter 1, all department names will be made
available to select in parameter 2.
How would one doe this? Any help is appreciated.I did this for a Northwind DB. Here is how my RDL looked. Multiple queries
and data sets returned.
I changed the names of my data source and data sets. It should still work
against a Northwind DB.
<?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">
<DataSources>
<DataSource Name="ds1">
<rd:DataSourceID>2122d420-3823-467b-8d49-89744c792684</rd:DataSourceID>
<ConnectionProperties>
<DataProvider>SQL</DataProvider>
<ConnectString>data source=servername;initial
catalog=Northwind</ConnectString>
</ConnectionProperties>
</DataSource>
</DataSources>
<rd:ReportID>48c77212-85fb-46c9-9347-e0ecf74aa394</rd:ReportID>
<Body>
<ReportItems>
<Textbox Name="textbox_2">
<Style>
<BackgroundColor>Yellow</BackgroundColor>
<TextAlign>Left</TextAlign>
<FontSize>9pt</FontSize>
</Style>
<Height>.5 in</Height>
<Width>3 in</Width>
<CanGrow>true</CanGrow>
<Value>=Fields!TestResult.Value
+ "********Parameters CustomerIDParam Value: " +
Parameters!CustomerIDParam.Value
+ "********Parameters EmployeeIDParam Value: " +
Str(Parameters!EmployeeIDParam.Value)
</Value>
<Top>.5 in</Top>
<Left>0 in</Left>
</Textbox>
<Textbox Name="textbox_1">
<Style>
<BackgroundColor>Red</BackgroundColor>
<FontSize>14pt</FontSize>
</Style>
<Height>.5 in</Height>
<Width>3 in</Width>
<CanGrow>true</CanGrow>
<Value>Test Result Below:</Value>
<Top>0 in</Top>
<Left>0 in</Left>
</Textbox>
</ReportItems>
<Height>1.705in</Height>
</Body>
<rd:DrawGrid>true</rd:DrawGrid>
<DataSets>
<DataSet Name="mydataset">
<Fields>
<Field Name="TestResult">
<DataField>TestResult</DataField>
<rd:TypeName>System.String</rd:TypeName>
</Field>
<Field Name="CustomerID">
<DataField>CustomerID</DataField>
<rd:TypeName>System.String</rd:TypeName>
</Field>
<Field Name="EmployeeID">
<DataField>EmployeeID</DataField>
<rd:TypeName>System.Int32</rd:TypeName>
</Field>
</Fields>
<Query>
<DataSourceName>ds1</DataSourceName>
<CommandType>Text</CommandType>
<CommandText>SELECT * , 'Test Passed' as "TestResult" FROM Orders
WHERE EmployeeID = @.EmployeeIDParam AND CustomerID =@.CustomerIDParam</CommandText>
<rd:UseGenericDesigner>true</rd:UseGenericDesigner>
<QueryParameters>
<QueryParameter Name="@.EmployeeIDParam">
<Value>=Parameters!EmployeeIDParam.Value</Value>
</QueryParameter>
<QueryParameter Name="@.CustomerIDParam">
<Value>=Parameters!CustomerIDParam.Value</Value>
</QueryParameter>
</QueryParameters>
</Query>
</DataSet>
<DataSet Name="mydataset2">
<Fields>
<Field Name="EmployeeID">
<DataField>EmployeeID</DataField>
<rd:TypeName>System.Int32</rd:TypeName>
</Field>
</Fields>
<Query>
<DataSourceName>ds1</DataSourceName>
<CommandType>Text</CommandType>
<CommandText>SELECT DISTINCT EmployeeID FROM Employees
ORDER BY EmployeeID ASC</CommandText>
<rd:UseGenericDesigner>true</rd:UseGenericDesigner>
</Query>
</DataSet>
<DataSet Name="mydataset3">
<Fields>
<Field Name="CustomerID">
<DataField>CustomerID</DataField>
<rd:TypeName>System.String</rd:TypeName>
</Field>
</Fields>
<Query>
<DataSourceName>ds1</DataSourceName>
<CommandType>Text</CommandType>
<CommandText>SELECT DISTINCT CustomerID FROM Orders
WHERE EmployeeID = @.EmployeeIDParam ORDER BY CustomerID ASC</CommandText>
<rd:UseGenericDesigner>true</rd:UseGenericDesigner>
<QueryParameters>
<QueryParameter Name="@.EmployeeIDParam">
<Value>=Parameters!EmployeeIDParam.Value</Value>
</QueryParameter>
</QueryParameters>
</Query>
</DataSet>
</DataSets>
<Language>en-US</Language>
<RightMargin>1in</RightMargin>
<BottomMargin>1in</BottomMargin>
<TopMargin>1in</TopMargin>
<rd:SnapToGrid>true</rd:SnapToGrid>
<Width>5in</Width>
<LeftMargin>1in</LeftMargin>
<ReportParameters>
<ReportParameter Name="EmployeeID">
<DataType>Integer</DataType>
<DefaultValue>
<Values>
<Value>1</Value>
</Values>
</DefaultValue>
<ValidValues>
<DataSetReference>
<DataSetName>mydataset2</DataSetName>
<ValueField>EmployeeID</ValueField>
<LabelField>EmployeeID</LabelField>
</DataSetReference>
</ValidValues>
<Prompt>Employee ID :</Prompt>
</ReportParameter>
<ReportParameter Name="EmployeeIDParam">
<DataType>Integer</DataType>
<DefaultValue>
<Values>
<Value>=Parameters!EmployeeID.Value</Value>
</Values>
</DefaultValue>
<Prompt> </Prompt>
</ReportParameter>
<ReportParameter Name="CustomerIDParam">
<DataType>String</DataType>
<DefaultValue>
<Values>
<Value>VINET</Value>
</Values>
</DefaultValue>
<ValidValues>
<DataSetReference>
<DataSetName>mydataset3</DataSetName>
<ValueField>CustomerID</ValueField>
<LabelField>CustomerID</LabelField>
</DataSetReference>
</ValidValues>
<Prompt>Customer ID :</Prompt>
</ReportParameter>
</ReportParameters>
</Report>
--
| Thread-Topic: Dynamic Parameter
| thread-index: AcUFhs+wyP3Cs1ONTxSLxYdNcM59XQ==| X-WBNR-Posting-Host: 70.66.0.254
| From: "=?Utf-8?B?U0FjYW51Y2s=?=" <SAcanuck@.discussions.microsoft.com>
| Subject: Dynamic Parameter
| Date: Fri, 28 Jan 2005 14:15:01 -0800
| Lines: 12
| Message-ID: <AE5AB9C9-7340-4ED3-9EC6-3F5DB22834AA@.microsoft.com>
| MIME-Version: 1.0
| Content-Type: text/plain;
| charset="Utf-8"
| Content-Transfer-Encoding: 7bit
| X-Newsreader: Microsoft CDO for Windows 2000
| Content-Class: urn:content-classes:message
| Importance: normal
| Priority: normal
| X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.0
| Newsgroups: microsoft.public.sqlserver.reportingsvcs
| NNTP-Posting-Host: TK2MSFTNGXA03.phx.gbl 10.40.1.29
| Path: cpmsftngxa10.phx.gbl!TK2MSFTNGXA03.phx.gbl
| Xref: cpmsftngxa10.phx.gbl microsoft.public.sqlserver.reportingsvcs:41264
| X-Tomcat-NG: microsoft.public.sqlserver.reportingsvcs
|
| My DB has 3 tables Employee, Department, Job
|
| I want to create a report with two parameters.
|
| Parameter 1 Options: Employee, Department or Job
|
| Parameter 2 Options: When user selects Employee from parameter 1, all
| employee names will be made available to select in parameter 2, When
user
| selects department from parameter 1, all department names will be made
| available to select in parameter 2.
|
| How would one doe this? Any help is appreciated.
||||It is not necessary to dive into this RDL code.
Go to the online help from Reporting Services. There is a chapter Create
Report (or similare) and there is a description of using Parameters. On the
end of this article is your question answered in the best way.
""Brad Syputa - MS"" wrote:
> I did this for a Northwind DB. Here is how my RDL looked. Multiple queries
> and data sets returned.
> I changed the names of my data source and data sets. It should still work
> against a Northwind DB.
> <?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">
> <DataSources>
> <DataSource Name="ds1">
> <rd:DataSourceID>2122d420-3823-467b-8d49-89744c792684</rd:DataSourceID>
> <ConnectionProperties>
> <DataProvider>SQL</DataProvider>
> <ConnectString>data source=servername;initial
> catalog=Northwind</ConnectString>
> </ConnectionProperties>
> </DataSource>
> </DataSources>
> <rd:ReportID>48c77212-85fb-46c9-9347-e0ecf74aa394</rd:ReportID>
> <Body>
> <ReportItems>
> <Textbox Name="textbox_2">
> <Style>
> <BackgroundColor>Yellow</BackgroundColor>
> <TextAlign>Left</TextAlign>
> <FontSize>9pt</FontSize>
> </Style>
> <Height>.5 in</Height>
> <Width>3 in</Width>
> <CanGrow>true</CanGrow>
> <Value>=Fields!TestResult.Value
> + "********Parameters CustomerIDParam Value: " +
> Parameters!CustomerIDParam.Value
> + "********Parameters EmployeeIDParam Value: " +
> Str(Parameters!EmployeeIDParam.Value)
> </Value>
> <Top>.5 in</Top>
> <Left>0 in</Left>
> </Textbox>
> <Textbox Name="textbox_1">
> <Style>
> <BackgroundColor>Red</BackgroundColor>
> <FontSize>14pt</FontSize>
> </Style>
> <Height>.5 in</Height>
> <Width>3 in</Width>
> <CanGrow>true</CanGrow>
> <Value>Test Result Below:</Value>
> <Top>0 in</Top>
> <Left>0 in</Left>
> </Textbox>
> </ReportItems>
> <Height>1.705in</Height>
> </Body>
> <rd:DrawGrid>true</rd:DrawGrid>
> <DataSets>
> <DataSet Name="mydataset">
> <Fields>
> <Field Name="TestResult">
> <DataField>TestResult</DataField>
> <rd:TypeName>System.String</rd:TypeName>
> </Field>
> <Field Name="CustomerID">
> <DataField>CustomerID</DataField>
> <rd:TypeName>System.String</rd:TypeName>
> </Field>
> <Field Name="EmployeeID">
> <DataField>EmployeeID</DataField>
> <rd:TypeName>System.Int32</rd:TypeName>
> </Field>
> </Fields>
> <Query>
> <DataSourceName>ds1</DataSourceName>
> <CommandType>Text</CommandType>
> <CommandText>SELECT * , 'Test Passed' as "TestResult" FROM Orders
> WHERE EmployeeID = @.EmployeeIDParam AND CustomerID => @.CustomerIDParam</CommandText>
> <rd:UseGenericDesigner>true</rd:UseGenericDesigner>
> <QueryParameters>
> <QueryParameter Name="@.EmployeeIDParam">
> <Value>=Parameters!EmployeeIDParam.Value</Value>
> </QueryParameter>
> <QueryParameter Name="@.CustomerIDParam">
> <Value>=Parameters!CustomerIDParam.Value</Value>
> </QueryParameter>
> </QueryParameters>
> </Query>
> </DataSet>
> <DataSet Name="mydataset2">
> <Fields>
> <Field Name="EmployeeID">
> <DataField>EmployeeID</DataField>
> <rd:TypeName>System.Int32</rd:TypeName>
> </Field>
> </Fields>
> <Query>
> <DataSourceName>ds1</DataSourceName>
> <CommandType>Text</CommandType>
> <CommandText>SELECT DISTINCT EmployeeID FROM Employees
> ORDER BY EmployeeID ASC</CommandText>
> <rd:UseGenericDesigner>true</rd:UseGenericDesigner>
> </Query>
> </DataSet>
> <DataSet Name="mydataset3">
> <Fields>
> <Field Name="CustomerID">
> <DataField>CustomerID</DataField>
> <rd:TypeName>System.String</rd:TypeName>
> </Field>
> </Fields>
> <Query>
> <DataSourceName>ds1</DataSourceName>
> <CommandType>Text</CommandType>
> <CommandText>SELECT DISTINCT CustomerID FROM Orders
> WHERE EmployeeID = @.EmployeeIDParam ORDER BY CustomerID ASC</CommandText>
> <rd:UseGenericDesigner>true</rd:UseGenericDesigner>
> <QueryParameters>
> <QueryParameter Name="@.EmployeeIDParam">
> <Value>=Parameters!EmployeeIDParam.Value</Value>
> </QueryParameter>
> </QueryParameters>
> </Query>
> </DataSet>
> </DataSets>
> <Language>en-US</Language>
> <RightMargin>1in</RightMargin>
> <BottomMargin>1in</BottomMargin>
> <TopMargin>1in</TopMargin>
> <rd:SnapToGrid>true</rd:SnapToGrid>
> <Width>5in</Width>
> <LeftMargin>1in</LeftMargin>
> <ReportParameters>
> <ReportParameter Name="EmployeeID">
> <DataType>Integer</DataType>
> <DefaultValue>
> <Values>
> <Value>1</Value>
> </Values>
> </DefaultValue>
> <ValidValues>
> <DataSetReference>
> <DataSetName>mydataset2</DataSetName>
> <ValueField>EmployeeID</ValueField>
> <LabelField>EmployeeID</LabelField>
> </DataSetReference>
> </ValidValues>
> <Prompt>Employee ID :</Prompt>
> </ReportParameter>
> <ReportParameter Name="EmployeeIDParam">
> <DataType>Integer</DataType>
> <DefaultValue>
> <Values>
> <Value>=Parameters!EmployeeID.Value</Value>
> </Values>
> </DefaultValue>
> <Prompt> </Prompt>
> </ReportParameter>
> <ReportParameter Name="CustomerIDParam">
> <DataType>String</DataType>
> <DefaultValue>
> <Values>
> <Value>VINET</Value>
> </Values>
> </DefaultValue>
> <ValidValues>
> <DataSetReference>
> <DataSetName>mydataset3</DataSetName>
> <ValueField>CustomerID</ValueField>
> <LabelField>CustomerID</LabelField>
> </DataSetReference>
> </ValidValues>
> <Prompt>Customer ID :</Prompt>
> </ReportParameter>
> </ReportParameters>
> </Report>
> --
> | Thread-Topic: Dynamic Parameter
> | thread-index: AcUFhs+wyP3Cs1ONTxSLxYdNcM59XQ==> | X-WBNR-Posting-Host: 70.66.0.254
> | From: "=?Utf-8?B?U0FjYW51Y2s=?=" <SAcanuck@.discussions.microsoft.com>
> | Subject: Dynamic Parameter
> | Date: Fri, 28 Jan 2005 14:15:01 -0800
> | Lines: 12
> | Message-ID: <AE5AB9C9-7340-4ED3-9EC6-3F5DB22834AA@.microsoft.com>
> | MIME-Version: 1.0
> | Content-Type: text/plain;
> | charset="Utf-8"
> | Content-Transfer-Encoding: 7bit
> | X-Newsreader: Microsoft CDO for Windows 2000
> | Content-Class: urn:content-classes:message
> | Importance: normal
> | Priority: normal
> | X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.0
> | Newsgroups: microsoft.public.sqlserver.reportingsvcs
> | NNTP-Posting-Host: TK2MSFTNGXA03.phx.gbl 10.40.1.29
> | Path: cpmsftngxa10.phx.gbl!TK2MSFTNGXA03.phx.gbl
> | Xref: cpmsftngxa10.phx.gbl microsoft.public.sqlserver.reportingsvcs:41264
> | X-Tomcat-NG: microsoft.public.sqlserver.reportingsvcs
> |
> | My DB has 3 tables Employee, Department, Job
> |
> | I want to create a report with two parameters.
> |
> | Parameter 1 Options: Employee, Department or Job
> |
> | Parameter 2 Options: When user selects Employee from parameter 1, all
> | employee names will be made available to select in parameter 2, When
> user
> | selects department from parameter 1, all department names will be made
> | available to select in parameter 2.
> |
> | How would one doe this? Any help is appreciated.
> |
>|||I had a look at the reporting services books online and it had a description
on how to use a basiv dynamic parameter...my requirement is a bit more
advanced than the given example.
"Dev Main" wrote:
> It is not necessary to dive into this RDL code.
> Go to the online help from Reporting Services. There is a chapter Create
> Report (or similare) and there is a description of using Parameters. On the
> end of this article is your question answered in the best way.
>
> ""Brad Syputa - MS"" wrote:
> > I did this for a Northwind DB. Here is how my RDL looked. Multiple queries
> > and data sets returned.
> >
> > I changed the names of my data source and data sets. It should still work
> > against a Northwind DB.
> >
> > <?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">
> > <DataSources>
> > <DataSource Name="ds1">
> >
> > <rd:DataSourceID>2122d420-3823-467b-8d49-89744c792684</rd:DataSourceID>
> > <ConnectionProperties>
> > <DataProvider>SQL</DataProvider>
> > <ConnectString>data source=servername;initial
> > catalog=Northwind</ConnectString>
> > </ConnectionProperties>
> > </DataSource>
> > </DataSources>
> > <rd:ReportID>48c77212-85fb-46c9-9347-e0ecf74aa394</rd:ReportID>
> > <Body>
> > <ReportItems>
> > <Textbox Name="textbox_2">
> > <Style>
> > <BackgroundColor>Yellow</BackgroundColor>
> > <TextAlign>Left</TextAlign>
> > <FontSize>9pt</FontSize>
> > </Style>
> > <Height>.5 in</Height>
> > <Width>3 in</Width>
> > <CanGrow>true</CanGrow>
> > <Value>=Fields!TestResult.Value
> > + "********Parameters CustomerIDParam Value: " +
> > Parameters!CustomerIDParam.Value
> > + "********Parameters EmployeeIDParam Value: " +
> > Str(Parameters!EmployeeIDParam.Value)
> > </Value>
> > <Top>.5 in</Top>
> > <Left>0 in</Left>
> > </Textbox>
> > <Textbox Name="textbox_1">
> > <Style>
> > <BackgroundColor>Red</BackgroundColor>
> > <FontSize>14pt</FontSize>
> > </Style>
> > <Height>.5 in</Height>
> > <Width>3 in</Width>
> > <CanGrow>true</CanGrow>
> > <Value>Test Result Below:</Value>
> > <Top>0 in</Top>
> > <Left>0 in</Left>
> > </Textbox>
> > </ReportItems>
> > <Height>1.705in</Height>
> > </Body>
> > <rd:DrawGrid>true</rd:DrawGrid>
> > <DataSets>
> > <DataSet Name="mydataset">
> > <Fields>
> > <Field Name="TestResult">
> > <DataField>TestResult</DataField>
> > <rd:TypeName>System.String</rd:TypeName>
> > </Field>
> > <Field Name="CustomerID">
> > <DataField>CustomerID</DataField>
> > <rd:TypeName>System.String</rd:TypeName>
> > </Field>
> > <Field Name="EmployeeID">
> > <DataField>EmployeeID</DataField>
> > <rd:TypeName>System.Int32</rd:TypeName>
> > </Field>
> > </Fields>
> > <Query>
> > <DataSourceName>ds1</DataSourceName>
> > <CommandType>Text</CommandType>
> > <CommandText>SELECT * , 'Test Passed' as "TestResult" FROM Orders
> > WHERE EmployeeID = @.EmployeeIDParam AND CustomerID => > @.CustomerIDParam</CommandText>
> > <rd:UseGenericDesigner>true</rd:UseGenericDesigner>
> > <QueryParameters>
> > <QueryParameter Name="@.EmployeeIDParam">
> > <Value>=Parameters!EmployeeIDParam.Value</Value>
> > </QueryParameter>
> > <QueryParameter Name="@.CustomerIDParam">
> > <Value>=Parameters!CustomerIDParam.Value</Value>
> > </QueryParameter>
> > </QueryParameters>
> > </Query>
> > </DataSet>
> > <DataSet Name="mydataset2">
> > <Fields>
> > <Field Name="EmployeeID">
> > <DataField>EmployeeID</DataField>
> > <rd:TypeName>System.Int32</rd:TypeName>
> > </Field>
> > </Fields>
> > <Query>
> > <DataSourceName>ds1</DataSourceName>
> > <CommandType>Text</CommandType>
> > <CommandText>SELECT DISTINCT EmployeeID FROM Employees
> > ORDER BY EmployeeID ASC</CommandText>
> > <rd:UseGenericDesigner>true</rd:UseGenericDesigner>
> > </Query>
> > </DataSet>
> > <DataSet Name="mydataset3">
> > <Fields>
> > <Field Name="CustomerID">
> > <DataField>CustomerID</DataField>
> > <rd:TypeName>System.String</rd:TypeName>
> > </Field>
> > </Fields>
> > <Query>
> > <DataSourceName>ds1</DataSourceName>
> > <CommandType>Text</CommandType>
> > <CommandText>SELECT DISTINCT CustomerID FROM Orders
> > WHERE EmployeeID = @.EmployeeIDParam ORDER BY CustomerID ASC</CommandText>
> > <rd:UseGenericDesigner>true</rd:UseGenericDesigner>
> > <QueryParameters>
> > <QueryParameter Name="@.EmployeeIDParam">
> > <Value>=Parameters!EmployeeIDParam.Value</Value>
> > </QueryParameter>
> > </QueryParameters>
> > </Query>
> > </DataSet>
> > </DataSets>
> > <Language>en-US</Language>
> > <RightMargin>1in</RightMargin>
> > <BottomMargin>1in</BottomMargin>
> > <TopMargin>1in</TopMargin>
> > <rd:SnapToGrid>true</rd:SnapToGrid>
> > <Width>5in</Width>
> > <LeftMargin>1in</LeftMargin>
> > <ReportParameters>
> > <ReportParameter Name="EmployeeID">
> > <DataType>Integer</DataType>
> > <DefaultValue>
> > <Values>
> > <Value>1</Value>
> > </Values>
> > </DefaultValue>
> > <ValidValues>
> > <DataSetReference>
> > <DataSetName>mydataset2</DataSetName>
> > <ValueField>EmployeeID</ValueField>
> > <LabelField>EmployeeID</LabelField>
> > </DataSetReference>
> > </ValidValues>
> > <Prompt>Employee ID :</Prompt>
> > </ReportParameter>
> > <ReportParameter Name="EmployeeIDParam">
> > <DataType>Integer</DataType>
> > <DefaultValue>
> > <Values>
> > <Value>=Parameters!EmployeeID.Value</Value>
> > </Values>
> > </DefaultValue>
> > <Prompt> </Prompt>
> > </ReportParameter>
> > <ReportParameter Name="CustomerIDParam">
> > <DataType>String</DataType>
> > <DefaultValue>
> > <Values>
> > <Value>VINET</Value>
> > </Values>
> > </DefaultValue>
> > <ValidValues>
> > <DataSetReference>
> > <DataSetName>mydataset3</DataSetName>
> > <ValueField>CustomerID</ValueField>
> > <LabelField>CustomerID</LabelField>
> > </DataSetReference>
> > </ValidValues>
> > <Prompt>Customer ID :</Prompt>
> > </ReportParameter>
> > </ReportParameters>
> > </Report>
> > --
> > | Thread-Topic: Dynamic Parameter
> > | thread-index: AcUFhs+wyP3Cs1ONTxSLxYdNcM59XQ==> > | X-WBNR-Posting-Host: 70.66.0.254
> > | From: "=?Utf-8?B?U0FjYW51Y2s=?=" <SAcanuck@.discussions.microsoft.com>
> > | Subject: Dynamic Parameter
> > | Date: Fri, 28 Jan 2005 14:15:01 -0800
> > | Lines: 12
> > | Message-ID: <AE5AB9C9-7340-4ED3-9EC6-3F5DB22834AA@.microsoft.com>
> > | MIME-Version: 1.0
> > | Content-Type: text/plain;
> > | charset="Utf-8"
> > | Content-Transfer-Encoding: 7bit
> > | X-Newsreader: Microsoft CDO for Windows 2000
> > | Content-Class: urn:content-classes:message
> > | Importance: normal
> > | Priority: normal
> > | X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.0
> > | Newsgroups: microsoft.public.sqlserver.reportingsvcs
> > | NNTP-Posting-Host: TK2MSFTNGXA03.phx.gbl 10.40.1.29
> > | Path: cpmsftngxa10.phx.gbl!TK2MSFTNGXA03.phx.gbl
> > | Xref: cpmsftngxa10.phx.gbl microsoft.public.sqlserver.reportingsvcs:41264
> > | X-Tomcat-NG: microsoft.public.sqlserver.reportingsvcs
> > |
> > | My DB has 3 tables Employee, Department, Job
> > |
> > | I want to create a report with two parameters.
> > |
> > | Parameter 1 Options: Employee, Department or Job
> > |
> > | Parameter 2 Options: When user selects Employee from parameter 1, all
> > | employee names will be made available to select in parameter 2, When
> > user
> > | selects department from parameter 1, all department names will be made
> > | available to select in parameter 2.
> > |
> > | How would one doe this? Any help is appreciated.
> > |
> >
> >|||Brad:
I copied the rdl into my designer anf get te following error:
Deserialization failed: The Report element was not found.
""Brad Syputa - MS"" wrote:
> I did this for a Northwind DB. Here is how my RDL looked. Multiple queries
> and data sets returned.
> I changed the names of my data source and data sets. It should still work
> against a Northwind DB.
> <?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">
> <DataSources>
> <DataSource Name="ds1">
> <rd:DataSourceID>2122d420-3823-467b-8d49-89744c792684</rd:DataSourceID>
> <ConnectionProperties>
> <DataProvider>SQL</DataProvider>
> <ConnectString>data source=servername;initial
> catalog=Northwind</ConnectString>
> </ConnectionProperties>
> </DataSource>
> </DataSources>
> <rd:ReportID>48c77212-85fb-46c9-9347-e0ecf74aa394</rd:ReportID>
> <Body>
> <ReportItems>
> <Textbox Name="textbox_2">
> <Style>
> <BackgroundColor>Yellow</BackgroundColor>
> <TextAlign>Left</TextAlign>
> <FontSize>9pt</FontSize>
> </Style>
> <Height>.5 in</Height>
> <Width>3 in</Width>
> <CanGrow>true</CanGrow>
> <Value>=Fields!TestResult.Value
> + "********Parameters CustomerIDParam Value: " +
> Parameters!CustomerIDParam.Value
> + "********Parameters EmployeeIDParam Value: " +
> Str(Parameters!EmployeeIDParam.Value)
> </Value>
> <Top>.5 in</Top>
> <Left>0 in</Left>
> </Textbox>
> <Textbox Name="textbox_1">
> <Style>
> <BackgroundColor>Red</BackgroundColor>
> <FontSize>14pt</FontSize>
> </Style>
> <Height>.5 in</Height>
> <Width>3 in</Width>
> <CanGrow>true</CanGrow>
> <Value>Test Result Below:</Value>
> <Top>0 in</Top>
> <Left>0 in</Left>
> </Textbox>
> </ReportItems>
> <Height>1.705in</Height>
> </Body>
> <rd:DrawGrid>true</rd:DrawGrid>
> <DataSets>
> <DataSet Name="mydataset">
> <Fields>
> <Field Name="TestResult">
> <DataField>TestResult</DataField>
> <rd:TypeName>System.String</rd:TypeName>
> </Field>
> <Field Name="CustomerID">
> <DataField>CustomerID</DataField>
> <rd:TypeName>System.String</rd:TypeName>
> </Field>
> <Field Name="EmployeeID">
> <DataField>EmployeeID</DataField>
> <rd:TypeName>System.Int32</rd:TypeName>
> </Field>
> </Fields>
> <Query>
> <DataSourceName>ds1</DataSourceName>
> <CommandType>Text</CommandType>
> <CommandText>SELECT * , 'Test Passed' as "TestResult" FROM Orders
> WHERE EmployeeID = @.EmployeeIDParam AND CustomerID => @.CustomerIDParam</CommandText>
> <rd:UseGenericDesigner>true</rd:UseGenericDesigner>
> <QueryParameters>
> <QueryParameter Name="@.EmployeeIDParam">
> <Value>=Parameters!EmployeeIDParam.Value</Value>
> </QueryParameter>
> <QueryParameter Name="@.CustomerIDParam">
> <Value>=Parameters!CustomerIDParam.Value</Value>
> </QueryParameter>
> </QueryParameters>
> </Query>
> </DataSet>
> <DataSet Name="mydataset2">
> <Fields>
> <Field Name="EmployeeID">
> <DataField>EmployeeID</DataField>
> <rd:TypeName>System.Int32</rd:TypeName>
> </Field>
> </Fields>
> <Query>
> <DataSourceName>ds1</DataSourceName>
> <CommandType>Text</CommandType>
> <CommandText>SELECT DISTINCT EmployeeID FROM Employees
> ORDER BY EmployeeID ASC</CommandText>
> <rd:UseGenericDesigner>true</rd:UseGenericDesigner>
> </Query>
> </DataSet>
> <DataSet Name="mydataset3">
> <Fields>
> <Field Name="CustomerID">
> <DataField>CustomerID</DataField>
> <rd:TypeName>System.String</rd:TypeName>
> </Field>
> </Fields>
> <Query>
> <DataSourceName>ds1</DataSourceName>
> <CommandType>Text</CommandType>
> <CommandText>SELECT DISTINCT CustomerID FROM Orders
> WHERE EmployeeID = @.EmployeeIDParam ORDER BY CustomerID ASC</CommandText>
> <rd:UseGenericDesigner>true</rd:UseGenericDesigner>
> <QueryParameters>
> <QueryParameter Name="@.EmployeeIDParam">
> <Value>=Parameters!EmployeeIDParam.Value</Value>
> </QueryParameter>
> </QueryParameters>
> </Query>
> </DataSet>
> </DataSets>
> <Language>en-US</Language>
> <RightMargin>1in</RightMargin>
> <BottomMargin>1in</BottomMargin>
> <TopMargin>1in</TopMargin>
> <rd:SnapToGrid>true</rd:SnapToGrid>
> <Width>5in</Width>
> <LeftMargin>1in</LeftMargin>
> <ReportParameters>
> <ReportParameter Name="EmployeeID">
> <DataType>Integer</DataType>
> <DefaultValue>
> <Values>
> <Value>1</Value>
> </Values>
> </DefaultValue>
> <ValidValues>
> <DataSetReference>
> <DataSetName>mydataset2</DataSetName>
> <ValueField>EmployeeID</ValueField>
> <LabelField>EmployeeID</LabelField>
> </DataSetReference>
> </ValidValues>
> <Prompt>Employee ID :</Prompt>
> </ReportParameter>
> <ReportParameter Name="EmployeeIDParam">
> <DataType>Integer</DataType>
> <DefaultValue>
> <Values>
> <Value>=Parameters!EmployeeID.Value</Value>
> </Values>
> </DefaultValue>
> <Prompt> </Prompt>
> </ReportParameter>
> <ReportParameter Name="CustomerIDParam">
> <DataType>String</DataType>
> <DefaultValue>
> <Values>
> <Value>VINET</Value>
> </Values>
> </DefaultValue>
> <ValidValues>
> <DataSetReference>
> <DataSetName>mydataset3</DataSetName>
> <ValueField>CustomerID</ValueField>
> <LabelField>CustomerID</LabelField>
> </DataSetReference>
> </ValidValues>
> <Prompt>Customer ID :</Prompt>
> </ReportParameter>
> </ReportParameters>
> </Report>
> --
> | Thread-Topic: Dynamic Parameter
> | thread-index: AcUFhs+wyP3Cs1ONTxSLxYdNcM59XQ==> | X-WBNR-Posting-Host: 70.66.0.254
> | From: "=?Utf-8?B?U0FjYW51Y2s=?=" <SAcanuck@.discussions.microsoft.com>
> | Subject: Dynamic Parameter
> | Date: Fri, 28 Jan 2005 14:15:01 -0800
> | Lines: 12
> | Message-ID: <AE5AB9C9-7340-4ED3-9EC6-3F5DB22834AA@.microsoft.com>
> | MIME-Version: 1.0
> | Content-Type: text/plain;
> | charset="Utf-8"
> | Content-Transfer-Encoding: 7bit
> | X-Newsreader: Microsoft CDO for Windows 2000
> | Content-Class: urn:content-classes:message
> | Importance: normal
> | Priority: normal
> | X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.0
> | Newsgroups: microsoft.public.sqlserver.reportingsvcs
> | NNTP-Posting-Host: TK2MSFTNGXA03.phx.gbl 10.40.1.29
> | Path: cpmsftngxa10.phx.gbl!TK2MSFTNGXA03.phx.gbl
> | Xref: cpmsftngxa10.phx.gbl microsoft.public.sqlserver.reportingsvcs:41264
> | X-Tomcat-NG: microsoft.public.sqlserver.reportingsvcs
> |
> | My DB has 3 tables Employee, Department, Job
> |
> | I want to create a report with two parameters.
> |
> | Parameter 1 Options: Employee, Department or Job
> |
> | Parameter 2 Options: When user selects Employee from parameter 1, all
> | employee names will be made available to select in parameter 2, When
> user
> | selects department from parameter 1, all department names will be made
> | available to select in parameter 2.
> |
> | How would one doe this? Any help is appreciated.
> |
>|||Please describe your exact requirement. Becouse your sample is to solve with
the description in the online docu. Isn't it?
"SAcanuck" wrote:
> I had a look at the reporting services books online and it had a description
> on how to use a basiv dynamic parameter...my requirement is a bit more
> advanced than the given example.
> "Dev Main" wrote:
> > It is not necessary to dive into this RDL code.
> >
> > Go to the online help from Reporting Services. There is a chapter Create
> > Report (or similare) and there is a description of using Parameters. On the
> > end of this article is your question answered in the best way.
> >
> >
> >
> > ""Brad Syputa - MS"" wrote:
> >
> > > I did this for a Northwind DB. Here is how my RDL looked. Multiple queries
> > > and data sets returned.
> > >
> > > I changed the names of my data source and data sets. It should still work
> > > against a Northwind DB.
> > >
> > > <?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">
> > > <DataSources>
> > > <DataSource Name="ds1">
> > >
> > > <rd:DataSourceID>2122d420-3823-467b-8d49-89744c792684</rd:DataSourceID>
> > > <ConnectionProperties>
> > > <DataProvider>SQL</DataProvider>
> > > <ConnectString>data source=servername;initial
> > > catalog=Northwind</ConnectString>
> > > </ConnectionProperties>
> > > </DataSource>
> > > </DataSources>
> > > <rd:ReportID>48c77212-85fb-46c9-9347-e0ecf74aa394</rd:ReportID>
> > > <Body>
> > > <ReportItems>
> > > <Textbox Name="textbox_2">
> > > <Style>
> > > <BackgroundColor>Yellow</BackgroundColor>
> > > <TextAlign>Left</TextAlign>
> > > <FontSize>9pt</FontSize>
> > > </Style>
> > > <Height>.5 in</Height>
> > > <Width>3 in</Width>
> > > <CanGrow>true</CanGrow>
> > > <Value>=Fields!TestResult.Value
> > > + "********Parameters CustomerIDParam Value: " +
> > > Parameters!CustomerIDParam.Value
> > > + "********Parameters EmployeeIDParam Value: " +
> > > Str(Parameters!EmployeeIDParam.Value)
> > > </Value>
> > > <Top>.5 in</Top>
> > > <Left>0 in</Left>
> > > </Textbox>
> > > <Textbox Name="textbox_1">
> > > <Style>
> > > <BackgroundColor>Red</BackgroundColor>
> > > <FontSize>14pt</FontSize>
> > > </Style>
> > > <Height>.5 in</Height>
> > > <Width>3 in</Width>
> > > <CanGrow>true</CanGrow>
> > > <Value>Test Result Below:</Value>
> > > <Top>0 in</Top>
> > > <Left>0 in</Left>
> > > </Textbox>
> > > </ReportItems>
> > > <Height>1.705in</Height>
> > > </Body>
> > > <rd:DrawGrid>true</rd:DrawGrid>
> > > <DataSets>
> > > <DataSet Name="mydataset">
> > > <Fields>
> > > <Field Name="TestResult">
> > > <DataField>TestResult</DataField>
> > > <rd:TypeName>System.String</rd:TypeName>
> > > </Field>
> > > <Field Name="CustomerID">
> > > <DataField>CustomerID</DataField>
> > > <rd:TypeName>System.String</rd:TypeName>
> > > </Field>
> > > <Field Name="EmployeeID">
> > > <DataField>EmployeeID</DataField>
> > > <rd:TypeName>System.Int32</rd:TypeName>
> > > </Field>
> > > </Fields>
> > > <Query>
> > > <DataSourceName>ds1</DataSourceName>
> > > <CommandType>Text</CommandType>
> > > <CommandText>SELECT * , 'Test Passed' as "TestResult" FROM Orders
> > > WHERE EmployeeID = @.EmployeeIDParam AND CustomerID => > > @.CustomerIDParam</CommandText>
> > > <rd:UseGenericDesigner>true</rd:UseGenericDesigner>
> > > <QueryParameters>
> > > <QueryParameter Name="@.EmployeeIDParam">
> > > <Value>=Parameters!EmployeeIDParam.Value</Value>
> > > </QueryParameter>
> > > <QueryParameter Name="@.CustomerIDParam">
> > > <Value>=Parameters!CustomerIDParam.Value</Value>
> > > </QueryParameter>
> > > </QueryParameters>
> > > </Query>
> > > </DataSet>
> > > <DataSet Name="mydataset2">
> > > <Fields>
> > > <Field Name="EmployeeID">
> > > <DataField>EmployeeID</DataField>
> > > <rd:TypeName>System.Int32</rd:TypeName>
> > > </Field>
> > > </Fields>
> > > <Query>
> > > <DataSourceName>ds1</DataSourceName>
> > > <CommandType>Text</CommandType>
> > > <CommandText>SELECT DISTINCT EmployeeID FROM Employees
> > > ORDER BY EmployeeID ASC</CommandText>
> > > <rd:UseGenericDesigner>true</rd:UseGenericDesigner>
> > > </Query>
> > > </DataSet>
> > > <DataSet Name="mydataset3">
> > > <Fields>
> > > <Field Name="CustomerID">
> > > <DataField>CustomerID</DataField>
> > > <rd:TypeName>System.String</rd:TypeName>
> > > </Field>
> > > </Fields>
> > > <Query>
> > > <DataSourceName>ds1</DataSourceName>
> > > <CommandType>Text</CommandType>
> > > <CommandText>SELECT DISTINCT CustomerID FROM Orders
> > > WHERE EmployeeID = @.EmployeeIDParam ORDER BY CustomerID ASC</CommandText>
> > > <rd:UseGenericDesigner>true</rd:UseGenericDesigner>
> > > <QueryParameters>
> > > <QueryParameter Name="@.EmployeeIDParam">
> > > <Value>=Parameters!EmployeeIDParam.Value</Value>
> > > </QueryParameter>
> > > </QueryParameters>
> > > </Query>
> > > </DataSet>
> > > </DataSets>
> > > <Language>en-US</Language>
> > > <RightMargin>1in</RightMargin>
> > > <BottomMargin>1in</BottomMargin>
> > > <TopMargin>1in</TopMargin>
> > > <rd:SnapToGrid>true</rd:SnapToGrid>
> > > <Width>5in</Width>
> > > <LeftMargin>1in</LeftMargin>
> > > <ReportParameters>
> > > <ReportParameter Name="EmployeeID">
> > > <DataType>Integer</DataType>
> > > <DefaultValue>
> > > <Values>
> > > <Value>1</Value>
> > > </Values>
> > > </DefaultValue>
> > > <ValidValues>
> > > <DataSetReference>
> > > <DataSetName>mydataset2</DataSetName>
> > > <ValueField>EmployeeID</ValueField>
> > > <LabelField>EmployeeID</LabelField>
> > > </DataSetReference>
> > > </ValidValues>
> > > <Prompt>Employee ID :</Prompt>
> > > </ReportParameter>
> > > <ReportParameter Name="EmployeeIDParam">
> > > <DataType>Integer</DataType>
> > > <DefaultValue>
> > > <Values>
> > > <Value>=Parameters!EmployeeID.Value</Value>
> > > </Values>
> > > </DefaultValue>
> > > <Prompt> </Prompt>
> > > </ReportParameter>
> > > <ReportParameter Name="CustomerIDParam">
> > > <DataType>String</DataType>
> > > <DefaultValue>
> > > <Values>
> > > <Value>VINET</Value>
> > > </Values>
> > > </DefaultValue>
> > > <ValidValues>
> > > <DataSetReference>
> > > <DataSetName>mydataset3</DataSetName>
> > > <ValueField>CustomerID</ValueField>
> > > <LabelField>CustomerID</LabelField>
> > > </DataSetReference>
> > > </ValidValues>
> > > <Prompt>Customer ID :</Prompt>
> > > </ReportParameter>
> > > </ReportParameters>
> > > </Report>
> > > --
> > > | Thread-Topic: Dynamic Parameter
> > > | thread-index: AcUFhs+wyP3Cs1ONTxSLxYdNcM59XQ==> > > | X-WBNR-Posting-Host: 70.66.0.254
> > > | From: "=?Utf-8?B?U0FjYW51Y2s=?=" <SAcanuck@.discussions.microsoft.com>
> > > | Subject: Dynamic Parameter
> > > | Date: Fri, 28 Jan 2005 14:15:01 -0800
> > > | Lines: 12
> > > | Message-ID: <AE5AB9C9-7340-4ED3-9EC6-3F5DB22834AA@.microsoft.com>
> > > | MIME-Version: 1.0
> > > | Content-Type: text/plain;
> > > | charset="Utf-8"
> > > | Content-Transfer-Encoding: 7bit
> > > | X-Newsreader: Microsoft CDO for Windows 2000
> > > | Content-Class: urn:content-classes:message
> > > | Importance: normal
> > > | Priority: normal
> > > | X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.0
> > > | Newsgroups: microsoft.public.sqlserver.reportingsvcs
> > > | NNTP-Posting-Host: TK2MSFTNGXA03.phx.gbl 10.40.1.29
> > > | Path: cpmsftngxa10.phx.gbl!TK2MSFTNGXA03.phx.gbl
> > > | Xref: cpmsftngxa10.phx.gbl microsoft.public.sqlserver.reportingsvcs:41264
> > > | X-Tomcat-NG: microsoft.public.sqlserver.reportingsvcs
> > > |
> > > | My DB has 3 tables Employee, Department, Job
> > > |
> > > | I want to create a report with two parameters.
> > > |
> > > | Parameter 1 Options: Employee, Department or Job
> > > |
> > > | Parameter 2 Options: When user selects Employee from parameter 1, all
> > > | employee names will be made available to select in parameter 2, When
> > > user
> > > | selects department from parameter 1, all department names will be made
> > > | available to select in parameter 2.
> > > |
> > > | How would one doe this? Any help is appreciated.
> > > |
> > >
> > >
Posted by mod at 11:45 PM
Labels: , , , , , , , , , , , , , ,

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)