ptg
231
Tablix (New in 2008) = Table, Matrix, List
13
FIGURE 13.11
Simple Tablix design.
To properly color the entire row (or column), you can use the following expression in
the
BackgroundColor
property of the innermost group, where
RowGroup1
is the name of
the row group:
=IIF(Not InScope(“RowGroup1”), “LightGrey”, “White”)
Because a cell in a Tablix contains one or more report items, you format the result by
formatting those items. For example, a cell that presents textual information contains a
Textbox report item. By setting properties and formatting text in a Textbox report item,
you can manipulate the rendering outcome.
For example, you can conditionally hide row data by setting the
Hidden
property of each
cell to
True
. Chapter 14 shows an example of this.
We frequently use several properties of a Tablix in our work. To set these properties, select
the entire Tablix by either clicking the Tablix’s corner handler or selecting the Tablix from
the drop-down list on the Properties window. The frequently used properties are as follows:
. Filters: A set of filter expressions for a Tablix. Filters limit data displayed by a
Tablix much like the
WHERE
clause limits results of a query. Whereas in most of the

. SortExpressions: A set of sort expressions for a whole Tablix. You can also define
sort expressions for a group.
Practical Application of Report Items
It is time to put your knowledge to practical use. By now, you have sufficient knowledge
to put fairly complex reports together. Let’s create a Sales Order summary report.
Adventure Works’s management requested a report that displays selected properties of an
order header (ship and bill to addresses, contact information, and billing summary) and
selected properties of an order’s line items (product name, unit price, order quantity, and
line total). Adventure Works requires each report to have a company logo. To meet these
requirements, let’s complete the following steps:
1. Create a new report. For the purpose of this exercise, we will reuse the
AdventureWorks shared data source that we created in earlier chapters. From the
Report Data window, select New, Data Source. Name the data source
AdventureWorks
, select the Use Shared Data Source Reference option and choose
AdventureWorks. (Yes, both data sources can have the same name.)
2. In the Report Data window, right-click the AdventureWorks data source and select
Add Dataset. Name the data set
Order_Header
.
Order_Header
will contain data
selected from a join between
SalesOrderHeader
,
Address
, and
StateProvince
tables.
3. To have a more complete picture of an order and include both shipping and billing

table:
From the Library of STEPHEN EISEMAN
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
ptg
233
Practical Application of Report Items
13
OrderDate
,
TaxAmt
,
SubTotal
,
Freight
,
TotalDue
,
Comment
,
ShipDate
. We also
included the following fields from
Address
(and
StateProvince
) tables:
AddressLine1
,
City
,

6. To retrieve a specific order, let’s use parameter
@SalesOrderNumber
in the
WHERE
clause of both data sets:
WHERE
SalesOrderHeader.SalesOrderNumber = @SalesOrderNumber).
The resulting queries are as follows:
Order_Header
SELECT Sales.SalesOrderHeader.OrderDate, Sales.SalesOrderHeader.TaxAmt,
Sales.SalesOrderHeader.SubTotal, Sales.SalesOrderHeader.Freight,
Sales.SalesOrderHeader.TotalDue, Sales.SalesOrderHeader.Comment,
Sales.SalesOrderHeader.ShipDate, BillToAddress.AddressLine1 AS
BillAddressLine1,
BillToAddress.City AS BillCity, BillToAddress.PostalCode AS BillPostalCode,
StateProviceBill.StateProvinceCode AS BillStateProvinceCode,
ShipToAddress.AddressLine1 AS ShipAddressLine1,
ShipToAddress.City AS ShipCity, ShipToAddress.PostalCode AS ShipPostalCode,
StateProviceShip.StateProvinceCode AS ShipStateProvinceCode
FROM
Sales.SalesOrderHeader
INNER JOIN Person.Address AS BillToAddress ON
Sales.SalesOrderHeader.BillToAddressID =
BillToAddress.AddressID AND
Sales.SalesOrderHeader.ShipToAddressID = BillToAddress.AddressID AND
Sales.SalesOrderHeader.BillToAddressID = BillToAddress.AddressID AND
Sales.SalesOrderHeader.ShipToAddressID = BillToAddress.AddressID
INNER JOIN Person.StateProvince AS StateProviceBill ON
BillToAddress.StateProvinceID = StateProviceBill.StateProvinceID
INNER JOIN Person.Address AS ShipToAddress ON

