Using The Current Worksheet Create A Report

An example of creating a report in an application. The ReportViewer control. The Microsoft Access database

This example provides a step-by-step process for creating a report using Microsoft Visual Studio. Using a similar example, you can create reports for other DBMS, for example, Microsoft SQL Server.

A specific example for generating a report in the Microsoft SQL Server database is given in the topic:

• An example of creating a report in an application created using the Windows Forms Application template for a database of the Microsoft SQL Server type, which is located in the local * .mdf file.

---

Contents

• Task
• Considerations
• Instructions
  • 1. Run Microsoft Visual Studio. Create a project using the Windows Forms Application template
  • 2. Create a database or download a ready-made database file
  • 3. Connecting database file to the project
  • 4. Development of the main form of application
  • 5. Connecting the report file to the project
  • 6. Designing a report
    • 6.1. The ToolBox toolbar
    • 6.2. The controls used to generate the report
    • 6.3. Setting the "Table" control
      • 6.3.1. Configuring the Data Source for the 'Table' control
      • 6.3.2. Setting the table columns to display in the report
        • 6.3.2.1. Setting titles
        • 6.3.2.2. Configuring Data
      • 6.4. Setting the report header
  • 7. Designing an additional form and linking the report to a form
    • 7.1. Adding a form to the project
    • 7.2. The Report Viewer control
  • 8. Program code of the Form2.cs form file
  • 9. Programming the call of the Show Report button to call a report from the main form
  • 10. Program code of the file Form1.cs
  • 11. Running the application for execution (testing)
• Related topics

---

Search other websites:

Task

A database named "Education.mdb" is given, which was created in the Microsoft Access database management system. The archive of database file can be downloaded here .

The database consists of 2 tables:

• 'Student' table;
• 'Session' table.

The structure of the Student table.

Field name	Data type	Comment
ID_Book	Integer	Unique field identifier, key field, counter
Num_Book	Text (10 characters)	Number of student record book
Name	Text (30 characters)	Surname and name of the student
Year	Integer	Year of birth

The structure of the Session table

Field name	Data type	Comment
ID_Session	Integer	Unique field identifier, key field, counter
ID_Book	Integer	Number of student record book
Mathematics	Integer	Score from Math
Informatics	Integer	Score from Informatics
Philosophy	Integer	Score from Philosophy

The tables are related to each other by the ID_Book field (the record book number). The scheme of relationships between tables is shown in Figure 1.

Figure 1. The scheme of relationships between the tables Student and Session

Develop an application that demonstrates the use of reports. Generate a report that contains the following information:

• number of student record book;
• surname and name of the student;
• score from Math;
• score from Informatics;
• score from Philosophy;
• average score (evaluated field).

Data from the report will be obtained from a query generated by the Microsoft Access database. The query is called Query1. The report should be displayed in a separate form.

⇑

---

Considerations

To create a report in Microsoft Visual Studio, you need to create a query in the Microsoft Access database. Queries in Microsoft Access correspond to Views in Microsoft Visual Studio.

Therefore, you first create a query in Microsoft Access that creates the resulting table in accordance with the condition of the task. This query is then used to display the report.

If you need to create several different reports in the application, then in Microsoft Access you need to create exactly the same number of requests for each report.

⇑

---

Instructions

1. Run Microsoft Visual Studio. Create a project using the Windows Forms Application template

After starting Microsoft Visual Studio, you need to create a new project. A detailed example of creating and saving a new project using the Windows Forms Application template is described in the topic:

• An example of creating an application of 'Windows Forms Application' type

⇑

2. Create a database or download a ready-made database file

The database was previously created by means of DBMS Microsoft Access. The database contains two related Student and Session tables, and a query Query1 from which the report will be generated.

The finished database file can be downloaded here .

⇑

3. Connecting database file to the project

Before connecting the database file to the project, it is recommended that you save this file to the folder in which the project files are saved.

To use the database "Education.mdb" you need to connect it to the project in the standard way. To connect the database file to the project, use the "Add Connection …" command from the context menu, which is called in the Data Connections list (Figure 2).

Another way is to select the command "Connect to Database" (Figure 2).

Figure 2. Variants of connection of a file of a database to the project

As a result, the wizard window opens, in which you need to perform the following actions:

