SelectAll.rdl - Chris Hays's Reporting Services Sleazy Hacks Weblog - Site Home

<?xml version="1.0" encoding="utf-8"?>
<Report xmlns="http://schemas.microsoft.com/sqlserver/reporting/2003/10/reportdefinition" 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="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>