Crystal Reports is one of the important business intelligence tools that allows you to view the same historical data from different angles. This software is created by Business Objects. It is very simple to learn and use, and features a very good user interface. This article will introduce you to the basics of building a Crystal Report.
Contributed by Madhuri Chanda Rating: / 5 September 29, 2008
With Crystal Reports you can create simple reports which display data according to your requirements. These include parametrized reports, drill down reports, specified range reports, and more. We can add totals, sub totals and averages to make reports more meaningful and useful. We can create reports in a variety of formats, including print, various electronic formats such as PDF and RTF, or even in HTML format for online reports.
With Crystal Reports, data can be easily sorted (ascending, descending), filtered, and/or grouped with different criteria. We can represent the same data using graphs and charts with Crystal Reports.
Crystal Reports are is widely used with .net applications because the software is provided along with Visual Studio for .net development.
Basic requirements for Crystal Reports
First of all, since we're developers, we need to know what the end user requires. The end user has to tell you how the report should look and what it should contain. Once you gather all of the requirements, you must make a mock up and get it approved by the end user.
Now you need to figure out which type of report will provide the best fit for your requirements. You may want to make additional suggestions based on your knowledge.
Once you decide which type of report you will use, you need to finalize your data source. The data source could be anything: a simple database, a relational database, an Excel sheet, a spreadsheet, text files, XML files, and so on.
Once you find the data source, you must decide which tables you need to use. Sometimes you can use views which are already present. Otherwise, you can create your own views for this particular reporting purpose.
The next step is to design the Crystal report. It should match up with your mock-up. Bind your report with the data source to see the results. Finally, show this to your end user for approval.
We will see details covering all of the different types of reports and how to create them in my upcoming series on Crystal Reports. In this article we will learn how to create a simple report with a database as the data source.
We will be using the "Northwind" database in SQL Server. This database comes with the purchase of SQL Server software. We will use the Employees table, and the following fields: EmployeeID, LastName, FirstName, Title, TitleOfCourtesy, BirthDate, HireDate, Address, City, Region, PostalCode, Country, HomePhone, Extension, Photo, Notes, ReportsTo, PhotoPath
Now let's create a Windows form application using Visual Studio to simply display all employees with the title of "Sales Manager" using Crystal Reports.
Create a connection with the "Northwind" database in your application. There are different ways to make the connection. Here I created a new ODBC connection with the name "NorthWind" and added it to the application through the server explorer window.
The next step is to add Crystal Reports to your Windows application. Do the following:
Select the Project Menu option
Select "Add New Item"
Select Crystal Reports from the template.
Click on the Add button.
Select the "Using the Report Wizard" option.
Choose Standard.
Press OK.
This will open a report wizard to do data source connections and manipulate the data by selecting sorting orders, filters, groups and so on.
If you choose to create a new Crystal report, clicking on "As a Blank Report" will open a blank report for you where you can add your desired fields and manipulations.
The third option, "From an Existing Report," will create a report from a report which already exists. We will learn more about these options later in other articles I've planned and written that cover Crystal Reports.
Now let's see how to create a data source connection. In the standard report creation wizard, take the following steps:
Select "create new connection."
Select "ODBC."
Select "Northwind" as the data source name.
Follow the steps in the wizard to create a connection.
Once you make the connection, the database is available to you. You can select the tables and view the one from which you want create a report.
We are going to select the "Employees" table from the "NorthWind" database.
Click on Next.
Select all the columns (or the desired columns) from the "Employees" table.
Click on Next.
The next window in the wizard is for grouping data with the desired columns, which you selected in the previous step. This step is optional, so let's skip it for now.
The next window is for "Record Selection." This is also an optional step. In this step we can select any column on which our criteria depends.
In this report we want to show information about employees whose title is "Sales Manager," so we need to select the "Title" column and choose "is equal to." This will open one more drop down menu with all of the distinct titles for employees. Select "Sales Manager."
Click next.
Select the standard report style.
Click finish.
This should create a report for you in design mode. Here you can make some changes, such as changing or adding a header, of changing the font size. Here you can also add a date, page numbers, footers, and so on to your report.
Once your report is ready, you need to add that report to your Windows application form by following these steps:
Go to the project menu.
Click on "Add Windows Form."
Populate the tool box.
Select the CrystalReportViewer tool.
Place it on the form.
Set the "Report source" property using the property window to the report which you have just created.
Now you can see the report in this form when you run your application.