• in the "Choose Data Source" window, shown in Figure 3, select the Data Source – 'Microsoft Access Database File';
• in the next window "Add Connection" in the "Database file name" field, specify the path to the database file as shown in Figure 4 (the 'Browse…' button).

Figure 3. The "Choose Data Source" window. "Microsoft Access Database File" is selected

Figure 4. The 'Add Connection' Window

In more detail, the connecting of the Microsoft Access database file to the project is described in the topic:

• Connecting to MS Access database in Microsoft Visual Studio

Once connected, the Server Explorer window will appear as shown in Figure 5.

Figure 5. Displaying the contents of the Education.mdb database in the Server Explorer window

⇑

4. Development of the main form of application

In accordance with the condition of the task, the report should be displayed in a separate form. Therefore, the main form of the application has the form as shown in Figure 6.

Figure 6. Main form of application

As you can see from the figure, the form contains only one Button control named button1. After clicking on the "Show Report" button, a report in another form will be displayed.

The following properties of controls are set:

• in the button1 control, the property Text = "Show Button";
• in the Form1 control (main form), the property Text = "Report Viewer".

⇑

5. Connecting the report file to the project

In the Microsoft Visual Studio system, each report corresponds to a file that has the extension "* .rdlc". This file contains the data that is generated in the report.

To create a file in Microsoft Visual Studio, you need to call the Add New Item command from the Project menu (see Figure 7).

Figure 7. The "Add New Item…" command from the Project menu

As a result, the "Add New Item …" window opens, in which you select the Report template (Figure 8) and specify a name Report1.rdlc for the report file (by default).

Figure 8. Selecting the Report template and setting the name Report1.rdlc for the report file

After confirming the selection (the Add button), the Microsoft Visual Studio window will appear as shown in Figure 9.

Figure 9. The tab window that corresponds to the Report1.rdlc file

⇑

6. Designing a report

Before designing a report, you need to activate the "Report1.rdlc [Design]" file window.

6.1. The ToolBox toolbar

To design report elements, use the ToolBox panel (Figure 10).

Figure 10. The ToolBox panel with tools that are necessary for designing a report

⇑

6.2. The controls used to generate the report

The Microsoft Visual Studio system offers a number of controls that you can use to develop a report. With these controls, you can design the appearance (view) of a variety of reports.

---

---

The following controls are proposed:

• the control of type TextBox. Displays labels, fields, or values that result from calculations;
• the control of type Line. Draws a line that has a thickness of 1 point or more;
• the control of type Table. This control displays data in a grid that has a fixed number of columns and a variable number of rows;
• the control of type Matrix. Displays aggregated data in a grid that has a variable number of columns and a variable number of rows;
• the control of type Rectangle. Draws a rectangle as a container for other report elements;
• the control of type List. Displays a set of report items that are repeated for each group or row of data;
• the control of type Image. Displays an image in the form of a bitmap (for example, a logo, a photo);
• the control of type Subreport. Displays the associated subreport within the scope of this report;
• the control of type Chart. Displays data in the form of diagrams of different types;
• the control of type Gauge. Displays a value, field, or expression as a linear or radial relationship.

Without going into the features of the use of each control, we use the Table control to solve our problem. This control displays data in the grid, which have a fixed number of columns and a variable number of rows.

⇑

6.3. Setting the "Table" control

6.3.1. Configuring the Data Source for the 'Table' Control

To place a Table type control on a report layout, you need to select it (using mouse) in the Toolbox and click in the report area. This will load the Data Source Configuration Wizard. In the first window of the Data Source Configuration Wizard, select the Database data source (Figure 11).

Figure 11. Selecting the data source from which the application will receive data for the report

After selecting "Next>" the following window will open (Figure 12). In this window you need to select the Dataset database model. The selected database model determines the types of data objects that the application uses. This model will be added to our project.

Figure 12. Selecting the Data Model

After selecting 'Next>' the following window will open (Figure 13). In this window, you must select the connection to the database to which the Education.mdb file corresponds. This connection corresponds to the string Connection String, which is automatically generated by the system.

Figure 13. Configuring the connection to the database. The 'Connection String' string

After selecting "Next>", you must confirm copying the database file to the current project (Figure 14). As a result, the connection string is modified. Since the Education.mdb database is already attached to the project (see step 3), it is recommended to select "No".

