Generating a Simple Crystal Report using VS 2005

Crystal Reports has become a very versatile application for generating reports, particularly if you use Visual Studio. This article will guide you through generating a report using this tool. It takes the wizard approach, and uses copious screen shots to keep you on track.

Crystal Report

It is relatively easy to retrieve data from a database and display it using any of the bound controls. This is especially so in ASP.NET 2.0 where the amount of code you need to write has been drastically reduced. However, to present the data in a formal, board-room friendly format and distribute it in hard copy, or for look-up, or both is challenging. Crystal Reports does this remarkably well. Microsoft has been bundling Crystal reports ever since the VB days, and it ships with every version of VS 2005. It is well integrated with Visual Studio 2005 as we shall see shortly. For highly functional and aesthetically pleasing report generation, Crystal Report is ideally suited. It is very versatile and can use data from a variety of databases as well as non-traditional sources.

Crystal Report comes with its own SDK which can be used to programmatically create reports and interact with them at run time, if one desires do so. Crystal Reports can be us in either Windows or a web application. It can be stand alone or made available over the Internet. These reports can be utilized in many different ways: application embedded reports; reports from web servers; as web services; and so forth. In VS 2005 you find two Crystal Report objects, the CrystalReportViewer and the ReportDocument as shown in the next picture.

The Object Browser reveals the various classes that are integrated with the Visual Studio application as shown in the next picture.

The Objective of this tutorial

The main objective of this tutorial is to give the reader enough guidance in generating a report easily using this tool. This hands-on approach should give enough confidence for the user so that he can experiment himself using this basis. In this tutorial, the report document will retrieve a filtered set of data from the back end SQL 2005 Server using an OLEDB connection. This tutorial uses the wizard approach to report generation as this is the simplest of the methods. There are basically three major steps in report creation, and these are described exhaustively with screen shots that the reader will encounter while creating the report.

{mospagebreak title=Creating a Crystal Report Project}

In this tutorial a Windows application with the Crystal Report will be created. The application’s form will have the report contained in it after the report is created.

Create a new project, accessing it from the File menu. This opens the New Project window listing Business Intelligence Projects at the top as shown. In the Visual Studio Installed templates area, highlight Crystal Reports Application. The default has been changed to Diamond in the Name: field as shown. This application will have a Windows interface for a Crystal Report.

 

Project Folder Contents

The next picture shows the folder created by this application. It has a form, Form1.vb, and a yet to be completed CrystalReport1.rpt. This is just a template of a report; when the wizard completes the tasks, it will be filled with all the necessary information.

{mospagebreak title=Report Generation}

Invoking the Report Wizard

When the application is created, the CrystalReport1.rpt tab will be in the default view with the window Crystal Reports Gallery contained in it as shown. As you can see from this you have three options to choose from. For this tutorial, the first option is chosen.

Accessing the Data

When you click on the OK button, you will get to the Standard Report Creation Wizard window as shown. There are lots of locations and sources that the data can come from, including the possibility to create a new connection.

Creating a New Connection

For this tutorial a new connection will be established to get the data. When you expand the Create New Connection node you will see even more options.

Choosing an OLEDB Provider

We will be using an OLEDB provider, so click on the OLEDB (ADO) folder which shows the OLEDB Providers available in the drop-down in the right pane. We plan to get the data from the SQL 2005 Server using the SQL Native Client. Highlight the SQL Native Client. Place a check mark in the checkbox Use DataLink file. Well, you might not have a DataLink file ready to be used. However, in the next sub-section we will create a DataLink file, and when this is created, we will browse to it by clicking the ellipsis button () in the Ole DB Provider screen.

{mospagebreak title=Creating a DataLink file}

The Microsoft DataLink utility can be used to create a DataLink file with the *.udl extension. If you create a new text document and change its extension from *.txt to *udl, a DataLink file will be created. This can be configured by double clicking the icon of the file. This opens the DataLink Properties window as shown with the Connection tab in the default view. Click on the Provider tab, and then scroll up or down until you locate the SQL Native Client. Highlight the SQL Native Client and click Next, or change to the Connection tab.

The data source is the SQL 2005 Server which in this case is Hodentek/Mysorian where Hodentek is the Windows XP machine and Mysorian is the SQL 2005 Server instance. The logon information is what is appropriate for the SA user. You may save the password with the connection. If the information up to this point is correct, you will be able to access all the databases on this server as shown in the drop-down box. For this report the Northwind database will be used. The reader will benefit from the earlier SQL 2005 articles on the ASPFree.com site. At this point you can also verify that the connection is correct by clicking the test button.

