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

Wednesday Sep 29th 2004 by William Pearson
Share:

MSAS Architect Bill Pearson continues a set of articles surrounding the rich Reporting Services chart features. In this article, we meet an illustrative business need to track exchange rates with a line chart.

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.

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

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.

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).

RS009_LineChart

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 ...

12.  Select Add Table from the context menu.

The Add Table dialog appears.

13.  Using the SHIFT key for multiple, non-contiguous selections, after the standard Windows manner, highlight the following tables:

  • Currency
  • CurrencyRate

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

Click for larger image

Illustration 16: Our Selections in the Add Table Dialog

14.  Click Add to add the tables to the Diagram pane.

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.

15.  Click Close to dismiss the Add Tables dialog.

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.

16.  Click the join between the Currency.CurrencyCode and CurrencyRate.FromCurrencyCode fields, to select it.

17.  Press the [Delete] button to remove the join.

18.  Select the columns listed in Table 1, by clicking the checkboxes in the associated tables on the Diagram pane, in the order presented.

Table

Column

CurrencyRate

CurrencyRateDate

CurrencyRate

FromCurrencyCode

CurrencyRate

ToCurrencyCode

CurrencyRate

EndOfDayRate

Currency

Name


Table 1: Column Selections by Table

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


Illustration 17: Our Column Selections (Compacted Table View)

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.

19.  Click the Alias box to the right of FromCurrencyCode (from the CurrencyRate table) in the Grid Pane, to place the cursor there, as depicted in Illustration 18.


Illustration 18: Select the Alias Box to Rename the Column ...

20.  Into the Alias box, type the following:

Home Currency

21.  Type the following into the Alias box for CurrencyRate. ToCurrencyCode column:

Foreign Currency

22.  Click the Criteria box on the ToCurrencyCode row in the Grid Pane, just as we did earlier for the Alias box, to place the cursor there.

23.  Into the Criteria box, type the following:

GBP

24.  Type the following into the Or... box, to the immediate right of, and on the same row as, the Criteria box within which we placed the GBP filter in the immediately preceding step, again on the ToCurrencyCode row:

EUR

NOTE: Ignore the notation changes that the grid makes for the present.

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


Illustration 19: Alias and Criteria Information, Grid Pane (Partial View)

25.  Execute the query to test its operation, by clicking the Run ("!") button.

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


Illustration 20: Results Dataset in the Results Pane (Partial View)

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.

26.  In the SQL pane, append the following expression to the existing SQL:

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.

27.  Execute the query to see the modified results, by clicking the Run ("!") button once again.

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.

TIP:

When designing a report in Reporting Services, or any other enterprise reporting application, always focus on the complete dataset first. Making sure you have all the data that the report will require - before beginning the physical construction of the report, and especially before investing time in formatting and the like - can save the hours of rework that is due upon those who have the "I'll come back to that later" mentality.

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.

1.  Click the Layout tab to switch to the Layout view.

2.  Drag the report edges so they comfortably fill the screen area.

3.  Select View -> Toolbox from the main menu to place the Toolbox within easy reach (if it already appears, simply disregard this step).

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.)


Illustration 21: The Toolbox ...

4.  Click the Chart button (at the bottom of the Toolbox pane).

5.  Place the mouse cursor over the upper left corner of the report layout.

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.

6.  Starting in the upper left corner of the layout body, click, and then, holding the mouse button down, drag to create a box that covers the report layout.

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


Illustration 22: Grab, Anchor, and Size the Chart Item ...

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

7.  Release the mouse to drop the chart item.

The chart item appears, in what I refer to as its generic manifestation (the placeholder that we see prior to specifying a chart type), as shown in Illustration 23.


Illustration 23: The Generic Chart Item Appears

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.

NOTE: Should you accidentally "drop" the chart item in a manner that you wish to realign, you can simply move the item by clicking inside the chart, then pointing to the now shaded border, to drag it to a new location.

Clicking again on the border will also allow you to expand / contract the chart shape. (The nuances are easy to learn with a little practice.) Double-clicking the chart item will make the "drop regions," seen above, appear.

The chart item is now in place, and we are ready to specify its "line" nature, as well as to populate it with the dataset that we have created.

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.

1.  Drag the EndOfDayRate field from the Fields window (the drop-down selector atop the Fields window should display the CurrencyData dataset we created above), dropping it on Drop Data Fields Here section of the generic chart item on the Layout tab.

2.  Drag the CurrencyRateDate field, and drop it on the area of the chart item marked Drop Category Fields Here.

3.  Drag the Foreign_Currency field, and drop it on the area of the chart item marked Drop Series Fields Here.

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


Illustration 24: Field Items within Intended Drop Points

4.  Right-click the chart.

5.  Select Properties from the context menu that appears.

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

6.  Type the following into the Title box on the General tab:

Currency Rate Activity

7.  Click the Style button, which appears to the right of the Title box.

The Style Properties dialog box appears.

8.  Make the settings, listed in Table 2 below, within the Style Properties dialog box:

Property

Setting

Family

Verdana

Size

14pt

Style

Normal

Weight

Bold

Color

Dark Green

Decoration

None


Table 2: Style Properties Dialog

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


Illustration 25: Style Properties Dialog Box with Settings

9.  Click OK to accept changes and to exit the Style Properties dialog box.

We return to the General tab.

10.  Select Line under Chart Type in the lower left corner of the General tab.

11.  Ensure that the leftmost of the two Chart sub-types is selected.

12.  Click the Data tab to select it.

13.  Highlighting [Value], click Edit, to the right of the Values box.

The Edit Chart Value dialog box appears.

14.  Click the Appearance tab to select it.

