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 3

By William Pearson

Create a New Chart Report

In this section, we will launch Reporting Services' Report Designer, and then create a new report with a dataset. Next, we will place the chart item on the report. Finally, we will designate and populate the report item.

Create a Blank Report

Let's begin by creating a blank report.

1.  Right-click the Reports folder in Solutions Explorer.

2.  Select Add from the context menu that appears.

3.  Click Add New Item from the cascading menu, as shown in Illustration 5.

Click for larger image

Illustration 5: Select Add --> Add New Item

4.  Click Report in the Add New Item dialog.

5.  Type the following into the Name box, replacing the default of Report1.rdl (or similar).


The Add New Item dialog appears, as shown in Illustration 6.

Illustration 6: The Add New Item Dialog Initial View

6.  Click the Open button at the bottom of the Add New Item dialog.

The design environment opens. We see the Data, Layout and Preview tabs appear in the Report Designer (As I mentioned in Master Chart Reports: Pie Charts in Reporting Services, in the views presented in many illustrations, I have docked many of my toolbars in places I find convenient. Your environment will probably differ somewhat, and so it may not appear identical to the illustrations).

The report has opened in Data View, as shown in Illustration 7.

Illustration 7: The Design Environment - Data View Tab (Compacted)

Set up a Data Connection and Create a Dataset

Our next step is to set up a Data Connection. As we have noted numerous times throughout our series, Reporting Services can connect with, and create the datasets it needs from, virtually any ODBC or OLE DB-compliant data source (in addition to the obvious MSSQL Server and MSAS data stores). .NET-based API's add the potential for other data sources, assuming that you have a legacy, or otherwise eccentric, scenario on your hands.

Let's set up a Connection, and create a Dataset within our practice example.

1.  Select New Dataset in the Dataset selector at the top of the Data tab, as depicted in Illustration 8.

Illustration 8: Select New Dataset in the Dataset Selector Data Tab

As soon as we click the New Dataset selection, the Data Link Properties dialog box appears, defaulted to the Connection tab.

2.  Type the name of the computer housing the targeted OLTP database, AdventureWorks2000.

(My server name, MOTHER1, appears in this article.)

3.  Select the radio button to the left of the authentication option that is appropriate for your environment.

(Mine is Windows NT Integrated security.)

4.  Select AdventureWorks2000 within the Select the database on the server selector.

The settings on the Connection tab of the Data Link Properties dialog should resemble those shown in Illustration 9.

Illustration 9: Data Link Properties Dialog Connection Tab

5.  Click the Test Connection button to verify connectivity to the data source.

We receive a message box, indicating a successful test connection, as shown in Illustration 10.

Illustration 10: Testing Positive for Connectivity ...

6.  Click OK to accept the settings we have made, and to close the Data Link Properties dialog.

Report Designer next presents us with the dataset design tool, based upon our newly connected source. We are immediately positioned to design our query, which brings us to the next step.

7.  Click the ellipses ("...") button to the right of the default dataset name of AdventureWorks2000, which appears in the Dataset selector, as shown in Illustration 11.

Illustration 11: Editing the New Dataset

8.  Enter CurrencyData for the name of the Dataset dialog box, replacing the default name of AdventureWorks2000.

(AdventureWorks2000 remains selected for the data source by default.) The Dataset dialog appears as shown in Illustration 12.

Illustration 12: Completed Dataset Dialog

9.  Click OK to accept the settings and return to the Data tab, which displays in the Generic Query Designer.

10.  Click the Generic Query Designer button, shown in Illustration 13, to "deactivate" it, and to shift, instead, to the Query Builder.

Illustration 13: Shifting to the Query Builder ...

The Query Builder appears. The Query Builder is composed of four distinct panes, each of which can be resized, or hidden (or "recalled") via the buttons atop the Query Builder and otherwise, to accommodate the needs of the developer. The Query Builder, with sectional panes labeled on blue, appears (compacted view) in Illustration 14.

Illustration 14: The Panes of the Query Builder

11.  Right click in the Diagram pane area of the Query Builder.

A context menu appears, as depicted in Illustration 15.

Illustration 15: Using the Context Menu to Add Tables to the Diagram Pane ...

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