Figure 14. Window for clarifying of copying the database file into the current project

The next window (Figure 15) proposes store the Connection String in the application's configuration file. It is necessary to leave everything unchanged and select "Next>".

Figure 15. Window with a suggestion to save the Connection String in the application configuration file

Next, there is a window for selecting database objects that will be included in the data set (Figure 16). In our case, we need to select all elements of the query Query1, which was generated by the tools of the Microsoft Access database.

Figure 16. Selecting objects to display

After selecting the "Finish" button, the previously opened Dataset Properties window with the created data set will be displayed (Figure 17). This window displays the Query1 query fields that will be displayed in the report. It should be recalled that the Query1 query was designed by means of DBMS Microsoft Access.

Figure 17. Dataset Properties window with Query1 fields displayed

If desired, you can create one more (or more) data source using the New … button (see figure 17).

After confirming to OK, the Report1.rdlc report file window will have a view, as shown in Figure 18.

Figure 18. The window of Report1.rdlc file after placing and configuring the Table table

⇑

6.3.2. Setting the table columns to display in the report

There are two areas in the Table1 control:

• the Header area, which displays the title of each column of the table
• the Data area, that displays a set of data, which are displayed in each row of the table. A dataset is generated based on the Query1 query.

⇑

6.3.2.1. Setting titles

To customize the name of each column, double-click in the corresponding cell in the Header area, as shown in Figure 19.

Figure 19. Setting the header for the field "Num_book"

As you can see from the figure, the system has generated a table that contains 3 columns. In our case, the query Query1 contains 6 columns. To add a column, you need:

• make a click with the right mouse button on the border of the table (gray background) in the corresponding column;
• execute the 'Left' or 'Right' command of the 'Insert Column' menu (Figure 20).

Figure 20. Calling the command to add a new column

To place the title of the table header in the center, use the TextAlign property (see Figure 21) of the TextBox13 object that was created for the first column of the table named "Book number".

Figure 21. Setting the centering of the table header

Also, you can adjust the width of each column of the table. After setting all the headers, the window of the report file will have the form, as shown in Figure 22.

Figure 22. The report window after setting the table headings

⇑

6.3.2.2. Configuring Data

At this stage, you need to configure the data that will be displayed in the rows of the table. The number of rows that will be displayed can be anything. The data is displayed in the Data area of the table.

Each column of the table is individually configured. The first column in the Query1 query is a column named Num_Book. This column should be selected in the context menu of the table as shown in Figure 23.

Figure 23. Setting the data displaying in the column Book number

The corresponding Query1 query fields are selected in all subsequent columns. After the formation of all the columns, the table will has view as shown in Figure 24.

Figure 24. The report window after configuring the table

⇑

6.4. Setting the report header

To set the report header, use the TextBox tool (see Figure 25), which you need to place above the table. After placement, the corresponding object will be created. To specify a name, you need to click in the rectangular area of the TextBox tool.

Figure 25. Specifying the title of the report "Query1"

If desired, you can improve (change) the type of the report with the help of other tools.

⇑

7. Designing an additional form and linking the report to a form

In accordance with the condition of the task, when clicking on the "Show Report" button, a new form of application can be opened with the created report. Therefore, in the program need to develop a new form.

⇑

7.1. Adding a form to the project

The development of an additional form is implemented in a standard way for MS Visual Studio. A more detailed example of creating a new form is described in the topic:

• Example of creating and calling a new form in C#

First, you need to add a form (a form file, a resource) to the project using the command

Project -> Add Windows Form...

As a result, the "Add New Item" window opens, in which you select the Windows Form template and leave the name of the Form2.cs file offered by the system.

Figure 26. The window for specifying the template of the created form

As a result, Microsoft Visual Studio will create a new form, which, so far, does not have any controls. Class, named Form2 corresponds to the form.

⇑

7.2. The Report Viewer control

In order for the report to be displayed in the application, the "Report Viewer" control is provided in the Microsoft Visual Studio (see Figure 27). This control connects the application (program) with the report file Report1.rdlc. First, go to the form design mode "Form2.cs [Design]". Next, from the ToolBox toolbox on the Reporting tab, select the ReportViewer control and place it on the Form2 form with the mouse, as shown in Figure 27. As a result, the system will automatically prompt you to select a report file in the ReportViewer Tasks window (see Figure 27). In addition, an instance (object) named reportViewer1 will be generated for the control.

