Designing Sample Applications and Creating Reports

The simplest way to learn Crystal Reports and Visual Studio is to follow the hands-on exercises in this tutorial. In no time you’ll be producing stunning charts and be able to create a reporting application from scratch.

(For more resources related to this topic, see here.)

Creating a new application

We will now start using Microsoft Visual Studio, which we installed in the previous article:

  1. From the Start menu, select All Programs, then select the Microsoft Visual Studio 2012 folder, and then select Visual Studio 2012.
  2. Since we are running Visual Studio for the first time, we will see the following screenshot. We can select the default environment setting. Also, we can change this setting in our application. We will use C# as the programming language, so we will select Visual C# Development Settings and click on the Start Visual Studio button.

  3. Now we will see the Start Page of Microsoft Visual Studio 2012. Select New Project or open it by navigating to File | New | Project. This is shown in the following screenshot:

  4. As shown in the following screenshot, we will select Windows as the application template. We will then select Windows Forms Application as the application type. Let us name our application Monitor. Choose where you want to save the application and click on OK.

    Now we will see the following screenshot. Let's start to design our application interface.

  5. Start by right-clicking on the form and navigating to Properties, as shown in the following screenshot:

  6. The Properties explorer will open as shown in the following screenshot. Change Text property from Form1 to monitor, change the Name property to MainForm, and then change Size to 322, 563 because we will add many controls to our form. After I finished the design process, I found that this was the suitable form size; you can change form size and all controls sizes as you wish in order to better suit your application interface.

Adding controls to the form

In this step, we will start designing the interface of our application by adding controls to the Main Form.

We will divide the Main Form into three main parts as follows:

  • Employees
  • Products and orders
  • Employees and products

Employees

In this section, we will see the different ways in which we can search for our employees and display the result in a report.

Beginning your design process

Let's begin to design the Employees section of our application using the following steps:

  1. From the Toolbox explorer, drag-and-drop GroupBox into the Main Form. Change the GroupBox Size property to 286, 181, the Location property to 12,12, and Text property to Employees.
  2. Add Label to the Main Form. Change the Text property to ID and the Location property to 12,25.
  3. Add Textbox to the Main Form and change the Name property to txtEmpId. Change the Location property to 70, 20.
  4. Add GroupBox to the Main Form. Change the Groupbox Size property to 250,103, the Location property to 6, 40, and the Text property to filters.
  5. Add Label to the Main Form. Change the Text property to Title and the Location property to 6,21.
  6. Add ComboBox to Main Form. Change the Name property to cbEmpTitle and the Location property to 56,17.
  7. Add Label to Main Form. Change the Text property to City and the Location property to 6,50.
  8. Add ComboBox to Main Form. Change the Name property to cbEmpCity and the Location property to 56,46.
  9. Add Label to Main Form. Change the Text property to Country and the Location property to 6,76.
  10. Add ComboBox to Main Form. Change the Name property to cbEmpCountry and the Location property to 56,72.
  11. Add Button to Main Form. Change the Name property to btnEmpAll, the Location property to 6,152, and the Text property to All.
  12. Add Button to Main Form. Change the Name property to btnEmpById, the Location property to 99,152, and the Text property to By Id.
  13. Add Button to Main Form. Change the Name property to btnEmpByFilters, the Location property to 196,152 and the Text property to By filters.

The final design will look like the following screenshot:

Products and orders

In this part, we will see the relationship between products and orders in order to demonstrate, which products have higher orders. Because we have a lot of product categories and orders from different countries, we filter our report by products category and countries.

Beginning your design process

After we finished the design of employees section, let's start designing the products and orders section.

  1. From Toolbox explorer, drag-and-drop GroupBox to the Main Form. Change the GroupBox Size property to 284,136, the Location property to 12,204 and the Text property to Products – Orders.
  2. Add GroupBox to the Main Form. Change the GroupBox Size property to 264,77, the Location property to 6,20, and the Text property to filters.
  3. Add Label to the Main Form, change the Text property to Category, and the Location property to 6,25.
  4. Add ComboBox to the Main Form. Change the Name Property to cbProductsCategory, and the Location property to 61,20.
  5. Add Label to the Main Form. Change the Text property to Country and the Location property to 6,51.
  6. Add ComboBox to the Main Form. Change the Name property to cbOrdersCountry and the Location property to 61,47.
  7. Add Button to the Main Form. Change the Name property to btnProductsOrdersByFilters, the Location property to 108,103, and the Text property to By filters.

The final design looks like the following screenshot:

Employees and orders

In this part we will see the relationship between employees and orders in order to evaluate the performance of each employee. In this section we will filter data by a period of time.

Beginning your design process

