Dear Friends,
In my report, I am having Listbox for users to choose Country, City & Company.
The user can choose Country. Based on the country selection, cities will be listed out.
Based on the city selection, Companies will be listed out.
They can choose companies.
Now, I have to show two set of results.
A. List of Companies as per selection ( dataset with equal to selection )
B. List of Companies which are not selected ( ie dataset with not equal to selection )
I have created a dataset with all companies and filter it by selection. When I tried with the filter option in the Dataset, I am able to check for only one value and not for multiple value. If the selection is one company, then I can filter it. But if they choose 5 companies, I am not not able to filter it. Is there any other option I can try out.
Please advice. Thanks.
warm regards
Rakin
Singapore.
It sounds like you are using RS 2005, since you have multi value report parameters.
On the filter tab, you can set the filter operator to "IN" (this is the last entry in the list of filter operators). Assuming that your Company report parameter is marked as "multi-value", you can then use the following filter settings:
Filter expression: =Fields!CompanyName.Value
Filter operator: IN
Filter value: =Parameters!Company.Value
The IN-filter can automatically deal with multi-value parameters (which represent an array of values).
-- Robert
|||Thanks Robert.
You are right. I am using RS 2005.
Now, I am able to get the value for the first option. ( with IN )
But I could n't find any NOT IN option in the filter for the second set of results ?
Could you please advice for this ?
Regards
Rakin
Rakin, a "not in" filter is currently not natively supported in Reporting Services. You would need to look into ways of achieving this directly in the query by using the NOT IN clause:
E.g. select * from products where ReorderLevel not IN (@.Level)
I also attached a small sample report at the bottom based on the Northwind database.
-- Robert
<?xml version="1.0" encoding="utf-8"?>
<Report xmlns="http://schemas.microsoft.com/sqlserver/reporting/2005/01/reportdefinition" xmlns:rd="http://schemas.microsoft.com/SQLServer/reporting/reportdesigner">
<DataSources>
<DataSource Name="Northwind">
<DataSourceReference>Northwind</DataSourceReference>
<rd:DataSourceID>9904b9f4-d569-4718-a75a-b7fb657657fd</rd:DataSourceID>
</DataSource>
</DataSources>
<BottomMargin>1in</BottomMargin>
<RightMargin>1in</RightMargin>
<ReportParameters>
<ReportParameter Name="Level">
<DataType>Integer</DataType>
<Prompt>Level</Prompt>
<ValidValues>
<ParameterValues>
<ParameterValue>
<Value>0</Value>
</ParameterValue>
<ParameterValue>
<Value>5</Value>
</ParameterValue>
<ParameterValue>
<Value>10</Value>
</ParameterValue>
<ParameterValue>
<Value>15</Value>
</ParameterValue>
<ParameterValue>
<Value>20</Value>
</ParameterValue>
<ParameterValue>
<Value>25</Value>
</ParameterValue>
<ParameterValue>
<Value>30</Value>
</ParameterValue>
</ParameterValues>
</ValidValues>
<MultiValue>true</MultiValue>
</ReportParameter>
</ReportParameters>
<rd:DrawGrid>true</rd:DrawGrid>
<InteractiveWidth>8.5in</InteractiveWidth>
<rd:SnapToGrid>true</rd:SnapToGrid>
<Body>
<ReportItems>
<Matrix Name="matrix1">
<MatrixColumns>
<MatrixColumn>
<Width>1in</Width>
</MatrixColumn>
</MatrixColumns>
<Left>0.125in</Left>
<RowGroupings>
<RowGrouping>
<Width>1in</Width>
<DynamicRows>
<ReportItems>
<Textbox Name="ReorderLevel">
<rd:DefaultName>ReorderLevel</rd:DefaultName>
<ZIndex>1</ZIndex>
<Style>
<TextAlign>Right</TextAlign>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value>=Fields!ReorderLevel.Value</Value>
</Textbox>
</ReportItems>
<Sorting>
<SortBy>
<SortExpression>=Fields!ReorderLevel.Value</SortExpression>
<Direction>Ascending</Direction>
</SortBy>
</Sorting>
<Grouping Name="matrix1_ReorderLevel">
<GroupExpressions>
<GroupExpression>=Fields!ReorderLevel.Value</GroupExpression>
</GroupExpressions>
</Grouping>
</DynamicRows>
</RowGrouping>
</RowGroupings>
<ColumnGroupings>
<ColumnGrouping>
<DynamicColumns>
<ReportItems>
<Textbox Name="CategoryID">
<rd:DefaultName>CategoryID</rd:DefaultName>
<ZIndex>2</ZIndex>
<Style>
<TextAlign>Right</TextAlign>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value>=Fields!CategoryID.Value</Value>
</Textbox>
</ReportItems>
<Grouping Name="matrix1_CategoryID">
<GroupExpressions>
<GroupExpression>=Fields!CategoryID.Value</GroupExpression>
</GroupExpressions>
</Grouping>
</DynamicColumns>
<Height>0.25in</Height>
</ColumnGrouping>
</ColumnGroupings>
<DataSetName>DataSet1</DataSetName>
<Top>0.25in</Top>
<Width>2in</Width>
<Corner>
<ReportItems>
<Textbox Name="textbox1">
<rd:DefaultName>textbox1</rd:DefaultName>
<ZIndex>3</ZIndex>
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value />
</Textbox>
</ReportItems>
</Corner>
<Height>0.5in</Height>
<MatrixRows>
<MatrixRow>
<Height>0.25in</Height>
<MatrixCells>
<MatrixCell>
<ReportItems>
<Textbox Name="ProductID">
<rd:DefaultName>ProductID</rd:DefaultName>
<Style>
<TextAlign>Right</TextAlign>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value>=Count(Fields!ProductID.Value)</Value>
</Textbox>
</ReportItems>
</MatrixCell>
</MatrixCells>
</MatrixRow>
</MatrixRows>
</Matrix>
</ReportItems>
<Height>1.25in</Height>
</Body>
<rd:ReportID>ed5d6416-a54d-4c50-8568-8f5bae4484cf</rd:ReportID>
<LeftMargin>1in</LeftMargin>
<DataSets>
<DataSet Name="DataSet1">
<Query>
<rd:UseGenericDesigner>true</rd:UseGenericDesigner>
<CommandText>select * from products where ReorderLevel not IN (@.Level)</CommandText>
<QueryParameters>
<QueryParameter Name="@.Level">
<Value>=Parameters!Level.Value</Value>
</QueryParameter>
</QueryParameters>
<DataSourceName>Northwind</DataSourceName>
</Query>
<Fields>
<Field Name="ProductID">
<rd:TypeName>System.Int32</rd:TypeName>
<DataField>ProductID</DataField>
</Field>
<Field Name="ProductName">
<rd:TypeName>System.String</rd:TypeName>
<DataField>ProductName</DataField>
</Field>
<Field Name="SupplierID">
<rd:TypeName>System.Int32</rd:TypeName>
<DataField>SupplierID</DataField>
</Field>
<Field Name="CategoryID">
<rd:TypeName>System.Int32</rd:TypeName>
<DataField>CategoryID</DataField>
</Field>
<Field Name="QuantityPerUnit">
<rd:TypeName>System.String</rd:TypeName>
<DataField>QuantityPerUnit</DataField>
</Field>
<Field Name="UnitPrice">
<rd:TypeName>System.Decimal</rd:TypeName>
<DataField>UnitPrice</DataField>
</Field>
<Field Name="UnitsInStock">
<rd:TypeName>System.Int16</rd:TypeName>
<DataField>UnitsInStock</DataField>
</Field>
<Field Name="UnitsOnOrder">
<rd:TypeName>System.Int16</rd:TypeName>
<DataField>UnitsOnOrder</DataField>
</Field>
<Field Name="ReorderLevel">
<rd:TypeName>System.Int16</rd:TypeName>
<DataField>ReorderLevel</DataField>
</Field>
<Field Name="Discontinued">
<rd:TypeName>System.Boolean</rd:TypeName>
<DataField>Discontinued</DataField>
</Field>
</Fields>
</DataSet>
</DataSets>
<Width>2.5in</Width>
<InteractiveHeight>11in</InteractiveHeight>
<Language>en-US</Language>
<TopMargin>1in</TopMargin>
</Report>
In RS 2005, I have a report with 5 report parameters. Two of those parameters are query based in order to populate drop down lists in the report for the user.
The BUG occurs when the report initialises: the first query based parameter is populated but the second query-based parameter is blank. I select the available values in both parameters and click on the "View Report" button but the same BUG occurs: the first parameter is populated but the second parameter's values are reset. If I select values from both and click the View Report button a few times it will eventually display the report.
Have any of you encountered anything like this?
Niall
