Chris Hays's Reporting Services Sleazy Hacks Weblog

"Continued" Header on Subsequent Pages

2006-09-28T01:40:00Z

Question:
How can I repeat a group header on each page, but have it say "MyGroup (continued)" on all but the first page?

Answer:
Since pagination is determined long after the report has been fully processed, there's no way for the body of the report to reference any page information. As a result, we're going to have to resort to a trick wherein something in the page header can be made to look as if it is the group header.

The following assumes you are using a table and have no table header but can be readily adapted to other situations.

Step 1: Turn off RepeatOnNewPage on your group header

Step 2: Duplicate the group header information in a hidden column
You're going to need the group header text to always be present on the page, even when the group header itself isn't present. The easiest way is to add a hidden column to the table and then copy the group header textbox into the detail row in that column. Make careful note of the names of both the group header textbox (which I'll call GroupHeader) and the copy (which I'll call GroupCopy)

Step 3: Copy the first GroupHeader and first GroupCopy to the page header
In the page header, add two textboxes with the following expressions:
=First(ReportItems!GroupHeader.Value)
=First(ReportItems!GroupCopy.Value)
Make careful note of the names of both these textboxes (which I'll call FirstGroupHeader and FirstGroupCopy). Run the report and notice the behavior: FirstGroupHeader is the same as FirstGroupCopy only if it appears at the top of the page. Mark both textboxes as Hidden=True

Step 4: Copy the GroupCopy textbox into the page header and add "continued"
In the page header, add a textbox with the following expression:
=First(ReportItems!GroupCopy.Value) + " (continued)"
Align and size the textbox to match the GroupHeader textbox exactly. Now when you run the report, you'll see both the normal group header and the Continued group header

Step 5: Conditionally hide the Continued header textbox
Ideally, the next step would be to set the Hidden expression on the Continued header textbox to this:
=ReportItems!FirstGroupHeader.Value=ReportItems!FirstGroupCopy.Value
Unfortunately, this won't work due to a slightly overzealous publishing restriction against using multiple report item references within a single expression in a page header item [which was intended to prevent potentially bad things like Sum(textbox1/textbox2)].
However, we can bypass this publishing restriction by passing in the report items collection to a custom function.
Add the following to the report's Code block:
public function HideHeader(Items as ReportItems) as Boolean
return Items("FirstGroupHeader").Value = Items("FirstGroupCopy").Value
end function
Now set the Hidden property of the Continued header to:
=Code.HideHeader(ReportItems)

Note: In the event you will always page break at the end of a group, there is a much simpler approach. You can skip steps 3 and 5 and instead set the Hidden property of the Continued header to:
=Not(ReportItems!GroupHeader.Value Is Nothing)

A full working sample of continued headers on subsequent pages is attached.

Custom Report Item Image Quality

2006-04-19T22:08:00Z

Question:
How can I get print-quality output from my custom report item?

Answer:
Custom report items have slightly different plumbing than native report items. Of particular note are native charts. These are generated during report rendering, which means it is possible for us to vary both the type (raster or vector) and the DPI of the image based on the needs of the renderer. For example, we currently use 300 DPI raster for PDF, vector for print and Winforms and 96 DPI raster for HTML and Excel. Custom Report Items, on the other hand, are generated during processing. This means only raster images can be used (since they must work with all renderers, including those which do not support vector images) and only a single DPI can be chosen for all rendering targets.

Custom Report Items should therefore generate a raster image of sufficiently high DPI as to be high quality when printed. A default setting of 300 DPI should be sufficient for most applications. However, since the renderers don't currently pay attention to DPI, this will result in the image being three times the intended size. To account for this, you'll need to set the Sizing property of your generated image to FitProportional so that the image is rendered at its originally intended size:

m_MyImage.Sizing = Microsoft.ReportingServices.ReportRendering.Image.Sizings.FitProportional;

In some future version when we add native vector graphics support (EMF or SVG), this will become a non-issue for custom report items. At that time, a CRI could generate a single vector image which would be automatically converted by the Rendering Object Model into a raster image of appropriate DPI if only raster images are supported by the target renderer.

I've attached an updated Custom Report Item sample which illustrates working with variable DPI.

"All" Parameter Value Redux

2006-04-19T03:54:00Z

Question:
How can I enable users to select "All" as the value of a multivalue query parameter?

Answer:
Since multivalue parameters cannot be null, you cannot use the approach previously described here. Fortunately, a simple modification of that approach to use a non-null "ALL" token is all that is required.

Step 1: Start with a report that already has a parameterized query and Valid Values queries.

Step 2: Edit your base query to account for "All".
Whever you have something like this:
... AND Field IN (@Parameter) ...
Replace it with this:
... AND (Field IN (@Parameter) OR 'ALL' IN (@Parameter)) ...

Step 3: Edit your Valid Values queries to insert an explicit "All" and separate ids from labels
For example, for this original query:
     SELECT name FROM productcategory
Change it to this:
     SELECT 'ALL' as ID, 'All' as Label
     UNION ALL
     SELECT name as ID, name as Label FROM productcategory

Step 4: Update the report parameters
Set the default value of each parameter to ALL
Change the valid values settings to account for the new field names (if needed).

Step 5 (Optional): Update Valid Values queries to account for "All".
If you have hierarchical parameters, in some cases, you may want the dependent parameters to be independently selectable when the parent parameter's value is "All". In this case, repeat step 2 for each of your Valid Values queries. Note: Don't do this if you have extremely large numbers of possible values, as performance will suffer and your users wouldn't be able to nagivate an excessively long list anyhow.

A full working sample of multivalue "All" parameters is attached.

Reset Page Number On Group

2006-01-06T05:14:00Z

Question:
How can I reset my page number back to 1 every time I get a group break?

Answer:
Resetting the page number on group breaks isn't natively supported, but it can be achieved by tracking group breaks in a shared variable and subtracting off the page offset of the first page of the group from the current page number.

Step 1: Make sure there's a textbox in the report which contains the group expression

Step 2: Add shared variables to track the current group and page offset
Shared offset as Integer
Shared currentgroup as Object

Step 3: Add a custom function to set the shared variables and retrieve the group page number
Public Function GetGroupPageNumber(group as Object, pagenumber as Integer) as Object
  If Not (group = currentgroup)
   offset = pagenumber - 1
   currentgroup = group
  End If
  Return pagenumber - offset
End Function

Step 4: Use the function in the page header or footer
=Code.GetGroupPageNumber(ReportItems!Category.Value,Globals!PageNumber)

Note: Because this uses static variables, if two people run the report at the exact same moment, there's a slim chance one will smash the other's variable state (In SQL 2000, this could occasionally happen due to two users paginating through the same report at the same time, not just due to exactly simultaneous executions) If you need to be 100% certain to avoid this, you can make each of the shared variables a hash table based on user ID (Globals!UserID).

A full working sample of page number reset on group is attached.

Custom Report Items

2005-10-05T01:45:00Z

A currently undocumented feature of SQL 2005 Reporting Services is the ability to develop custom report items for embedding in reports. The documentation will be available in the web doc update around the time of the release. But for those of you interested in playing around with this in the mean time, I've put together a sample that shows pretty much everything you need to know to build one.

There are two controls you'll need to build when creating a Custom Report Item:

First, there’s the runtime control. Based on the presence of a CustomReportItem element in the report (RDL), the report processing engine processes the data and hands the resulting CustomReportItem object (part of the Rendering Object Model) to the runtime control (ICustomReportItem). The runtime control then creates a standard ReportItem to hand back in to the Rendering Object Model. Note: Currently the only type of report items supported is Image.

The second component is the design time control, which provides a plug-in to the Visual Studio-based Report Designer. It consists of a design surface, an activated design space (good for drag-and-drop of fields), integration with the VS property browser and hooks for a custom editor and other custom actions. Using this control, you use the report designer object model to construct a custom report item object within the report definition.

There's also a third optional component which allows you to "upgrade" an existing report item to your custom report item. It just takes an XML node for the original report item and returns an XML node for the custom report item.

~~The full sample can be downloaded here:~~
~~http://www.msnusers.com/ReportingServicesSleazyHacks/Documents/PolygonsCRI.zip~~
~~(You'll need to register here first for access:~~ ~~http://www.msnusers.com/ReportingServicesSleazyHacks/join~~)

For instructions on where to obtain the official sample, see: http://msdn.microsoft.com/en-us/library/ms345265.aspx

Centered Images

2004-10-27T22:38:00Z

Question:
I have variable sized images I want to display in a table column. How can I center these images without stretching them to fill the table cell?

Answer:
While there is no automatic centering behavior for the Image control, you can simulate this by writing an expression for PaddingLeft and PaddingTop to result in a centered image.

Step 1: Clip, don't Fit

Set the Sizing property of the Image control to Clip. In a table cell, this is the only setting that won't resize your image. (If your image gets automatically resized, the calculations below won't work.)

Step 2: Give yourself room to grow

Make your column and row large enough to accomodate your largest image so that the image won't clip.

Step 3: Turn off CanGrow

For all other cells in the row, turn off CanGrow, so ensure long textual contents won't cause the row to increase in size (thereby invalidating the calculations below).

Step 4: Reference System.Drawing

To determine the size of the image, you'll need to add a reference to the System.Drawing assembly in your report. This is in the Report Properties dialog on the References Tab.

Step 5: Calculate padding

To determine the size of the image, you'll need to load the image from your field into a memory stream and from there load it into a .Net Image object. Since fields are of type Object, you'll need to cast it to a byte array before loading it into a memory stream.

ImageWidthInPixels = System.Drawing.Image.FromStream(new System.IO.MemoryStream(CType(Fields!Photo.Value,Byte()))).Width

From there, you can calculate the half of the difference between the width of the image and the width of the column.

Padding = (ColumnWidthInInches-ImageWidthInPixels/DPI)/2

DPI is typically 96

The padding property takes a size, which is a string including the size and the units.

PaddingLeft = CStr(Round(Padding,2)) & "in"

Here's the expression fully expanded:

=CStr(Round((3-System.Drawing.Image.FromStream(new System.IO.MemoryStream(CType(Fields!Photo.Value,Byte()))).Height/96)/2,2))&"in"

Now just do the same thing for PaddingTop and you've got a fully centered image.

A full working sample of centered images is attached.

CenteredImage.rdl

2004-10-27T22:33:00Z

<?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">
<CodeModules>
    <CodeModule>System.Drawing, Version=1.0.5000.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a</CodeModule>
</CodeModules>
<RightMargin>1in</RightMargin>
<Body>
    <ReportItems>
      <Table Name="table1">
        <Height>3.25in</Height>
        <Style />
        <Header>
          <TableRows>
            <TableRow>
              <Height>0.25in</Height>
              <TableCells>
                <TableCell>
                  <ReportItems>
                    <Textbox Name="textbox1">
                      <Style>
                        <PaddingLeft>2pt</PaddingLeft>
                        <BorderStyle>
                          <Default>Solid</Default>
                        </BorderStyle>
                        <PaddingBottom>2pt</PaddingBottom>
                        <PaddingTop>2pt</PaddingTop>
                        <PaddingRight>2pt</PaddingRight>
                      </Style>
                      <ZIndex>5</ZIndex>
                      <rd:DefaultName>textbox1</rd:DefaultName>
                      <CanGrow>true</CanGrow>
                      <Value>Product</Value>
                    </Textbox>
                  </ReportItems>
                </TableCell>
                <TableCell>
                  <ReportItems>
                    <Textbox Name="textbox2">
                      <Style>
                        <PaddingLeft>2pt</PaddingLeft>
                        <BorderStyle>
                          <Default>Solid</Default>
                        </BorderStyle>
                        <PaddingBottom>2pt</PaddingBottom>
                        <PaddingTop>2pt</PaddingTop>
                        <PaddingRight>2pt</PaddingRight>
                      </Style>
                      <ZIndex>4</ZIndex>
                      <rd:DefaultName>textbox2</rd:DefaultName>
                      <CanGrow>true</CanGrow>
                      <Value>Thumbnail</Value>
                    </Textbox>
                  </ReportItems>
                </TableCell>
                <TableCell>
                  <ReportItems>
                    <Textbox Name="textbox3">
                      <Style>
                        <PaddingLeft>2pt</PaddingLeft>
                        <BorderStyle>
                          <Default>Solid</Default>
                        </BorderStyle>
                        <PaddingBottom>2pt</PaddingBottom>
                        <PaddingTop>2pt</PaddingTop>
                        <PaddingRight>2pt</PaddingRight>
                      </Style>
                      <ZIndex>3</ZIndex>
                      <rd:DefaultName>textbox3</rd:DefaultName>
                      <CanGrow>true</CanGrow>
                      <Value>Photo</Value>
                    </Textbox>
                  </ReportItems>
                </TableCell>
              </TableCells>
            </TableRow>
          </TableRows>
        </Header>
        <Details>
          <TableRows>
            <TableRow>
              <Height>3in</Height>
              <TableCells>
                <TableCell>
                  <ReportItems>
                    <Textbox Name="Name">
                      <Style>
                        <PaddingLeft>2pt</PaddingLeft>
                        <BorderStyle>
                          <Default>Solid</Default>
                        </BorderStyle>
                        <PaddingBottom>2pt</PaddingBottom>
                        <PaddingTop>2pt</PaddingTop>
                        <PaddingRight>2pt</PaddingRight>
                      </Style>
                      <ZIndex>2</ZIndex>
                      <rd:DefaultName>Name</rd:DefaultName>
                      <CanGrow>true</CanGrow>
                      <Value>=Fields!Name.Value</Value>
                    </Textbox>
                  </ReportItems>
                </TableCell>
                <TableCell>
                  <ReportItems>
                    <Image Name="image1">
                      <ZIndex>1</ZIndex>
                      <MIMEType>image/bmp</MIMEType>
                      <Source>Database</Source>
                      <Style>
                        <PaddingLeft>=CStr(Round((1.5-System.Drawing.Image.FromStream(new System.IO.MemoryStream(CType(Fields!ThumbNailPhoto.Value,Byte()))).Width
/96)/2,2))&"in"</PaddingLeft>
                        <BorderStyle>
                          <Default>Solid</Default>
                        </BorderStyle>
                        <PaddingTop>=CStr(Round((3-System.Drawing.Image.FromStream(new System.IO.MemoryStream(CType(Fields!ThumbNailPhoto.Value,Byte()))).Height/96)/2,2))&"in"</PaddingTop>
                      </Style>
                      <Value>=Fields!ThumbNailPhoto.Value</Value>
                      <Sizing>Clip</Sizing>
                    </Image>
                  </ReportItems>
                </TableCell>
                <TableCell>
                  <ReportItems>
                    <Image Name="image2">
                      <MIMEType>image/bmp</MIMEType>
                      <Source>Database</Source>
                      <Style>
                        <PaddingLeft>=CStr(Round((3.5-System.Drawing.Image.FromStream(new System.IO.MemoryStream(CType(Fields!LargePhoto.Value,Byte()))).Width
/96)/2,2))&"in"</PaddingLeft>
                        <BorderStyle>
                          <Default>Solid</Default>
                        </BorderStyle>
                        <PaddingTop>=CStr(Round((3-System.Drawing.Image.FromStream(new System.IO.MemoryStream(CType(Fields!LargePhoto.Value,Byte()))).Height
/96)/2,2))&"in"</PaddingTop>
                      </Style>
                      <Value>=Fields!LargePhoto.Value</Value>
                      <Sizing>Clip</Sizing>
                    </Image>
                  </ReportItems>
                </TableCell>
              </TableCells>
            </TableRow>
          </TableRows>
        </Details>
        <DataSetName>DataSet1</DataSetName>
        <Width>6.75in</Width>
        <TableColumns>
          <TableColumn>
            <Width>1.75in</Width>
          </TableColumn>
          <TableColumn>
            <Width>1.5in</Width>
          </TableColumn>
          <TableColumn>
            <Width>3.5in</Width>
          </TableColumn>
        </TableColumns>
      </Table>
    </ReportItems>
    <Style />
    <Height>3.375in</Height>
</Body>
<TopMargin>1in</TopMargin>
<DataSources>
    <DataSource Name="AdventureWorks2000">
      <rd:DataSourceID>1f55598a-99e9-4bf8-8795-f7db27094cf7</rd:DataSourceID>
      <ConnectionProperties>
        <DataProvider>SQL</DataProvider>
        <ConnectString>data source=.;initial catalog=AdventureWorks2000</ConnectString>
        <IntegratedSecurity>true</IntegratedSecurity>
      </ConnectionProperties>
    </DataSource>
</DataSources>
<Code />
<Width>6.79167in</Width>
<DataSets>
    <DataSet Name="DataSet1">
      <Fields>
        <Field Name="Name">
          <DataField>Name</DataField>
          <rd:TypeName>System.String</rd:TypeName>
        </Field>
        <Field Name="ThumbNailPhoto">
          <DataField>ThumbNailPhoto</DataField>
          <rd:TypeName>System.Byte[]</rd:TypeName>
        </Field>
        <Field Name="LargePhoto">
          <DataField>LargePhoto</DataField>
          <rd:TypeName>System.Byte[]</rd:TypeName>
        </Field>
      </Fields>
      <Query>
        <DataSourceName>AdventureWorks2000</DataSourceName>
        <CommandText>SELECT     Product.Name, ProductPhoto.ThumbNailPhoto, ProductPhoto.LargePhoto
FROM         ProductPhoto INNER JOIN
                          (SELECT     MIN(P.ProductID) ProductID, ProductPhotoID
                            FROM          Product P
                            GROUP BY ProductPhotoID) MinP ON MinP.ProductPhotoID = ProductPhoto.ProductPhotoID INNER JOIN
                      Product ON Product.ProductID = MinP.ProductID
ORDER BY Product.Name</CommandText>
      </Query>
    </DataSet>
</DataSets>
<LeftMargin>1in</LeftMargin>
<rd:SnapToGrid>true</rd:SnapToGrid>
<rd:DrawGrid>true</rd:DrawGrid>
<Description />
<rd:ReportID>534db985-e9b3-4009-aff8-ff3c97b2730e</rd:ReportID>
<BottomMargin>1in</BottomMargin>
<Language>en-US</Language>
</Report>

Green-Bar Matrix

2004-08-31T04:22:00Z

Question:
How can I get a green-bar effect (alternating colors) in a matrix?

Answer:
For a green-bar table, you can simply use a background color expression like this: =iif(RowNumber(Nothing) Mod 2,"Green","White")
However, there is currently no GroupNumber() function on which to base a green-bar calculation in a matrix.
GroupNumber can be (mostly) simulated by using the RunningValue function to get a running distinct count of group expression values.
However, the trickiest part of green-bar in a matrix is the fact that some matrix cells may contain no data at all. This makes the group number calculation incorrect for empty cells.
To work around this, you need to effectively calculate the group number in the row header and then use that value inside the data cells.

Step 1: Add a (fake) inner row grouping
Select the innermost row grouping in your matrix. Right-click and select Insert Group.
For the group expression, group on a constant, such as =1

Step 2: Calculate the name of the color in the inner row grouping header
In the Value property of the newly created grouping header, add a calculation for the desired color based on a running value of a count distinct of the containing group expression.
For example: =iif(RunningValue(Fields!Country.Value,CountDistinct,Nothing) Mod 2, "AliceBlue", "White")
Note: If you have more than one row grouping, you may need to do the count distinct on the combination of all group expressions, like this:
=iif(RunningValue(Fields!Country.Value & CStr(Fields!Year.Value),CountDistinct,Nothing) Mod 2, "AliceBlue", "White")

Step 3: Set the background color of the inner row grouping header to =Value

Step 4: Set the background color of the matrix data cell to the value of the inner row grouping header
For example: =ReportItems!ColorNameTextbox.Value

Step 5: Set the background color of the outer row grouping header
You'll need to use the same expression here that you used for the Value of the inner row grouping header.

Step 6: "Cloak" the inner row grouping header (so it looks like part of of the outer grouping header)
Set the right border style of the outer grouping header to None.
Set the left border style of the inner grouping header to None.
Set the font weight of the inner grouping header to 1 pt.
Set the font color of the inner grouping header to =Value.
Set the CanGrow property of the inner grouping header to False.
Drag the inner grouping header to be as narrow as possible.
Optional: Hand-edit the RDL to set the width of the inner grouping header to 0in.

A full working sample of green-bar matrix is attached.

MatrixGreenBar.rdl

2004-08-31T04:19:00Z

<?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>
      <Matrix Name="matrix1">
        <Corner>
          <ReportItems>
            <Textbox Name="textbox1">
              <Style>
                <PaddingLeft>2pt</PaddingLeft>
                <BorderStyle>
                  <Default>Solid</Default>
                </BorderStyle>
                <PaddingBottom>2pt</PaddingBottom>
                <PaddingTop>2pt</PaddingTop>
                <PaddingRight>2pt</PaddingRight>
              </Style>
              <ZIndex>4</ZIndex>
              <rd:DefaultName>textbox1</rd:DefaultName>
              <CanGrow>true</CanGrow>
              <Value />
            </Textbox>
          </ReportItems>
        </Corner>
        <Height>0.5in</Height>
        <Style />
        <MatrixRows>
          <MatrixRow>
            <MatrixCells>
              <MatrixCell>
                <ReportItems>
                  <Textbox Name="Qty">
                    <Style>
                      <PaddingLeft>2pt</PaddingLeft>
                      <BackgroundColor>=ReportItems!Color.Value</BackgroundColor>
                      <BorderStyle>
                        <Default>Solid</Default>
                      </BorderStyle>
                      <TextAlign>Right</TextAlign>
                      <PaddingBottom>2pt</PaddingBottom>
                      <PaddingTop>2pt</PaddingTop>
                      <PaddingRight>2pt</PaddingRight>
                    </Style>
                    <rd:DefaultName>Qty</rd:DefaultName>
                    <CanGrow>true</CanGrow>
                    <Value>=Sum(Fields!Qty.Value)</Value>
                  </Textbox>
                </ReportItems>
              </MatrixCell>
            </MatrixCells>
            <Height>0.25in</Height>
          </MatrixRow>
        </MatrixRows>
        <MatrixColumns>
          <MatrixColumn>
            <Width>0.875in</Width>
          </MatrixColumn>
        </MatrixColumns>
        <DataSetName>DataSet1</DataSetName>
        <ColumnGroupings>
          <ColumnGrouping>
            <DynamicColumns>
              <Grouping Name="Category">
                <GroupExpressions>
                  <GroupExpression>=Fields!CategoryName.Value</GroupExpression>
                </GroupExpressions>
              </Grouping>
              <ReportItems>
                <Textbox Name="CategoryName">
                  <Style>
                    <PaddingLeft>2pt</PaddingLeft>
                    <BorderStyle>
                      <Default>Solid</Default>
                    </BorderStyle>
                    <TextAlign>Right</TextAlign>
                    <PaddingBottom>2pt</PaddingBottom>
                    <PaddingTop>2pt</PaddingTop>
                    <PaddingRight>2pt</PaddingRight>
                  </Style>
                  <ZIndex>3</ZIndex>
                  <rd:DefaultName>CategoryName</rd:DefaultName>
                  <CanGrow>true</CanGrow>
                  <Value>=Fields!CategoryName.Value</Value>
                </Textbox>
              </ReportItems>
            </DynamicColumns>
            <Height>0.25in</Height>
          </ColumnGrouping>
        </ColumnGroupings>
        <Width>2in</Width>
        <Top>0.125in</Top>
        <Left>0.125in</Left>
        <RowGroupings>
          <RowGrouping>
            <DynamicRows>
              <Grouping Name="Country">
                <GroupExpressions>
                  <GroupExpression>=Fields!Country.Value</GroupExpression>
                </GroupExpressions>
              </Grouping>
              <ReportItems>
                <Textbox Name="Country">
                  <Style>
                    <PaddingLeft>2pt</PaddingLeft>
                    <BackgroundColor>=iif(RunningValue(Fields!Country.Value,CountDistinct,Nothing) Mod 2, "AliceBlue", "White")</BackgroundColor>
                    <BorderStyle>
                      <Default>Solid</Default>
                      <Right>None</Right>
                    </BorderStyle>
                    <PaddingBottom>2pt</PaddingBottom>
                    <PaddingTop>2pt</PaddingTop>
                    <PaddingRight>2pt</PaddingRight>
                  </Style>
                  <ZIndex>2</ZIndex>
                  <rd:DefaultName>Country</rd:DefaultName>
                  <CanGrow>true</CanGrow>
                  <Value>=Fields!Country.Value</Value>
                </Textbox>
              </ReportItems>
            </DynamicRows>
            <Width>1in</Width>
          </RowGrouping>
          <RowGrouping>
            <DynamicRows>
              <Grouping Name="Count">
                <GroupExpressions>
                  <GroupExpression>=1</GroupExpression>
                </GroupExpressions>
              </Grouping>
              <ReportItems>
                <Textbox Name="Color">
                  <Style>
                    <PaddingLeft>2pt</PaddingLeft>
                    <BackgroundColor>=Value</BackgroundColor>
                    <BorderStyle>
                      <Default>Solid</Default>
                      <Left>None</Left>
                    </BorderStyle>
                    <FontSize>1pt</FontSize>
                    <Color>=Value</Color>
                    <PaddingBottom>2pt</PaddingBottom>
                    <PaddingTop>2pt</PaddingTop>
                    <PaddingRight>2pt</PaddingRight>
                  </Style>
                  <ZIndex>1</ZIndex>
                  <CanGrow>true</CanGrow>
                  <Value>=iif(RunningValue(Fields!Country.Value,CountDistinct,Nothing) Mod 2, "AliceBlue", "White")</Value>
                </Textbox>
              </ReportItems>
            </DynamicRows>
            <Width>0.125in</Width>
          </RowGrouping>
        </RowGroupings>
      </Matrix>
    </ReportItems>
    <Style />
    <Height>0.75in</Height>
</Body>
<TopMargin>1in</TopMargin>
<DataSources>
    <DataSource Name="Northwind">
      <rd:DataSourceID>26f1bf87-1fa6-4e77-8d1a-81b0cd940403</rd:DataSourceID>
      <ConnectionProperties>
        <DataProvider>SQL</DataProvider>
        <ConnectString>data source=.;initial catalog=Northwind</ConnectString>
        <IntegratedSecurity>true</IntegratedSecurity>
      </ConnectionProperties>
    </DataSource>
</DataSources>
<Code />
<Width>2.25in</Width>
<DataSets>
    <DataSet Name="DataSet1">
      <Fields>
        <Field Name="Country">
          <DataField>Country</DataField>
          <rd:TypeName>System.String</rd:TypeName>
        </Field>
        <Field Name="Qty">
          <DataField>Qty</DataField>
          <rd:TypeName>System.Int32</rd:TypeName>
        </Field>
        <Field Name="CategoryName">
          <DataField>CategoryName</DataField>
          <rd:TypeName>System.String</rd:TypeName>
        </Field>
        <Field Name="Year">
          <DataField>Year</DataField>
          <rd:TypeName>System.Int32</rd:TypeName>
        </Field>
      </Fields>
      <Query>
        <DataSourceName>Northwind</DataSourceName>
        <CommandText>SELECT     Customers.Country, SUM([Order Details].Quantity) AS Qty, Categories.CategoryName, DATEPART(yyyy, Orders.OrderDate) AS Year
FROM         Customers INNER JOIN
                      Orders ON Customers.CustomerID = Orders.CustomerID INNER JOIN
                      [Order Details] ON Orders.OrderID = [Order Details].OrderID INNER JOIN
                      Products ON [Order Details].ProductID = Products.ProductID INNER JOIN
                      Categories ON Products.CategoryID = Categories.CategoryID
GROUP BY Customers.Country, Categories.CategoryName, DATEPART(yyyy, Orders.OrderDate)</CommandText>
      </Query>
    </DataSet>
</DataSets>
<LeftMargin>1in</LeftMargin>
<rd:SnapToGrid>true</rd:SnapToGrid>
<rd:DrawGrid>true</rd:DrawGrid>
<Description />
<rd:ReportID>ab2c120b-3169-427d-8ad6-b8716f8c5101</rd:ReportID>
<BottomMargin>1in</BottomMargin>
</Report>

"All" Parameter Value

2004-07-28T02:09:00Z

Question:
How can I enable users to select "All" as the value of a query parameter?

Answer:
The simplest way is to modify your query to accept nulls and map "All" to null.

Step 1: Start with a report that already has a parameterized query and Valid Values queries.

Step 2: Edit your base query to account for nulls.
Whever you have something like this:
... AND Field = @Parameter ...
Replace it with this:
... AND (Field = @Parameter OR @Parameter is NULL) ...

Step 3: Edit your Valid Values queries to insert an explicit "All" and separate ids from labels
For example, for this original query:
     SELECT name FROM productcategory
Change it to this:
     SELECT name as ID, name as Label FROM productcategory
     UNION
     SELECT Null as ID, 'All' as Label

Step 4: Update the report parameters
Mark each parameter as nullable
Change the valid values settings to account for the new field names (if needed).

Step 5 (Optional): Update Valid Values queries to account for nulls.
If you have hierarchical parameters, in some cases, you may want the dependent parameters to be independently selectable when the parent parameter's value is "All". In this case, repeat step 2 for each of your Valid Values queries. Note: Don't do this if you have extremely large numbers of possible values, as performance will suffer and your users wouldn't be able to nagivate an excessively long list anyhow.

Update