SelectAll.rdl
<?xml version="1.0" encoding="utf-8"?>
<Report xmlns="https://schemas.microsoft.com/sqlserver/reporting/2003/10/reportdefinition" xmlns:rd="https://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="textbox3">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
<FontWeight>700</FontWeight>
</Style>
<ZIndex>7</ZIndex>
<rd:DefaultName>textbox3</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value>Category</Value>
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox5">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
<FontWeight>700</FontWeight>
</Style>
<ZIndex>6</ZIndex>
<CanGrow>true</CanGrow>
<Value>Subcategory</Value>
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox1">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
<FontWeight>700</FontWeight>
</Style>
<ZIndex>5</ZIndex>
<rd:DefaultName>textbox1</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value>Product</Value>
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox4">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<TextAlign>Right</TextAlign>
<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>Quantity</Value>
</Textbox>
</ReportItems>
</TableCell>
</TableCells>
</TableRow>
</TableRows>
<RepeatOnNewPage>true</RepeatOnNewPage>
</Header>
<Details>
<TableRows>
<TableRow>
<Height>0.25in</Height>
<TableCells>
<TableCell>
<ReportItems>
<Textbox Name="Cat">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>3</ZIndex>
<rd:DefaultName>Cat</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value>=Fields!Cat.Value</Value>
<HideDuplicates>DataSet1</HideDuplicates>
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox10">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>2</ZIndex>
<CanGrow>true</CanGrow>
<Value>=Fields!SubCat.Value</Value>
<HideDuplicates>DataSet1</HideDuplicates>
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="Product">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>1</ZIndex>
<rd:DefaultName>Product</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value>=Fields!Product.Value</Value>
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="Qty">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<TextAlign>Right</TextAlign>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<rd:DefaultName>Qty</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value>=Fields!Qty.Value</Value>
</Textbox>
</ReportItems>
</TableCell>
</TableCells>
</TableRow>
</TableRows>
<Sorting>
<SortBy>
<SortExpression>=Fields!Cat.Value</SortExpression>
<Direction>Ascending</Direction>
</SortBy>
<SortBy>
<SortExpression>=Fields!SubCat.Value</SortExpression>
<Direction>Ascending</Direction>
</SortBy>
<SortBy>
<SortExpression>=Fields!Product.Value</SortExpression>
<Direction>Ascending</Direction>
</SortBy>
</Sorting>
</Details>
<DataSetName>DataSet1</DataSetName>
<Width>5.875in</Width>
<TableColumns>
<TableColumn>
<Width>1.25in</Width>
</TableColumn>
<TableColumn>
<Width>1.75in</Width>
</TableColumn>
<TableColumn>
<Width>1.75in</Width>
</TableColumn>
<TableColumn>
<Width>1.125in</Width>
</TableColumn>
</TableColumns>
</Table>
</ReportItems>
<Style />
<Height>0.75in</Height>
</Body>
<TopMargin>1in</TopMargin>
<DataSources>
<DataSource Name="DataSource1">
<rd:DataSourceID>f4ac6544-ddf6-4e3a-aecd-2b45a4b69aa0</rd:DataSourceID>
<ConnectionProperties>
<DataProvider>SQL</DataProvider>
<ConnectString>data source=.;initial catalog=AdventureWorks2000</ConnectString>
<IntegratedSecurity>true</IntegratedSecurity>
</ConnectionProperties>
</DataSource>
</DataSources>
<Width>6.125in</Width>
<DataSets>
<DataSet Name="DataSet1">
<Fields>
<Field Name="Product">
<DataField>Product</DataField>
<rd:TypeName>System.String</rd:TypeName>
</Field>
<Field Name="SubCat">
<DataField>SubCat</DataField>
<rd:TypeName>System.String</rd:TypeName>
</Field>
<Field Name="Cat">
<DataField>Cat</DataField>
<rd:TypeName>System.String</rd:TypeName>
</Field>
<Field Name="Qty">
<DataField>Qty</DataField>
<rd:TypeName>System.Int32</rd:TypeName>
</Field>
</Fields>
<Query>
<DataSourceName>DataSource1</DataSourceName>
<CommandText>SELECT Product.Name AS Product, ProductSubCategory.Name AS SubCat, ProductCategory.Name AS Cat, SUM(SalesOrderDetail.OrderQty) AS Qty
FROM SalesOrderDetail INNER JOIN
SalesOrderHeader ON SalesOrderDetail.SalesOrderID = SalesOrderHeader.SalesOrderID INNER JOIN
Product ON SalesOrderDetail.ProductID = Product.ProductID INNER JOIN
ProductSubCategory ON Product.ProductSubCategoryID = ProductSubCategory.ProductSubCategoryID INNER JOIN
ProductCategory ON ProductSubCategory.ProductCategoryID = ProductCategory.ProductCategoryID
GROUP BY Product.Name, ProductSubCategory.Name, ProductCategory.Name
HAVING (Product.Name = @Product OR
@Product IS NULL) AND (ProductSubCategory.Name = @SubCat OR
@SubCat IS NULL) AND (ProductCategory.Name = @Cat OR
@Cat IS NULL)</CommandText>
<QueryParameters>
<QueryParameter Name="@Product">
<Value>=Parameters!Product.Value</Value>
</QueryParameter>
<QueryParameter Name="@SubCat">
<Value>=Parameters!SubCat.Value</Value>
</QueryParameter>
<QueryParameter Name="@Cat">
<Value>=Parameters!Cat.Value</Value>
</QueryParameter>
</QueryParameters>
</Query>
</DataSet>
<DataSet Name="Categories">
<Fields>
<Field Name="ID">
<DataField>ID</DataField>
<rd:TypeName>System.String</rd:TypeName>
</Field>
<Field Name="Name">
<DataField>Name</DataField>
<rd:TypeName>System.String</rd:TypeName>
</Field>
</Fields>
<Query>
<DataSourceName>DataSource1</DataSourceName>
<CommandText>SELECT Name AS ID, Name AS Name
FROM ProductCategory
UNION
SELECT NULL, 'All'</CommandText>
<rd:UseGenericDesigner>true</rd:UseGenericDesigner>
</Query>
</DataSet>
<DataSet Name="Subcategories">
<Fields>
<Field Name="ID">
<DataField>ID</DataField>
<rd:TypeName>System.String</rd:TypeName>
</Field>
<Field Name="Name">
<DataField>Name</DataField>
<rd:TypeName>System.String</rd:TypeName>
</Field>
</Fields>
<Query>
<DataSourceName>DataSource1</DataSourceName>
<CommandText>SELECT ProductSubCategory.Name AS ID, ProductSubCategory.Name AS Name
FROM ProductCategory INNER JOIN
ProductSubCategory ON ProductCategory.ProductCategoryID = ProductSubCategory.ProductCategoryID
WHERE (ProductCategory.Name = @Cat)
UNION
SELECT NULL, 'All'</CommandText>
<QueryParameters>
<QueryParameter Name="@Cat">
<Value>=Parameters!Cat.Value</Value>
</QueryParameter>
</QueryParameters>
<rd:UseGenericDesigner>true</rd:UseGenericDesigner>
</Query>
</DataSet>
<DataSet Name="Products">
<Fields>
<Field Name="ID">
<DataField>ID</DataField>
<rd:TypeName>System.String</rd:TypeName>
</Field>
<Field Name="Name">
<DataField>Name</DataField>
<rd:TypeName>System.String</rd:TypeName>
</Field>
</Fields>
<Query>
<DataSourceName>DataSource1</DataSourceName>
<CommandText>SELECT Product .Name AS ID, Product .Name AS Name
FROM Product INNER JOIN
ProductSubCategory ON Product .ProductSubCategoryID = ProductSubCategory.ProductSubCategoryID INNER JOIN
ProductCategory ON ProductSubCategory.ProductCategoryID = ProductCategory.ProductCategoryID
WHERE (ProductCategory.Name = @Cat) AND (ProductSubCategory.Name = @SubCat)
UNION
SELECT NULL, 'All'</CommandText>
<QueryParameters>
<QueryParameter Name="@Cat">
<Value>=Parameters!Cat.Value</Value>
</QueryParameter>
<QueryParameter Name="@SubCat">
<Value>=Parameters!SubCat.Value</Value>
</QueryParameter>
</QueryParameters>
<rd:UseGenericDesigner>true</rd:UseGenericDesigner>
</Query>
</DataSet>
</DataSets>
<LeftMargin>1in</LeftMargin>
<rd:SnapToGrid>true</rd:SnapToGrid>
<rd:DrawGrid>true</rd:DrawGrid>
<rd:ReportID>5ad37685-2c6d-4463-94f0-7ce2a83ea8c8</rd:ReportID>
<BottomMargin>1in</BottomMargin>
<ReportParameters>
<ReportParameter Name="Cat">
<DataType>String</DataType>
<Nullable>true</Nullable>
<Prompt>Category</Prompt>
<ValidValues>
<DataSetReference>
<DataSetName>Categories</DataSetName>
<ValueField>ID</ValueField>
<LabelField>Name</LabelField>
</DataSetReference>
</ValidValues>
</ReportParameter>
<ReportParameter Name="SubCat">
<DataType>String</DataType>
<Nullable>true</Nullable>
<Prompt>Subcategory</Prompt>
<ValidValues>
<DataSetReference>
<DataSetName>Subcategories</DataSetName>
<ValueField>ID</ValueField>
<LabelField>Name</LabelField>
</DataSetReference>
</ValidValues>
</ReportParameter>
<ReportParameter Name="Product">
<DataType>String</DataType>
<Nullable>true</Nullable>
<Prompt>Product</Prompt>
<ValidValues>
<DataSetReference>
<DataSetName>Products</DataSetName>
<ValueField>ID</ValueField>
<LabelField>Name</LabelField>
</DataSetReference>
</ValidValues>
</ReportParameter>
</ReportParameters>
<Language>en-US</Language>
</Report>