Logo
. (Refer back
to Figure 13.1 to see the Image Properties dialog box.)
8. Add a list by dragging a List item from the Toolbox. As you remember, List is a
template for Tablix. You can take advantage of the
Dataset
property of the List item
to avoid typing scope resolution for each of the simple report items, such as
Textboxes, included on the List report item.
9. As an experiment, drag and drop the
ShipCity
field of
Order_Header
outside of the
List item. Note the value of the text box outside of the list is
=First(Fields!ShipCity.Value, “Order_Header”)
. As a comparison, drag and drop
the
ShipCity
field on the list. Note the value of the created text box is
=Fields!ShipCity.Value
. Also note that the
DataSetName
property of the list is now
set to
Order_Header
, and it was blank originally. Be careful when dropping fields
from other data sets to a list. If you do so, BIDS will update
DataSetName
to the data

changed to the name of the field.
14. To summarize line-item charges, right-click the detail row and select Insert Row,
Outside Group Below from the context menu. This row becomes a footer of the table.
15. In the rightmost cell of the row, enter the following summarization expression:
=Sum(Fields!LineTotal.Value)
.
The resulting design-time view of the report should look similar to Figure 13.12.
Chart Report Item (Improved in 2008)
A Chart report delivers a graphic presentation of data from a single data set. Chart has
comprehensive functionality and has similar capabilities to an Excel chart, including a
variety of chart types, 3D effects, trend lines, and more.
FIGURE 13.12
Design picture of the Sales Order Summary repor t.
From the Library of STEPHEN EISEMAN
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
ptg
236
CHAPTER 13 Working with Report Items
Microsoft significantly overhauled chart capabilities in SSRS 2008 and added the following:
. New chart types, such as bar/column cylinder, pyramid, funnel, polar, radar, stock,
candlestick, range column, range bar, smooth area, smooth line, stepped line, box
plot, Pareto, and histogram.
. Secondary axes support.
. Calculated series functionality that allows you to select 15 commonly used calcula-
tions, including statistical analysis, moving averages, and financial indicators.
. More control over common chart elements like
Legends
,
Titles
,

aggregate expressions are syntactically allowed. However, the result contains the last value
of a field rather than a summary value for a group and, therefore, produces an unexpected
result. Report Designer automatically adds an aggregate function, but changes are allowed.
To verify or change the data value expression, you can right-click a field you added and
select Series Properties from the context menu.
From the Library of STEPHEN EISEMAN
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
ptg
237
Chart Repor t Item (Improved in 2008)
13
Data field
Minor gridlines
appear here
when set up
Category field
Axis label categoryAxis label categoryAxis label categoryAxis label categoryAxis label category
Data Point label appears like this when set up
Minor tick marks appear between major tick marks when set up
Major tickmark
Major tickmarkMajor tick mark
Major gridline
Major gridlineMajor gridline
Legend
98.5
Axis label value
Series
Data Point marker will be here when set up
FIGURE 13.13
Design-time picture of a chart.

missing point or simply skipping a display of a missing value altogether.
Table 13.6 provides partial RDL of Chart Data. From this point forward in this book, the
section surrounded by the
<ChartData>
tag is abbreviated as
{CHART DATA}
.
TABLE 13.6
Partial Set of Tags for Char t Data RDL
Element Explanation
<ChartData>
Begin the Chart Data section.
<ChartSeriesCollection>
Collection of series. Each series in a collection has
associated data points and describes how those
points look on a chart.
<ChartSeries Name=”Standard-
Cost”>
<ChartDataPoints>
<ChartDataPoint>
<ChartDataPointValues>
<Y> =Sum(Fields!Standard-
Cost.Value)
Names comes from a data field associated with a
series, the value from the
StandardCost
field.
From the Library of STEPHEN EISEMAN
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
ptg

