Create a Line Chart Report in Reporting Services
Objective and Business Scenario
In the following
sections, we will perform the steps required to create a line chart
report to meet a business need as expressed by a hypothetical group of information
consumers. We will base our report datasets on the AdventureWorks2000 sample
OLTP database that accompanies the installation of Reporting Services, to take
advantage of its easy accessibility to any organization installing Reporting
Services.
For purposes of our
practice procedure, we will assume that information consumers within the
Finance department of the AdventureWorks2000 organization have expressed
the need for a line chart report to support ongoing analysis surrounding
specific currency exchange rates. They specifically need a report that depicts the
Foreign - to U. S. Dollar exchange rates for British Pounds and
Euros (denominated as GBP and EUR, respectively, in the AdventureWorks2000
OLTP database). The comparative line chart will potentially be used for
other currencies in which the organization has developing interests in the
future, but for now, they are focusing only on these two.
Moreover, the
information consumers request that the line chart display end-of-month
rates for the months at which daily rates are captured in the database, as the
rate is of interest to them from a fixed asset valuation standpoint,
versus from the perspective of income recognition. They state that,
although their simple charting need has been met before with an enterprise
reporting application, they have grown weary of the recurring formatting issues
that erupt anytime they need to integrate the current chart reports with MS
Office applications, predominantly the MS Excel spreadsheets and accompanying
MS PowerPoint presentations they use to present various statistics to
management.
As part of our typical
business requirements gathering process, we listen attentively to the details, formulating,
in the background, an idea of the source data fields involved in creating the
requested line chart. Once we grasp the stated need, and confirm our
understanding with the intended audience, we begin the process of creating the
chart report to satisfy the information consumers.
Considerations and Comments
The
report that we will create involves the sample MSSQL Server 2000 database, AdventureWorks2000,
which accompanies the installation of Reporting Services. At the time of writing, the
Service Pack 1 update is assumed for Reporting
Services and the related Books Online and Samples.
For purposes of this
exercise, we will create a Reporting Services project within the Visual
Studio.Net 2003 Report Designer environment, within which we will work primarily
with a Chart data region, much as we did in our last article. Creating a
line chart is relatively straightforward, making the assumptions that
have become standard in this series: that you have the authority, access and
privileges, within both MSSQL Server and Reporting Services, needed
to establish a data connection and accomplish the steps involved, and that
performing these operations within the AdventureWorks2000 database
presents no other issues in your environment.
If the sample AdventureWorks2000
database was not created as part of the initial Reporting Services
installation, or was removed prior to your beginning this article, please see
the Reporting Services documentation, including the Installation Notes,
for the procedure to create the database, and direction to the appropriate
files. As of this writing, a copy of the samples can be obtained from the
installation CD or via download from the appropriate Microsoft site(s).
Hands-On Procedure
Preparation
Create a Reporting
Services Project
To
begin, we will launch Reporting Services' Report Designer, found in Microsoft
Visual Studio .NET 2003.
1.
Click Start.
2.
Navigate to
the Microsoft Visual Studio .NET 2003 shortcut in the Programs
group, as appropriate.
The
equivalent on my PC appears as shown in Illustration 1.
Illustration 1:
Beginning in Microsoft Visual Studio .NET 2003 ...
3.
Select File
-> New from the main menu.
4.
Click Project
from the cascading menu, as shown in Illustration 2.
Illustration 2:
Selecting a New Project
The New
Project dialog appears. Business Intelligence Projects appears in
the Project Types tree, indicating an installation of Reporting Services.
5.
Click Business Intelligence Projects in the Project Types tree, if necessary.
6.
Click Report
Project in the Templates list.
7.
Type the
following into the Name box, leaving other settings at default:
RS009
8.
Navigate to a
location in which to place the Report Project files.
The New Project
dialog appears, with our additions, as shown in Illustration 3.
Illustration 3: The New
Projects Dialog, with Addition
Our
new project appears in the Solution Explorer (upper right corner
of the Visual Studio .NET interface), as we see in Illustration 4.
Illustration 4: The New
Project Appears in the Solution Explorer
Having
created a Report Project, we are ready to proceed with creating
the new report.
» 