Figure 27. Placement of the "Report Viewer" on the form

After selecting the drop-down button, the Report Viewer Tasks window displays a list of the connected (created) report files. In our case, you need to select one file named TrainReports1.Report1.rdlc (Figure 28). Here the prefix TrainReports1 stands for the name of our project.

Figure 28. Connecting the project file to the ReportViewer control

After selecting the Report1.rdlc file, adjusting the size of the Form2 form, and adjusting the size of the reportViewer1, the form window will appear as shown in Figure 29.

Figure 29. The additional form window after you place and configure the ReportViewer control

The system automatically generates three objects named EducationDataSet, Query1BindingSource, and Query1TableAdapter. These objects connect the application to the database. They can be used to programmatically manage the report. But this is a completely different topic.

To verify that the Report1.rdlc file associated with reportViewer1, it is needed to click on the arrow in the right corner.

Figure 30. Checking the reportViewer1 connection with the Report1.rdlc report file

⇑

8. Program code of the Form2.cs form file

After setting up reportViewer1, the system will automatically generate the report display code on the form in the Form2 event's Load event handler. The Load event of the Form2 form is called at the moment if you want to display the form on the screen. Therefore, calling Form2 from the main form (Form1) will automatically display the report.

The general listing of the Form2.cs module is as follows:

          using          System;          using          System.Collections.Generic;          using          System.ComponentModel;          using          System.Data;          using          System.Drawing;          using          System.Linq;          using          System.Text;          using          System.Windows.Forms;          namespace          TrainReports01 {          public partial class          Form2          :          Form          {          public          Form2()         {             InitializeComponent();         }          private void          Form2_Load(object          sender,          EventArgs          e)         {          // TODO: This line of code loads data into the 'EducationDataSet.Query1' table. You can move, or remove it, as needed.          this.Query1TableAdapter.Fill(this.EducationDataSet.Query1);          this.reportViewer1.RefreshReport();         }     } }

⇑

9. Programming the call of the Show Report button to call a report from the main form

The last step is to program a click on the "Show Report" button of the main form. Calling the Click event handler on the Show Report button is performed in a standard way for the MS Visual Studio. A detailed description of event programming is described in the topic:

• Example of event programming of button click in C#. Development of a program to determine the surface area of the sphere

Without going into the details of event programming, here is the code of the event handler button1_Click (), from which the report is called

          // click on the "Show Report" button          private void          button1_Click(object          sender,          EventArgs          e) {                      //          Form2          f =          new                    Form2();          // create an instance of form 2          f.ShowDialog();          // display the form, the report will be automatically displayed          }

As you can see from the listing, an instance of Form2 is created in a standard way and a form is called on which the report will be displayed.

⇑

10. Program code of the file Form1.cs

          using          System;          using          System.Collections.Generic;          using          System.ComponentModel;          using          System.Data;          using          System.Drawing;          using          System.Linq;          using          System.Text;          using          System.Windows.Forms;          namespace          TrainReports01 {          public partial class          Form1          :          Form          {          public          Form1()         {             InitializeComponent();         }          // click on the "Show Report" button          private void          button1_Click(object          sender, EventArgs e)         {          //          Form2          f =          new                    Form2();          // create the instance of form 2          f.ShowDialog();          // display the form, the report will be automatically displayed          }     } }

⇑

11. Running the application for execution (testing)

After running the application for execution, the program window, where the report is displayed, will appear (Figure 31).

Figure 31. Displaying the report in program

⇑

---

Related topics

• C# + Databases. An example of creating a report in application created using Windows Forms Application template for a database of the Microsoft SQL Server type, which is located in the *.mdf-file
• How to show MS Access database table in dataGridView
• Connecting to MS Access Database in Visual Studio 2010

⇑

---

Using The Current Worksheet Create A Report

Source: https://www.bestprog.net/en/2017/12/26/an-example-of-creating-a-report-in-an-application-created-using-the-windows-forms-application-template-the-microsoft-access-database-mdb/

Posted by: rollinghend1996.blogspot.com

Share this post