=Sum(Fields!ProductID.Value,
“ProductCostHistory”)
Each point on chart can have a label. It is common to
see an actual value next to a data point.
<ChartMarker>
Allows formatting a marker. A marker is a graphical
highlight of a data point on a graph. On a line chart, a
marker enables you to highlight the difference
between a connector line and the actual data.
<Type>Line</Type>
Chart type. In this case, it is
Line
.
<ChartEmptyPoints>
Describes how to handle empty or null data in a
series.
<ValueAxisName>Primary
<CategoryAxisName>Primary
Axes and series association.
From the Library of STEPHEN EISEMAN
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
ptg
240
CHAPTER 13 Working with Report Items
Chart Category
Chart Category Groups is the optional mechanism of grouping data that provides the
labels for chart elements.
For example, in a column chart, Country Name fields placed in the Category region gener-
ate country labels for x-axes (United States, Italy, and so forth).
You can nest categories. Multiple categories nest x-axes labels. For example, in a column

=
Default
. You will need to change
ChartArea, Name
property to associate series with a
different Chart area.
While you can combine most of the charts types (like line and column) on a single Chart
area, for some (such as bar, polar, and shape) you may need to add a new area to accom-
modate them. Table 13.7 provides a partial list of a chart area’s RDL elements.
From the Library of STEPHEN EISEMAN
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
ptg
241
Chart Repor t Item (Improved in 2008)
13
Chart’s RDL
A rudimentary chart is described by the following structure:
Chart Name=”chart1”>
{CHART SERIES}
{CHART CATEGORY}
{CHART AREAS}
{CHART LEGENDS}
{CHART TITLES}
{CHART DATA}
{LOCATION}
<DataSetName>DataSet1</DataSetName>
</Chart>
A real-life chart has additional elements in its RDL. These elements describe graphical
presentation properties for a chart.
Reporting Services supports the following chart types: area, bar, column, line, polar, range,

LabelsAutoFitDisabled>
Properties of labels for each axis.
LabelsAutoFit
ensures that labels do not overlap. When
AutoFit
is disabled, you can rotate a label yourself so that
it does not overlap.
<ChartValueAxes>
<ChartAxis Name=”Primary”>
Describes the y-axes of a chart (primar y and
secondary) .
TABLE 13.7
Partial List of Elements for a Char t Area’s RDL
Element Explanation
From the Library of STEPHEN EISEMAN
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
ptg
242
CHAPTER 13 Working with Report Items
Best Practices
Chart design best practices can be summed up as this: Make a picture that is worth a thou-
sand words. With that in mind, a report designer wants to make sure that a chart is
simple, meaningful, and efficient. A good chart:
. Includes relevant data (excludes irrelevant). For example, it does not make sense to
chart daily values if your business client wants to see quarterly aggregations of data.
Of course, as needed, you can allow your customer to drill through the data to deter-
mine whether a spike in the revenue is a result of the entire quarter or a single week
when a company had a successful marketing campaign.
TABLE 13.8
Chart Types

values.
Scatter XY
Bubble, 3D bubble
Displays data as a set of points in
space.
Displays data as a set of symbols
whose position and size are based on
the data in the chart.
Shape Pie, exploded pie, doughnut, exploded
doughnut, funnel, pyramid, and 3D varia-
tions.
Displays data as percentages of the
whole.
From the Library of STEPHEN EISEMAN
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
ptg
243
Practical Application of a Chart
13
. Is clear to read and does not have overlaps. All labels, including axes and data point,
are spaced appropriately and do not overlap. You either minimize the number of
data points or format labels appropriately to avoid overlaps.
. Clearly marks empty values to avoid unclear gaps: Is this value zero or missing?
. Displays series of data and not a single value. A Gauge report provides better graphi-
cal representation of a single value.
Practical Application of a Chart
Let’s apply the knowledge from this chapter to create a report.
To create a report that displays sales by country and by year, including graphical presenta-
tion of sales data, complete the following steps.
1. Similar to steps presented in the “Practical Application of Report Items” section of

