first objective is to create a copy of the Sales Reason Comparisons sample report, within which we will perform the
addition and setup of the Simple XY Chart
data region, for the reasons noted above. (We typically work with a similar report
copy in articles that are focused on the individual chart types elsewhere in
the series.) We will perform our practice
session from inside the MSSQL Server Business Intelligence Development
Studio. For more exposure to the Business Intelligence Development
Studio itself, and the myriad design, development and other evolutions we
can perform within this powerful interface, see other articles in this column,
as well as within my Database Journal series Introduction to MSSQL Server
In this article, we will be commenting only on the features relevant to our
immediate practice exercise, to allow us to keep to the focus of the session
Preparation: Create a Clone Report within the Reporting Services Development Environment
As we have noted, for
purposes of our practice session, we will create a copy of the Sales Reason Comparisons report, one of several samples
that are available with (and installable separately from) the MSSQL Server
2005 integrated business intelligence suite. Making preparatory
modifications, and then making the enhancements to the report to add the
functionality that forms the subject of our lesson, can be done easily within
the Business Intelligence Studio environment. Working with a copy
of the report will allow us the luxury of freely exploring our options, and will
leave us with a functioning example of the specific approach we took, to which
we can refer in our individual business environments.
Open the Sample Report Server Project
For purposes of our
practice session, we will open the AdventureWorks Sample Reports
project, which contains the sample reports that ship with the Reporting
Services component of the MSSQL Server 2005 suite. We will complete
our practice session within the sample project, so as to save the time required
to set up a development environment from scratch within the Business
Intelligence Development Studio.
To open the AdventureWorks
Sample Reports project, please see the following procedure in the References
section of my articles index:
Ascertain Connectivity of the Shared Data Source
ensure we have a working data source. Some of us may be running side-by-side
installations of MSSQL Server 2000 and MSSQL Server 2005. This
means that our installation of the latter will need to be referenced as a server
/ instance combination, versus a server name alone. (The default for
the Adventure Works DW project samples connection is localhost,
which will not work correctly in such a side-by-side installation, as MSSQL
Server 2000 will have assumed the identity of the local PC by default.)
If you do not know how
to ascertain or modify connectivity of the Analysis Services data
source, please perform the steps of the following procedure in the References
section of my articles index:
Create a Copy of the Sales Reason Comparisons Report
We will begin with a copy
of the Reporting Services 2005 Sales Reason Comparisons OLAP
report, which we will use as a basis for our Line chart type practice
exercise. Creating a
clone of the report means we can make changes to select components (perhaps
as a part of later exploration with our independent solution), while retaining
the original sample in a pristine state for other purposes, such as using it to
accompany relevant sections of the Books Online, and other
documentation. Such uses may form a part of learning more about Reporting
Services (particularly an OLAP report using an Analysis Services data
source), and other components of the Microsoft integrated business
intelligence solution in general.
If you do not know how
to create a copy of an existing report, please perform the steps of the
following procedure in the References section of my articles index:
clone Analysis Services report file within our Reporting Services
2005 Project, we are ready to begin our hands-on practice session with the Line
chart type, within which we will accomplish in the next section.
Preparation: Modify the Analysis Services Report Clone for Use within Our Practice Session
next make a few modifications to prepare the report for our practice session. Lets open the report clone we
created above in Layout view (for those of us not already there), upon
which we can commence our overview steps.
in the Solution Explorer.
from the context menu that appears, as shown in Illustration 1, as
Illustration 1: Opening
the New Report ...
opens in Layout
Filter the Primary Dataset to Limit the Data Retrieved
we will modify the primary dataset underlying the existing report,
called ProductData. We will simply place filters on returned
data to limit the size and complexity of our ultimate report.
Click the Data
the ProductData dataset appears within the data selector.
On the Metadata
tab, within the pane that appears under the dataset selector (which now
indicates ProductData), expand the Date dimension by clicking the
+ sign to its immediate left.
Expand the Calendar
folder that appears underneath the newly expanded Date dimension.
Expand the Date.Calendar
hierarchy that appears underneath the newly expanded Date dimension.
Click the Month
level to select it.
Drag the Month
level to the Query pane, dropping it to the immediate left of the Sales
Reason column that is already in place.
Click the Date.Calendar
Year hierarchy to select it.
Drag the Date.Calendar
Year attribute hierarchy to the Query pane, dropping it to the
immediate left of the newly added Date.Calendar Quarter of Year column.
pane, with the newly added Month and Year columns, appears as
partially presented in Illustration 2.
Illustration 2: The Query Pane with the Newly Added Column (Partial View)
Click the Edit
Selected Dataset (...) button to the immediate right of the Dataset
selector, as depicted (circled) in Illustration 3.
Illustration 3: Editing the Dataset ...
multi-tabbed Dataset dialog opens.
Click the Filters
In the first
row of the Filters box, within the leftmost Expression column, select
=Fields!Calendar_Year.Value, as shown in Illustration 4.
Illustration 4: Select =Fields!Calendar_Year.Value in the Expression Column ...
In the Operator
column (to the immediate right of the Expression column), select the =
Type (or cut
and paste) the following into the Value column (to the immediate right
of the Value column):
we are stating that we want only Calendar Year equal to CY 2003,
we know we will retrieve only one years data into the dataset (the Adventure
Works cube contains Calendar Years 2001 through 2004).
tab of the Dataset dialog appears as presented in Illustration 5.
Illustration 5: The Filters Tab of the Dataset Dialog with Our Additions ...
to accept our addition, and to dismiss the Dataset dialog.
Modify the Report Layout to Accommodate Our Focus
we will make some changes to the report layout to more easily accommodate the
focus of our practice session. We will start with a larger report canvas.
Click the Layout
tab atop the Report Designer.
Click the report
body at some point below the matrix data region that is in place.
bar assumes the focus (becomes darker). Body also appears in the Properties
pane (by default to the lower right of the design environment). The point
here is to ascertain that the report body is, indeed, selected.
cursor over the lower edge of the report body, until it becomes a
report canvas to about the 4-1/2 point on the scale on the left
side of the Layout tab, stretching it downward to create empty space
below the matrix data region on the report body, as depicted in Illustration
Illustration 6: Stretch the Canvas Downward to Create Empty Space in the Report Body
from this point, we will be performing procedures that relate only to the chart
type with which we are working in this article, and because we create similar
files for different chart types in other articles, lets rename the .rdl file
to clearly associate it with this article and the Line chart type.
from the main menu.
DBJ_OLAP_Report.rdl As ....
following into the File name box of the Save File As box that
RS068_Simple XY Chart
Click the Save
button in the lower right corner of the Save File As box.
File As box is dismissed and we see the new name appear in the Solution
Explorer. We are now ready to begin the procedural section of our practice