ptg
281
Paging Report (Improved in 2008)
15
columns that you want to have and spacing between. Figure 15.3 shows a multicolumn
report design example.
Advanced Group Properties: RepeatOnNewPage, KeepTogether
(New in 2008), KeepWithGroup (New in 2008), and
HideIfNoRows (New in 2008)
These properties are accessible in the Advanced mode of the grouping pane and enable
you to do the following:
. RepeatOnNewPage: Repeat the group’s row header on every page where the group
has at least one row.
. KeepTogether: Force SSRS to attempt keeping the entire group together on a page,
instead of inserting a page break between. Not supported for column groups.
. KeepWithGroup: Help to keep the group’s header and footer together on the same
page, instead of orphaning to another page. The property can have one of the
following values:
. Before: Keep this static member (mostly footer) with the previous group in a
grouping pane. Note gray static members in the grouping pane.
. After: Keep this static member (mostly header) with the previous group in a
grouping pane.
FIGURE 15.3
Multicolumn report design.
From the Library of STEPHEN EISEMAN
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
ptg
282
CHAPTER 15 Advanced Report Formatting
. None: Let SSRS decide whether to use
Before

.
.
(###)###-####
formats a value of
1234567890
to the output
(123)456-7890
,
which is typical presentation of a phone number.
.
C
formats a value of
1234.56
to a typical presentation of currency
$1,234.56
.
.
yyyy-MM-dd HH:mm
formats
11:59PM
on
1/2/2006
to
2003-01-02 23:59
.
. Language: Controls locale formatting. For example, when set to
English (United
Kingdom)
, the currency sign in formatting changes from
$

(Go to Report)
Navigation
. Document Map
. Hide and Toggle Items
. Practical Application of Action
Items
O
ne of the main uses for navigation functionality is to
simplify navigation of large reports and navigation of report
hierarchies. To achieve this, report developers can add
hyperlink actions (or simply actions) to a report. Reporting
Services supports three types of actions:
. Drillthrough (Go to Report): Go to other reports.
This action also provides an opportunity to provide
parameters for the target report and the ability to
jump to the same report with different parameters.
This action is denoted with the
<Drillthrough>
element in a report’s Report Definition Language
(RDL).
. BookmarkLink (Go to Bookmark): Jump to other
areas (bookmarked) within the report. This action is
denoted with the
<BookmarkLink>
element in a
report’s RDL.
. Hyperlink (Go to URL): Jump to web pages and
other
HREF
constructs, such as mail and news. This

destinations. To illustrate this, imagine that you have a bookmark Bookmark10 located on
page 10 of your report and a table of contents (TOC) on page one of your report. One of
the TOC’s lines contains the
BookmarkLink
action involving Bookmark10. If you click this
action in the TOC, the focus of the view will switch to page 10. You can set
Bookmark
and
Document Map
via like-named properties in the Properties window.
TIP
Highlight an actionable item so that it is intuitive to a user that the item can be
clicked. Blue in color, underlined text items usually provide good highlights.
Hyperlink (Go to URL) Navigation
When a report developer adds the
Hyperlink
action, SSRS generates
HREF
to create a navi-
gable HTML link. For example, when you enter http://www.microsoft.com into a Go to
URL text box and set the value of
Textbox.Value
equal to
Visit Microsoft
, the following
is the link generated by SSRS when it renders the report to HTML format:
From the Library of STEPHEN EISEMAN
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
ptg
285

Hyperlink
(Go to URL) action, enter a valid (such as http://www.microsoft.com
or other described previously)
HREF
construct in the Go to URL action field. Figure 16.1
shows the Action property page that allows you to input the Go to URL action.
Action
has
the following corresponding RDL:
<Action>
<Hyperlink>http://www.microsoft.com</Hyperlink>
</Action>
Or
<Action>
<Hyperlink>={Expression}</Hyperlink>
</Action>
In the preceding code fragment, an
{Expression}
is any expression that evaluates to a
valid
HREF
construct as described previously, such as http://www.microsoft.com.
BookmarkLink (Go to Bookmark) Navigation
BookmarkLink
(Go to Bookmark) simplifies navigation for large reports. This action allows
navigation to a bookmarked line or page of the report. Keep in mind that you can associ-
ate multiple actions with a single bookmark link.
From the Library of STEPHEN EISEMAN
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
ptg

BookmarkLink
action is set, Report Designer adds the following RDL:
<Action>
<BookmarkLink>
={Expression}
</BookmarkLink>
</Action>
Drillthrough (Go to Report) Navigation
This type of action is commonly used when there is a need to have master (parent) and
detail (child) reports. An action, which takes users to a detail report, is created on the
master report.
To create a
Drillthrough
navigation (action), you need to set the Go to Report expression
on the Action tab of the item’s Properties dialog box. The Go to Report expression must
evaluate to a name of a report.
You can also specify parameters to pass to a report by clicking the Add button under the
Use This Parameters to Run the Report section (which becomes visible once you select Go
CHAPTER 16 Report Navigation
From the Library of STEPHEN EISEMAN
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
ptg
287
Document Map
16
to Report action on the Action tab). In the Parameters dialog box, you can enter a
constant name of a parameter and an expression that will assign a value to the parameter.
The following is an example of the RDL for the
Drillthrough
action:

<Label>={Expression}</Label>
Document map labels from report items are displayed on the same level (next after the
root level) and ordered in the order of the report item’s appearance on a report. The order
is based first on the position of the top side (vertical ordering) of a report item and then
on the position of the left side (horizontal ordering) of the report item.
From the Library of STEPHEN EISEMAN
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
ptg
288
The document map also supports a hierarchical display of labels. SSRS creates a hierarchi-
cal view on a document map when you set Document Map and Recursive Parent expres-
sions using the Advanced tab of the Group Properties dialog box. PDF shows labels of a
document map in the Bookmarks navigation panel. (In Adobe Reader 9.0, the panel is
accessible from View, Navigation Panels, Bookmarks menu.) Note that early versions of the
Adobe Reader did not support hierarchical bookmarks.
The document map has the same name as the name of the report. This name is used for
the root node of a document map. There are no options that allow changing the name of
a document map and only one document map is allowed per report.
Rendering of a document map by the Word rendering extension is a bit trickier and you
have to perform a couple of additional steps. The steps are necessary because the Word
rendering extension renders the document map as table entry fields. You can see table
entry fields as
{TC “Text” [switches]}
if you show hidden fields (you can do this by
using Ctrl-* shortcut) while viewing a Word document. To create a table of contents (TOC)
from table entry fields, you would perform an “insert table of contents” action and set
appropriate options. In Word 2007, you select the References tab on the Ribbon, click the
Table of Contents button, and select Insert Table of Contents from the drop-down list. At
this point, a Table of Contents dialog opens. Click the Options button, and in the Table of
Contents Options dialog box unselect everything except the Table Entry Fields check box.

From the Library of STEPHEN EISEMAN
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
ptg
289
Practical Application of Action Items
16
BIDS adds the following RDL for the
InitialToggleState
:
<ToggleImage>
<InitialState>true</InitialState>
</ToggleImage>
Reporting Services adds the following RDL for
ToggleItem
and
Hidden
. (When the item is
visible, the
<Hidden>
tag is not included.)
<Visibility>
<ToggleItem>{ToggleItem}</ToggleItem>
<Hidden>={Expression}or {true}</Hidden>
</Visibility>
Practical Application of Action Items
For illustrative purposes, imagine that the Adventure Works Internet sales department
wants to create an interactive product catalog. The initial screen of a report should not be
larger than a single page. A report must provide effective navigation through the products
(items in the catalog). A user of the catalog must be able to navigate through the product
category and subcategory hierarchies.

Text box
Table with 3 row groups,
including Details group
Row groups
Matrix with single column
group based on CategoryName
FIGURE 16.2
Navigation report Design view.
Create a report layout outline, as shown in Figure 16.2. This report uses Matrix (remember
that Matrix is a template for Tablix) to provide category navigation.
CHAPTER 16 Report Navigation
To show pictures from the database, add an Image report item from the toolbox to the last
column of the Details row in the table.
To show pictures from a database, right-click the cell with an image in it and select Image
Properties from the context menu. Set Select the Image Source to Database. Set Use This
Field to [ThumbNailPhoto] and set Use This MIME Type to image/jpeg.
Right-click the Matrix’s cell with
CategoryName
value in it and select Text Box Properties
from the context menu. On the Action tab (see Figure 16.3), select Go to Bookmark and
select [CategoryName] from the drop-down list. This adds the following expression to the
Select Bookmark action:
=Fields!CategoryName.Value
This bookmark will allow users to access an appropriate category name on a report. Apply
formatting so that a user can tell that this is a clickable item. For example, in the Text Box
Properties dialog box (under the Font tab), select Italic, Blue, Underline font.
From the Library of STEPHEN EISEMAN
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
ptg
291

Document map and its hierarchy.
Click the text box that contains the [SubCategoryName] expression and a group indicator.
Note that because it was a part of the first group that we created it has the name Group1.
Let’s set its name to
SubCategoryGroupTxt
.
Similarly, rename the text box with [CategoryName] to
CategoryNameTxt
.
From the Library of STEPHEN EISEMAN
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
ptg
293
Practical Application of Action Items
16
In the grouping pane, right-click the Details group, select Group Properties from the
context menu, and go to the Visibility tab. Set the When the Report Is Initially Run
option (initial visibility) to Hide. Check the Display Can Be Toggled by This Report Item
check box, and select SubCategoryGroupTxt from the drop-down list. Visibility informa-
tion should look similar to Figure 16.6.
For a clean display, set the
Hidden
property (from the Properties window) to
True
and set
the
ToggleItem
property to
SubCategoryGroupTxt
for detail cells in the Product Name and

(or Go to
Report),
BookmarkLink
(or Go to Bookmark), and
Hyperlink
(or Go to URL; general web
navigation).
Actions can be added to a Textbox, Image, Gauge, or Chart data series. Because a Tablix
can contain a Textbox, Image, Gauge, or Chart, those, in turn, can provide actionable
items within Tablix.
Any Reporting Services item can have Bookmark and Document Map labels associated
with it. Those labels assist in navigation by providing “landing marks” for navigation
destinations.
After a report developer has defined
Document Map
labels, SSRS automatically renders a
document map. Document maps are supported by HTML, Excel, Word, and PDF rendering
extensions.
The next chapter explains how SSRS works with multidimensional data sources and how it
integrates with Analysis Services and data-mining features of SQL Server 2008.
From the Library of STEPHEN EISEMAN
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
ptg
CHAPTER
17
Working with
Multidimensional Data
Sources
IN THIS CHAPTER
. Analysis Services Concepts

If an online purchasing transaction is slow, a user is likely
going to another site that offers better performance.
From the Library of STEPHEN EISEMAN
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
ptg
296
CHAPTER 17 Working with Multidimensional Data Sources
A DW usually has the following attributes:
. It is large in size (it is not uncommon to have multiterabyte data warehouses) and
might contain years of historical data. Part of the data might not be currently useful,
but provides a view into the company’s past and can be analyzed to determine why,
for example, online sales were successful in a certain time frame, or how well a
marketing campaign performed.
. It stores data in the matter understandable to business users as opposed to applica-
tions. In contrast, OLTP databases are designed to accommodate applications. A DW
is designed to answer business questions, such as sales numbers for a particular
region and time frame.
. It is updated on predetermined intervals (once daily, weekly, or monthly) from trans-
actional databases and ideally does not change previously stored historical data.
A subset of a DW is called a data mart. A DW usually contains data from a variety of
heterogeneous data sources. Data marts are designed to minimize the amount of data used
in processing and can contain a subset of data, based on, for example, time or geography.
Unified Data Model (UDM) was a new feature that first appeared in SQL Server 2005.
UDM greatly simplifies access to data and combines the best of relational and analytical
models.
UDM allows Reporting Services to get data from Analysis Services in a similar fashion as
from any relational data source. One way to think about UDM is as a view on data, which
allows “combining” data from various data sources (SQL Server relational and OLAP data-
bases, Oracle, Teradata, DB2, and so on), “defining” relationships between that data,
“defining” calculated fields, and mapping between original column names and newly

gated) data. This is because OLAP is specially tuned to perform aggregations. One of the
most useful OLAP modes is Multidimensional OLAP (MOLAP). MOLAP stores aggregated
data in an Analysis Services multidimensional structure, called a cube, which is highly
optimized to maximize query performance.
NOTE
This chapter discusses default OLAP aggregation mode: MOLAP. Analysis Services pro-
vides two modes in addition to MOLAP: Relational OLAP (ROLAP), which does not store
summaries and queries relational data for each Multidimensional Expressions (MDX)
query; and Hybrid OLAP (HOLAP, a combination of MOLAP and ROLAP). Details of vari-
ous modes are beyond this book’s scope.
A Transact-SQL (T-SQL) query against the AdventureWorks DW database to get a summary
by country would look like the following:
SELECT DimSalesTerritory.SalesTerritoryCountry AS [Country-Region],
SUM(FactInternetSales.SalesAmount) AS [Internet Sales-Sales Amount]
FROM DimSalesTerritory INNER JOIN FactInternetSales ON
DimSalesTerritory.SalesTerritoryKey = FactInternetSales.SalesTerritoryKey
GROUP BY DimSalesTerritory.SalesTerritoryCountry
A comparative multidimensional (or MDX; you can find more about MDX later in this
chapter) query to retrieve the same result would look like the following:
SELECT NON EMPTY
{ [Measures].[Internet Sales Amount]} ON COLUMNS,
NON EMPTY { [Customer].[Customer Geography].[Country]}
DIMENSION PROPERTIES
MEMBER_CAPTION,
MEMBER_UNIQUE_NAME ON ROWS
FROM [Adventure Works]
As you can see, for a simple aggregation the complexity of either query is fairly compara-
ble. The key difference is in the underlying structures that each query accesses.
At this point, you should not be concerned if you are not familiar with MDX. SQL Server
2008 has an extremely capable visual designer that makes creation of an MDX query a

