Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Tips Database Forum Rss Feed

» Database Journal Home
» Database Articles
» Database Tutorials
MS Access
SQL Scripts & Samples
» Database Forum
» Slideshows
Free Newsletters:

News Via RSS Feed

Rss Feed

Database Journal |DBA Support |SQLCourse |SQLCourse2

Featured Database Articles


Posted Sep 29, 2004

MSSQL Server Reporting Services: Master Chart Reports: Track Exchange Rates in a Line Chart - Page 2

By William Pearson

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


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:


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

9.  Click OK.

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.

MS SQL Archives

Latest Forum Threads
MS SQL Forum
Topic By Replies Updated
SQL 2005: SSIS: Error using SQL Server credentials poverty 3 August 17th, 07:43 AM
Need help changing table contents nkawtg 1 August 17th, 03:02 AM
SQL Server Memory confifuration bhosalenarayan 2 August 14th, 05:33 AM
SQL Server Primary Key and a Unique Key katty.jonh 2 July 25th, 10:36 AM