15.  Click, to place a checkmark, in the checkbox to the left of Show markers.

16.  Leave Marker size at the default of 6.

17.  Click the radio button to the left of Circle in the Marker type section.

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


Illustration 26: Edit Chart Value Dialog Box with Settings

18.  Click OK to accept changes and to exit the Edit Chart Value dialog box.

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

19.  Click the X-Axis tab to select it.

20.  Make the settings, listed in Table 3 below, on the X-Axis tab (leaving other property settings on the tab at default):

Property

Setting

Title

Month End

Show Labels (Checkbox)

Checked

Format Code

MM-yyyy

Major Gridlines (Checkbox)

Checked

Minor Gridlines (Checkbox)

Checked

Title Align (Radio Button)

Center

Numeric or Time Scale Values (Checkbox)

Checked


Table 3: X-Axis Tab Settings

21.  Click the Style button, which appears to the right of the Title box.

The Style Properties dialog box appears.

22.  Make the settings, listed in Table 4 below, within the Style Properties dialog box:

Property

Setting

Family

Verdana

Size

12pt

Style

Normal

Weight

Bold

Color

Dark Green

Decoration

None


Table 4: Style Properties Dialog

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


Illustration 27: Style Properties Dialog Box with Settings

23.  Click OK to accept changes and to exit the Style Properties dialog box.

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


Illustration 28: X-Axis Tab with Settings

24.  Click the Y-Axis tab to select it.

25.  Make the settings, listed in Table 5 below, on the Y-Axis tab (leaving unspecified property settings on the tab at default):

Property

Setting

Title

Foreign to USD Rate

Show Labels (Checkbox)

Checked

Major Gridlines:

(Checkbox)

Checked

Intervals

.05

Title Align (Radio Button)

Center

Scale:

Checked

Minimum

0

Maximum

1.35

Side Margins (Checkbox)

Checked


Table 5: Y-Axis Tab Settings

26.  Click the Style button, which appears to the right of the Title box.

The Style Properties dialog box appears.

27.  Make the settings, listed in Table 6 below, within the Style Properties dialog box:

Property

Setting

Family

Verdana

Size

12pt

Style

Normal

Weight

Bold

Color

Dark Green

Decoration

None


Table 6: Style Properties Dialog

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


Illustration 29: Style Properties Dialog Box with Settings

28.  Click OK to accept changes and to exit the Style Properties dialog box.

29.  Click the Style button, which appears to the right of the blank Format code box.

The Style Properties dialog box appears.

30.  Make the settings, listed in Table 7 below, within the Style Properties dialog box:

Property

Setting

Family

Arial Narrow

Size

8pt

Style

Normal

Weight

Normal

Color

Black

Decoration

None


Table 7: Style Properties Dialog

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


Illustration 30: Style Properties Dialog Box with Settings

31.  Click OK to accept changes and to exit the Style Properties dialog box.

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


Illustration 31: Y-Axis Tab with Settings

32.  Click the Legend tab to select it.

33.  Make the settings, listed in Table 8 below, on the Legend tab (leaving any unlisted property settings on the tab at default):

Property

Setting

Show Legend (Checkbox)

Checked

Layout (Radio Button)

Select Table

Position

Bottom square, Right Side (Note 1)


Table 8: Legend Tab Settings

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

The Legend tab appears as depicted in Illustration 32.


Illustration 32: Legend Tab with Settings

34.  Click OK to accept settings.

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

Verify Operation of the Chart Report

Let's ascertain the accuracy and completeness our work. We will execute the report with the following steps:

1.  Click the Preview tab, to the right of the Layout tab atop the design surface.

The new chart report generates, and appears as depicted in Illustration 33.

Click for larger image

Illustration 33: The Chart Report, Preview Tab

While there are myriad opportunities for refinement and general enhancement, we can confidently return to the information consumers with our design as a pilot. Anyone moving for any length of time within business intelligence and analytics circles comes to realize that beginning with a good general idea often leads to feedback requests. Through this mechanism, consumers usually provide more focused and useful direction: we provide a means by which they can actually see what we can do for them. Their feedback allows us to modify our designs, and provides an opportunity to approach the real needs of the consumers more closely before final delivery of the assigned report. The pilot and feedback approach is an excellent way to develop, because it more efficiently produces a report that often exceeds initial consumer expectations.

2.  Click the Layout tab, once more.

3.  Select File -> Save RS009_PieChart.rdl As ... from the main menu.

4.  Resave the file, and then exit Visual Studio.net, when desired.

Through the foregoing steps, we have met the requirements of the information consumers within the AdventureWorks2000 Purchasing department. We have provided a line chart report, which will lend itself readily to use in other Microsoft Office applications, as well as within other reports. We have also met the expressed need to analyze two specified currencies' exchange rate activity over the time frames contained in the corporate OLTP database, showing comparative rate activity between the currencies at key month-end dates.

Conclusion...

In this article, we continued our exploration of chart reports in Reporting Services with an examination of a line chart. We illustrated the use of the chart item within an illustrative exercise that involved helping a group of hypothetical information consumers meet a stated business need.

We created a blank report in Report Designer, added an underlying dataset, and then located a chart item onto the new report. We specified the chart type, and then populated the chart item with data, making use of various property settings, to add informational value to the report we created. Finally, after constructing the dataset and building and populating the chart report, we previewed the report and verified its operation as a whole.

» See All Articles by Columnist William E. Pearson, III

Discuss this article in the MSSQL Server 2000 Reporting Services Forum.

Share:
Home
Mobile Site | Full Site
Copyright 2017 © QuinStreet Inc. All Rights Reserved