About the Series ...

This is the ninth article of the series MSSQL Server 2000 Reporting Services. The series is designed to introduce MSSQL Server 2000 Reporting Services ("Reporting Services"), with the objective of presenting an overview of its features, together with many tips and techniques for real-world use. This column also serves as a vehicle for sharing my conviction in Reporting Services' role as a new paradigm in enterprise reporting. As I advise clients on a more and more frequent basis these days, this is the future in a big way. I hope you will consider my input valuable, and that you will investigate closely the savings and advanced functionality that will soon be available to anyone with an MSSQL Server 2000 (and beyond) license.

Important: For information concerning the applications to which you will require access to benefit the most from our series, please see our initial Database Journal article, A New Paradigm for Enterprise Reporting.

It is assumed that you have access and the other rights / privileges required to complete the articles within the series. For details on the specifics of the adjustments necessary to quickly allow full freedom to complete the exercises in this and subsequent articles, as well as important assumptions regarding rights and privileges in general, please see earlier articles in our series, as well as the Reporting Services Books Online.

Overview

In our last article, Pie Charts in Reporting Services, we began the Master Chart Reports subset of our MSSQL Server 2000 Reporting Services series. This "mini-series" will demonstrate how to create chart reports of various types, and how to exploit the rich and flexible features contained in Reporting Services that enable us to make report data more meaningful, and easier to understand, from the perspective of our information consumer audiences.

As we have seen in previous articles, and as we will show in many prospective articles within this series, Reporting Services enables us to present both summarized and detailed data in colorful, easy-to-read charts of various designs. We can choose from a number of chart layouts and types within the Reporting Services chart data region options. (As we have discussed in earlier articles, a data region is an area on a report that contains data from a data source that is repeated. The types of data regions are list, matrix, table, and chart.) As we have begun to see in our exploration of charts, as well as other data regions within Reporting Services, already, and as we will see through in-depth, practical exercises in coming articles, we can also extend the value of our reports in myriad ways. Among these ways, just for starters, are the capability to format chart and other objects in a host of ways, to drill down to see the details behind the graphical / numerical summaries, to combine chart reports with other types of reports, and to access myriad other options in the powerful Reporting Services tool set.

The focus of many of my "introductory" articles is a full set of procedures that are designed to underlie a more in-depth study of specific property settings, and so forth, in subsequent articles. My objective is to allow a reader to complete a report, or a report component, in a manner that is insulated from non-linear distractions. This, I hope, serves as a complement to the digital documentation that ships with the application, and which focuses more on definitions and purposes of fields and settings than on building a specific kind of report from scratch. The ultimate objective, again, is to provide hands-on opportunities to learn overall, start-to-finish procedures, before homing in on specific options of interest, although we will certainly deal with a set of these options in each exercise as a part of completing the stated objectives of the session.

In this article, our exploration of chart reports will move into an examination of the line chart. Line charts are another popular chart type, and are likely to be familiar to the majority of us, if not in the context of report authoring, then at least as an information consumer. Just as we saw was the case with the Reporting Services pie chart item in our last article, we will find that the line chart item is both easy to use and feature endowed. In this session we will:

• Create a chart report in Report Designer;
• Create an underlying dataset;
• Locate a chart item on the new report;
• Assign the line chart type to the item;
• Populate the chart item with the required data.
• Practice the use of various properties available to the line chart;
• Preview the report to verify its operation.

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.

The equivalent on my PC appears as shown in Illustration 1.

The New Project dialog appears. Business Intelligence Projects appears in the Project Types tree, indicating an installation of Reporting Services.

RS009

The New Project dialog appears, with our additions, as shown in Illustration 3.

Our new project appears in the Solution Explorer (upper right corner of the Visual Studio .NET interface), as we see in Illustration 4.

Having created a Report Project, we are ready to proceed with creating the new report.

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.

RS009_LineChart

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

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.

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.

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

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

(Mine is Windows NT Integrated security.)

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

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

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.

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

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.

A context menu appears, as depicted in Illustration 15.

The Add Table dialog appears.

• Currency
• CurrencyRate

The tables appear selected in the Add Table dialog as shown in Illustration 16.

The Add Tables dialog remains open, while the tables we have selected appear on the Diagram pane, allowing us to make further choices. We will close it for now.

The Add Table dialog disappears, leaving the view of the Diagram pane with the two added tables. One of the automatic joins (there are two in place) needs to be deleted, before selecting fields for the dataset.

Our selections appear in the Diagram pane as depicted in Illustration 17.

We see the column names appear in the Grid pane. We will next modify a couple of the field names we have selected to make them a bit more useful.

Home Currency

Foreign Currency

GBP

EUR

The relevant portion of the Grid pane, with our Alias and Criteria additions, appears as shown in Illustration 19.

The data populates the Results pane, appearing similar to that depicted in Illustration 20. We will discuss the rate dates, circled in red, momentarily.

We can see the SQL we have just created, in the SQL pane, beneath the Grid pane. We often simply input SQL in my articles, as this allows us to reach the intended main subjects more rapidly, but, as we can see, the Query Builder makes it easier for those of us who are not familiar with SQL to create queries without having to learn the language first. The Query Builder is more than adequate to support the creation of many business reports, so if you come across any assertions that "Reporting Services requires report authors to know SQL," you can discount the statement out of hand.