In this section we will start to design the last section in our application employees and orders.

  1. From Toolbox explorer, drag -and-drop GroupBox to the Main Form. Change the Size property of GroupBox to 284,175, the Location property to 14,346, and the Text property to Employees - Orders.
  2. Add GroupBox to the Main Form. Change the GroupBox Size property to 264,77, the Location property to 6,25, and the Text property to filters.
  3. Add Label to the Main Form. Change the Text property to From and the Location property to 7,29.
  4. Add DateTimePicker to the Main Form and change the Name property to dtpFrom. Change the Location property to 41,25, the Format property to Short, and the Value property to 1/1/1996.
  5. Add Label to the Main Form. Change the Text property to To and the Location property to 7,55.
  6. Add DateTimePicker to the Main Form. Change the Name property to dtpTo, the Location property to 41,51, the Format property to Short, and the Value property to 12/30/1996.
  7. Add Button to the Main Form. Change the Name property to btnBarChart, the Location property to 15,117, and the Text property to Bar chart.
  8. Add Button to the Main Form and change the Name property to btnPieChart. Change the Location property to 99,117 and the Text property to Pie chart.
  9. Add Button to the Main Form. Change the Name Property to btnGaugeChart, the Location property to 186, 117, and the Text property to Gauge chart.
  10. Add Button to the Main Form. Change the Name property to btnAllInOne, the Location property to 63,144, and the Text property to All In One.

The final design looks like the following screenshot:

You can change Location, Size, and Text properties as you wish; but don't change the Name property because we will use it in code in the next articles.

Validating form data

When you start designing your application, you need to think of some facts, such as performance and graphical user interface (GUI). One of the most important rules you need to take into account to build a good application is the ease of use. You need to make sure that the user is comfortable with your application. Reduce the amount of data that the user has to enter into your application. In our application therefore, you will see that we use GroupBox more than Textbox because selecting data is easy and safe when we use GroupBox.

Sketching your reports on paper

Our application will be kept simple and complete and will be designed as a single form with common reporting types. All reports will be displayed in another form containing a Crystal Report Viewer as we will see later. Every button in the Main Form is designed to display a report.

  • The btnEmpAll button will display a report containing all employees' data and it will look like the following screenshot:

  • The btnEmpById button will display a report containing the data for a specific employee and it will look like the following screenshot:

  • The btnEmpByFilters button will display a report containing employee data and filter these employees using data in cbEmpTitle, cbEmpCity, and the cbEmpCountry GroupBoxes as we see in the following screenshot:

  • The btnProductsOrdersByFilters button will display a cross-tab report to see the relationship between products and orders. We will filter the data using data in cbProductsCategory and the cbOrdersCountry GroupBoxes, as shown in the following screenshot:

  • The btnBarChart button will display a bar chart report to see relations between employees and orders. We will filter data using the data in dtpFrom and the dtpTo DateTimePicker, as shown in the following screenshot:

  • The btnPieChart button will display a pie chart report to display the relationship between employees and orders. We will filter data using data in dtpFrom and the dtpTo DateTimePicker as shown in the following screenshot.

  • The btnGaugeChart button will display a gauge chart report showing the relationship between employees and orders. We will filter data using data in the dtpFrom and the dtpTo DateTimePicker as shown in the following screenshot:

  • The btnAllInOne button will display a report similar to the following screenshot:

Preparing a report data

Before we begin to add Crystal Reports to our application, we need to understand the data that we will be displaying and know what data will be displayed in this report. Look at the design of the report in the following screenshot:

Selecting the report data

As we can see in the preceding screenshot, all report data comes from the Employees table. The SELECT query will appear as shown in the following screenshot:

When we compare the report data with the data retrieved through the query, we will find two differences changes:

  • The Full Name column: The Full Name column is displayed in the report as one part but in the database it consists of three parts (TitleOfCourtesy, FirstName, and LastName). We can address this problem here in the SELECT query or in the report itself. We can concatenate the three fields into one field using the SQL query by the plus (+) operator, as shown in the following screenshot:

  • The Reports To column: The Reports To column is displayed in the report as a name, but as we can see in the preceding screenshot it is displayed as number. If we look at the Employee table we will see that this table has a self-join relationship, see the following screenshot:

We can modify our query to display names rather than numbers in two ways:

  • Using subquery: We will update the query to look like the following screenshot:

  • Using self join: We will update the query to look like the following screenshot:

Summary

In this article we discussed the design of our application. We also learned how to create a new application, add controls to the form, validate form data, and sketch our reports on paper. We also learned how to prepare our report.

Resources for Article:


Further resources on this subject:


Books to Consider

comments powered by Disqus