Have you ever wanted to learn how to create charts from scratch using the SQL 2000 Reporting Services in the Visual Studio .NET 2003 IDE? This article gives you the appropriate introduction.
A picture is worth a thousand words is not really an exaggeration. Whereas a mass of data can be overwhelming, the information conveyed by a picture can create a strong impression of the same data. Lotus 1-2-3 provided strong support for charting the data with a user friendly interface. This was quickly overtaken by the MS Excel engine, which became enormously popular not only because of its number crunching power, but also because of its excellent support for creating charts.
Crystal Reports was (and is) a constant companion for the various versions of Visual Basic. In fact, it is also strongly supported with designer tools in Visual Studio .NET 2003 IDE. However, Microsoft introduced the SQL 2000 Server Reporting Services that assists in creating reports from not only data in SQL 2000 Servers, but from any ODBC, OLEDB, SqlXmlOleDb sources. This opens up the possibility of making Web-based/hardcopy reporting with strong security features inherent in the .NET Framework a reality.
We need to watch how Crystal Report reacts to this new challenge. SQL 2000 Server Reporting Services also provides excellent support for creating charts, by means of a Chart Control in its Reporting Items in the toolbox. The chart control is a third party tool; the Dundas Chart control is a small sized, highly scalable, very efficient charting software that happily lives in the .NET Framework.
This "How to" article mostly covers creating charts starting from scratch using the SQL 2000 Reporting Services in the Visual Studio .NET 2003 IDE. There are excellent sources of information on the RS both at Microsoft and elsewhere. More packages and addins are expected from Microsoft supporting the RS. Topics covered in this article include the following:
Choosing Data for the Chart
Creating a Data Source
Creating a Report Project in Visual Studio .NET IDE
Almost any kind of data can be used for creating charts of various kinds, and there is hardly any human activity where charts are not used. For the purposes of this article, a simple data source is chosen so that the screen shots can be meaningfully appreciated. The data source is from an Excel spread sheet consisting of the plotting of time against some relative values. Because of this choice, this article is also about how to connect to Excel and use Excel data with Visual Studio .NET 2003 IDE. This data is shown in the next picture, from an Excel file.
Creating a Data Source
In order to use this data to create a report/graph/chart in the IDE, it is necessary to create an ODBC data source so that the IDE can connect to the data stored in the Excel file. This can be accomplished by calling up the ODBC Data Source Administrator->ODBC Sources in the control panel shown in the next picture.
While parked in the System DSN tab, clicking Add... will bring up the next dialogue, wherein you choose an appropriate driver -- in this case, the Microsoft Excel Driver.
When you click FINISH for the above screen, you are prompted to provide information as to where the data is coming from and add some description.
Now you click on Select Workbook, which brings up the next screen, where you navigate to the location where your file is stored. In this case, the workbook chosen is called 'TestBook.xls' on the C:\ drive.
The user given System DSN name is NetChart and there is some description for documentation. This uses the version of Excel shown in this picture. The workbook location on the drive is also shown in this picture. The access to the Excel file is now available by using the DSN created in this step.
Several different kinds of projects can be created in VS.NET 2003 IDE, and one of them is the Report project. It is the only project type in the Business Intelligence (more types would be added!) with two items, a Report and a Report Wizard. When the project is created, there will be two folders created, a Shared Data Sources folder and a reports folder. The basics of creating a report are exhaustively covered in another article; in this article, only some essentials are described.
Adding a Data Connection
For report generation, a data source is neccesary, and from the VS.NET IDE a connection has to be established to this source. This is accomplished by going to the menu item, View -->Server Explorer, and on the Data Connections node right click to add a connection from the pop-up menu. This wakes up the Data Link applet shown in the next picture. In the Providers tab, choose the Microsoft OLEDB providers for ODBC. In the connections tab, choose the ODBC DSN created earlier in this article using the ODBC Data Source Manager. NetChart should be selected from a list of various kinds of source names. There is no need for a username and password for this particular instance; in some cases it may be needed. The catalog is the same as the XLS file that you chose to create the report. The connection may be tested to confirm.
Once the connection is established, it is time to pull the data out. At this point a report may be added to the report project from the Solution explorer. Right click on the Project folder in the solution explorer and click on Add New Item..; that opens up the Report Projects categories, from which either a Report or a Report Wizard may be chosen.
In this case the ReportWizard was chosen, and a report named NetCharts.rdl file was added to the reports folder [for this tutorial an existing report project was used, and therefore a previous report is also seen in this picture]. At the same time a Report Wizard wakes up, offering guidance. The very first thing the Wizard configures is the data source, as shown in the next picture. The data source is accessed by scrolling the combo box, and the source configured earlier is chosen.
Each report in the reports folder is supported by three panes on which the report is configured: the Data, the Layout and the Preview. In the Data a New DataSet can be configured, or an existing Dataset can be edited. This dataset is not the same as the disconnected dataset of the System.Data.DataSet class. Clicking <New Dataset..> will bring up the next dialogue.
This is where the Dataset is to be configured.
Clicking on the dropdown will display options from which the information provided for the connection is chosen as shown. The only type for the CommandType is "Text." It is now necessry to code a sql select statement. For the present example, a select * from [Sheet1$] statement is used for the query. Note the absence of a query designer tool! Although several other items may be tuned on this dialogue, defaults are assumed. Later there will be an opportunity to peek at these options.
At this point the query may be run as shown, clicking on the !, and the result is displayed in the region below the query statement as shown.
The result of the above can be slightly modifed by altering the dataset[ and in fact creating a new dataset with a slightly altered query statement to produce an integers only column as follows. The point to note is that the query may be changed to suit your needs using applicable expressions, like the one shown in the next figure.
Report related items are added to the toolbox when RS is installed on a system on which VS.NET 2003 is installed. These items are shown in the next picture. When a new report is created, the layout just consists of a body onto which items from the Report menu can be draggged and dropped.
In this case, as we will be looking at a tabular structure, a table item is added to the Body as shown. The table comes with a 3 x 3 structure by default. You may add more columns as shown (right click the last column and pick up the item you like). There will be header, data, and footer rows. The header and footer are for static information, but may be modifed to hold some dynamic content. The data is populated from the data pulled from the source.
To assist in populating the data in the table, a fieldlist item is also produced when the dataset is created. From the field list box, items can be dragged and dropped onto the table [reminiscent of the DataEnvironment in VS6.0]. For example, the item t can be dropped to the first column, second row to reference the data bound item Fields!t.Value, the letter t will be automatically added to the first row, first column and so on.
To add the third item, a extra column has to be appended to the default. At this stage it is possible to preview the report by clicking the preview tab. Depending on the amount of data, it can take some time. There is also a "cache" feature which may be utilized to bring data from the "cache." Please refer to the references cited earlier for more details.
Adding a Chart for Data Display
The previously mentioned chart control can be dragged and dropped below the tabulation in the body as shown here. It is resizable in both directions by dragging the handles. By right clicking the chart, its properties can be customized. There are a lot of features that can be customized, and they are of a nature similar to what one can do in Excel. Here, once again the basics are stressed.
In the General tab, the title of the chart is defined. A name is given to the chart and the type of chart may be chosen. Chart and plot area styles may be chosen, but that is not attempted here.
In the Data tab, the important configurations are made for the data that gets into the chart. The dataset is to be set to the dataset defined earleir [Dataset2] from the dropdown box. Now click the Add... button next to the first textarea box with the label Values:. This brings up the next Edit Chart Value applet.
There are two items to be filled, one is a label and other is a value. The value is chosen from the dropdown box. For some strange reason the value shows sum(Fields!Second.Value). But what is needed to be plotted is Fields!Second.value. This correction can be applied here. In a similar manner, the three column values are applied to the chart by invoking this applet three times.
This shows the three items added, First, Second, Expr1003, because the third item is calculated from the third column.
In the next Textarea box, the default shown is taken when the Add.. button is clicked. The value of this corresponds to Fields!t.Value.
Moving on to the X-axis using the next tab, the x-axis title, its position in the chart, the minimum and maximum values of x-axis can be set as shown. Similarly the Y-axis related items can be set in the next Y-axis tab.
In the Legends tab, properties can be chosen to show the chart legends that improve the presentation value of the chart. You may see the type of control you can exert on the Legend in this picture. Alternatively it is also possible to drag and drop items from the fieldlist onto the chart area, with the fine-tuning carried out from the properties dialogue. When all of this is finished, the chart has the following apperance in the design pane.
The table has the following appearance when displayed in the preview mode.
This is how the chart appears in the preview mode.
Well, let's go ahead and look at the chart created by MS Excel for the same data. How do you compare Dundas vs. Excel?
The chart information can be exported to various formats as shown by right clicking the finished chart in the preview mode. The next picture shows part of an exported xml file opened in an IE6.0 browser. Exported to the Excel format, the table and the graph gets housed in a workbook's worksheet, as in the preview mode.
Summary and Conclusions
The steps shown in this article can be rapidly implemented, and charts and reports can be prepared in record time, a truly RAD feat. Reports once developed can be deployed using the properly configured ReportServer, which works seamlessly with the SQL 2000 server and the IIS. This technology is still evolving and may morph lot more than its present avatar. The Table and the Chart controls can be customized to your heart's content, although only a small portion was shown. The Dundas chart control must be a pretty good one for Microsoft to bundle with their product. I believe that the speed, the small footprint, the the extensibility, and the ease with which it was integrated with .NET Framework were all the important selling points.
MS SQL Server 
ASP.NET Export to Excel and Word using VB.NET and C#