Make sure that after this task you go back to the OleDB Provider screen and browse and locate this file.

Connecting to the Data Server

After locating the file, click on the Finish button. This brings up the next screen where you make the connection by providing the authentication information.

 

Do you want to change something?

When you click on the Next button, the OLEDB(AD0) screen presents some advanced information. You could “Edit” or “Remove the Property” altogether. For this tutorial the default is accepted.

{mospagebreak title=Selecting the Data}

When you click on the Finish button in the above screen, you enter the next step of the report creation wizard. You will be choosing the source of the data on the server you logged on. Here the Northwind database will be used. You may access any of the objects; that includes some of the system objects as well.

Selecting a table(s) from the database

We will be using one of the tables on this database, in particular, the Employees table. Expand the Table node, highlight the Employees table, and click on the button marked “>”. This will take the Employees table to the Selected Tables: area as shown. Instead of a single table, multiple tables can also be used.

Selecting Columns (Fields) from the table

A table can have a large number of columns, also called Fields. In a report, depending on the requirement, one may or may not need all the columns. In this case, out of all the available fields only a subset is chosen to be displayed. Transfer all these fields to be displayed from left pane to right pane by clicking one-by-one, or highlighting them as a bunch and using the “>” button. By using the Up/Down arrows in the Fields to Display pane, they can be reordered top to bottom.

Grouping the data

When you click on the Next button, you will be taken to the Grouping area. The Employees may come from different cities and in this case they can be grouped on the basis of the city they come from. In this screen you would choose the Employees. City and click on the “>” button to transfer it to the Group By: column. In the background, the wizard is creating an appropriate SQL Select statement.

Summarizing the data

When you click on the Next button, you will bring up the window shown where you may summarize the data. The data fields we have chosen do not require summarizing, therefore you go the next task by clicking the Next button on the following screen.

Selecting only required fields

Although this section is optional you may still want to make a change to the record selection. For example FirstName was not chosen in the initial selection, but here it was added.

{mospagebreak title=Choosing a Style for the Report}

When you click on the Finish button in the above screen you will have completed all the tasks related to data to be displayed. In this step you will choose the style from an available list of styles. Here the last in the list, Maroon/Teal box, is chosen.

Design View of generated report

When you click Finish you will have created the report. At this point you will see two new windows, which are described here. These two windows show the various report fields in a node tree, and the design pane of the report.

Accessing various report fields

The Fields Explorer window shows all the different fields. This is the same as the ones you would find in the earlier versions of Crystal Report, such as Crystal Report 8.5. In a more elaborate and complex report you may need a combination of all of these fields.

Design pane of the report

The design pane of the report is also similar to the ones you would find in the earlier Crystal Report versions, such as 8.5. This is the well know banded report with header, footer, and data-related sections. You could drag and drop fields from the Field Explorer onto these sections if you were to design a report from scratch.

{mospagebreak title=Displaying the Report}

Providing authentication

In order to display the report, when you build and run the project, you will have to provide the authentication information which will be presented to you. The Main Report will be embedded in Form1, and will be revealed when you are properly authenticated.

Displayed Report

After you click the Finish button, the report displays as shown in the next picture. On the left pane you see the Grouping, in this case the various cities the employees come from, and on the right you see the related personal information of the employee.

From the above window’s main menu at the top, you can export the report; print the report; refresh the report; toggle the Group tree; search for text; zoom up and down; and navigate the report.

Class View of the Report

Since the report finds itself on a form, which is a class, you may also find the class view by right clicking the Form1.vb in the Solution Explorer and choosing the View Class Diagram menu item.

Summary

This tutorial describes the very basic steps needed to create a report using the wizard. The Crystal Report creation process is extremely easy and fast. The interface is very intuitive. If you have worked on any other reporting software such as the earlier versions of Crystal Report, creating Crystal Reports in VS 2005 is a piece of cake.

4 thoughts on “Generating a Simple Crystal Report using VS 2005

  1. Clean, pleasing reports can be generated quite easily and fast. But if you use the latest version of the software you can do much more. Connecting to non-relational data is quite as easy as connecting to relational data. Check out the other crystal reports artciles on this site.

    I look forward to your comments and questions. Go easy, Jay

  2. There could be more than one reason for this. You may have certain problems with your program or installation. You also give insufficient information and I cannot judge what your are doing is right or wrong. I have received emails from satisfied readers which makes me believe the outlined procedure works. It did indeed on my computer. I have (I am sure this is true of most writers on this site) never posted a code or procedure without first testing myself.:yes:

[gp-comments width="770" linklove="off" ]