for example, a customer, who has the following attributes: age, martial status, and wealth.
Creating a Data-Mining Model
Before creating a new model, you should do the following:
. Define the problem. What columns or attributes should the model predict?
. Determine the location of the data to be analyzed. The source of data could be a SQL
Server database or a cube.
TABLE 17.1
Query Results
Country-Region Internet Sales-Sales Amount
Australia 9061000.5844
Canada 1977844.8621
France 2644017.7143
Germany 2894312.3382
United Kingdom 3391712.2109
United States 9389789.5108
From the Library of STEPHEN EISEMAN
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
ptg
299
Data-Mining Concepts
17
. Decide the data-mining algorithm the model should use.
The data-mining model (DMM) designed to analyze the probability of a bike purchase for
a customer of a certain age could be created with the following code:
CREATE MINING MODEL [BikePurchasePrediction]
(
CustomerKey LONG KEY,
BikeBuyer DOUBLE DISCRETE PREDICT,
Age DOUBLE CONTINUOUS
)

Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
ptg
300
CHAPTER 17 Working with Multidimensional Data Sources
TABLE 17.3
Two-Dimensional Data
Internet Sales
Amount
Calendar
Year
Country-Region 2001 2002 2003 2004 Grand Total
Australia 1,309,047 2,174,285 3,033,784 2,563,884 9,061,000
Canada 146,830 621,602 535,784 673,628 1,977,844
France 180,572 514,942 1,026,325 922,179 2,644,018
Germany 237,785 521,231 1,058,406 1,076,891 2,894,312
For example
SELECT
age, PredictProbability([Bike Buyer])
FROM BikePurchasePrediction PREDICTION JOIN ProspectiveBuyer
ON BikePurchasePrediction.age = ProspectiveBuyer.age
MDX and DMX
MDX is an acronym for Multidimensional Expressions. It is a statement-based scripting
language used to define, manipulate, and retrieve data from multidimensional objects in
SSAS. MDX is similar in many ways to the familiar Structured Query Language (SQL)
syntax typically used with relational databases, but it is not an extension of SQL.
Data Mining Extensions (DMX) is a language that you can use to create and work with
DMMs in SSAS. You can use DMX to create new DMMs, to train these models, and to
browse, manage, and predict using those models. The DMX language is an extension of
SQL, to create and work with models.
Advanced OLAP Concepts