from the context menu. Click the Number tab. This tab allows you to format axis
From the Library of STEPHEN EISEMAN
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
ptg
244
CHAPTER 13 Working with Report Items
labels. Choose appropriate formatting. We have chosen the options shown in
Figure 13.15.
5. Click the x-axis label to select it. Right-click and select Axis Properties from the
context menu. A Category Axis Properties dialog box will display. Click the Labels
tab. Notice the Enable Auto-Fit selections (see Figure 13.16). You can experiment with
options and disable Auto-Fit, choosing instead to rotate labels by a specified angle.
6. Preview the results. Suppose we manage U.S. sales and by looking at the chart we see
that somehow 2004 was a bad year as compared to 2003. We also see that this was
the case across all counties. Is this a global recession or another anomaly? Let’s
design a chart that shows us the monthly breakdown of the U.S. sales.
7. Drag and drop another Chart item onto a report. In this chart, we will present only
U.S. sales aggregated on a monthly basis.
8. Add a new data set based on the following query. Note that the query is essentially
the same as the earlier query, but with an added
Month
field and
HAVING
clause for
the United States (changes in bold):
SELECT
SUM(SOH.TotalDue) AS Sales,
DATENAME(yyyy, SOH.OrderDate) As Year,
MONTH(SOH.OrderDate) AS Month,
A.Name AS CountryName

Calculated Series option from the context menu.
From the Library of STEPHEN EISEMAN
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
Download at WoweBook.com
ptg
246
CHAPTER 13 Working with Report Items
13. Select Exponential Moving Average and use 12 periods to better see annual trends.
Also check Start from First Point to see the trend line starting from the beginning of
the graph and not 12 periods after.
14. Click the Border tab and set the line width to 3 points. This will make the trend line
easier to view. Click OK to close and preview. You should see something similar to
Figure 13.17.
Gauge Report Item
A Gauge report is a great tool to graphically display key performance indicators (KPIs). In
the previous version of SSRS, you had to use a workaround and display various images,
depending on the state of the KPI. (For example, for a thermometer, you had to display
four images of a thermometer depending on what quartile the temperature value was in.)
You can still use the same technique in this version, especially if there is no gauge avail-
able to satisfy your needs. For example, because a smiley-face gauge is not available, you
would instead display an image of a smiley face when the company is meeting its revenue
targets and a sad face when it is not.
Figure 13.18 shows a design view of a gauge.
SSRS includes linear and radial charts. You select a gauge type when you add a Gauge item
to your report. Because a gauge consists of multiple components, you cannot change the
type of gauge after it has been added to a report. However, you can manipulate individual
FIGURE 13.17
Chart at work.
From the Library of STEPHEN EISEMAN
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.

Count()
for non-numeric fields.
Table 13.9 lists some of the more commonly leveraged properties of a gauge.
TABLE 13.9
Gauge Properties
Property Action
MaximumValue
and
MinimumValue
Ending and beginning value of the scale
StartValue
and
EndValue
Move, expand, and contract the gauge’s range
StartWidth
Convert the beginning of the gauge range to a pointy or fat
shape
From the Library of STEPHEN EISEMAN
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
ptg
248
CHAPTER 13 Working with Report Items
You can add multiple gauges to a gauge panel. A panel is a container for gauges.
Chapter 17, “Working with Multidimensional Data Sources,” includes an example of a
gauge in use.
Report Body Versus Page Header/Footer
The report body can contain any SSRS items, including data regions. The page header and
footer can only contain independent report items, such as Textbox, Line, and Rectangle.
More complex page header and footer functionality can be implemented with Tablix and
the

footer). Those properties control whether a header or footer is rendered on the first and
last pages and are pretty much self-explanatory.
Summary
Report items are the presentation elements within SSRS.
Data regions function as a repetitive display of rows, groups of rows, or columns from a
data set associated with a region. Data regions include Tablix (Table, Matrix, List), Chart,
and Gauge. Data regions cannot be included in page headers or page footers.
Other report items are used for display purposes and are commonly called independent
report items. These items include Line, Rectangle, Textbox, and Image.
From the Library of STEPHEN EISEMAN
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
ptg
249
Summary
13
Data regions and independent report items support navigation; see Chapter 16 for more
information.
Containers can include other reporting items. Items placed in a container become the
container’s children, and the container becomes the parent. Tablix, Rectangle, Report
Body, Page Header, and Page Footer are containers.
In the following chapter, you build on this knowledge by learning how to group, sort, and
aggregate data within a given scope of a data region. By learning how to use report items
and group data effectively, you will be able to create advanced reports in no time.
From the Library of STEPHEN EISEMAN
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
ptg
This page intentionally left blank
From the Library of STEPHEN EISEMAN
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.