MSSQL Server Reporting Services: Master Chart Reports: Track Exchange Rates in a Line Chart - Page 3
September 29, 2004
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
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.
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.
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.
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.
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.
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.
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.
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.
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.
11. Right click in the Diagram pane area of the Query Builder.
A context menu appears, as depicted in Illustration 15.