The more SQL we have under our belts, the more likely we will be more efficient authors, particularly in advanced query generation (some queries cannot be created through the Query Builder, due to complexity or other complications. However, being fluent in SQL is certainly not a prerequisite to creating robust and useful reports, as many of my articles will demonstrate.

We now have a final refinement to make to the query. The information consumers specified that they need exchange rates as of the last day of each month. The dates that we see in the results dataset, circled in red in Illustration 20 above, reflect numerous dates within the months whose data resides in the database. We need to generate the exchange rates for the last day of the months in the results set only. We will manage this with an addition to the WHERE clause in the SQL, initially created when we specified the GBP and EUR currencies above. To do this, we will use the AND Keyword, as we shall see in the next step.

AND (CurrencyRate.CurrencyRateDate = CONVERT(CHAR, DATEADD(ss, - 1, 



   DATEADD(mm, DATEDIFF(mm, 0, CurrencyRate.CurrencyRateDate) + 1, 0)), 101))

The complete query, with our adjustments, should appear as follows:

SELECT  CurrencyRate.CurrencyRateDate, CurrencyRate.FromCurrencyCode AS [Home 



   Currency], CurrencyRate.ToCurrencyCode AS [Foreign Currency], 

                      

CurrencyRate.EndOfDayRate, Currency.Name





FROM   Currency INNER JOIN



   CurrencyRate ON Currency.CurrencyCode = CurrencyRate.ToCurrencyCode





WHERE  (CurrencyRate.ToCurrencyCode = 'GBP') OR(CurrencyRate.ToCurrencyCode = 'EUR') 



   AND (CurrencyRate.CurrencyRateDate = CONVERT(CHAR, DATEADD(ss, - 1, 



      DATEADD(mm, DATEDIFF(mm, 0, CurrencyRate.CurrencyRateDate) + 1, 0)), 101))

The purpose of the addition is to filter the results, once more, for the data that is associated with the dates that reflect end-of-month dates. The DATEADD function is useful for many such scenarios when working with relational data in T-SQL, as are other components of the expression we have added. Its purpose here is to generate the last day of the month for any month that has dates appearing in the data (contained in the CurrencyRateDate field).

The function generates the last day of the month for a given CurrencyRateDate by 1) using DATEDIFF to compare the CurrencyRateDate with 01/01/1900, and then 2) returning the number of intervals that result, 3) adding one month. It thus 4) delivers the first day of the month following the CurrencyRateDate, whereupon it 5) subtracts one second to "roll back the date to the day immediately preceding. The final result is the last day of the month. This approach works regardless of the number of days that are contained in the month of the CurrencyRateDate under consideration.

The dataset we have generated now contains the ingredients for a report that meets the expressed need of the information consumers. We see the currency rates for the currencies for which the consumers have manifested interest, for the end-of month dates for any month with rate data in the data source. We are ready to begin constructing the report that will present this data in a comparative line chart.

Locate the Chart Item on the Blank Report

The process of building a chart report consists of dragging the chart item onto the Layout tab, and adjusting it, while setting properties as appropriate to meet the report specifications. We will accomplish this in the following steps.

The toolbox window should appear similar to that shown in Illustration 21. Mine is pinned to the upper left corner of the design environment, where I find it most convenient. (I also dock my Fields and Server Explorer panes in this area to maximize design real estate, as an aside.)

The cursor becomes a small chart icon in combination with crosshairs when held above the layout. This indicates that we can click to "anchor" the point, from which we wish to draw the box that the chart will inhabit.

The select / grab, anchor and size process, consisting of three primary steps, is shown in Illustration 22 below.

The box should appear at the perimeter of the Layout tab.

Because the chart is the only item we intend to place in the report, it should extend almost completely over the report layout, once it appears.

Specify Line Chart Type, and Populate Chart item to Meet Requirements

Specifying the Line Chart type is now required for the generic chart item we have put in place. Since this designation occurs in the same physical location as other item property settings, we will accomplish it as part of the general setup of the chart item. Our next steps focus upon simply dragging fields from the Fields window. Ensure that the Fields window appears, either fixed in place or as a dynamic tab (as mine appears in Illustration 21 above), for easy access in accomplishing the next steps.

The fields are depicted, circled, in Illustration 24, within the sections into which we are dropping each.

The Chart Properties dialog box appears, defaulted to the General tab.

Currency Rate Activity

The Style Properties dialog appears, with our settings, as shown in Illustration 25.

We return to the General tab.

The Edit Chart Value dialog box appears.

The Edit Chart Value dialog appears, with our settings, as depicted in Illustration 26.

We return to the Data tab. Let's set the title information for the X- and y- axes, at this point.

The Style Properties dialog appears, with our settings, as shown in Illustration 27.

We return to the X-Axis tab, which, at this stage, appears as depicted in Illustration 28.

The Style Properties dialog appears, with our settings, as shown in Illustration 29.

The Style Properties dialog appears, with our settings, as shown in Illustration 30.

We return to the Y-Axis tab, which appears as shown in Illustration 31.

NOTE 1: For clarity, the setting is circled in red in the illustration that follows.

The Legend tab appears as depicted in Illustration 32.

The Chart Properties dialog closes, returning us to the generic chart item in Report Designer, Layout tab.