003 – Generating report of RaveReport type based on Microsoft Access table in Borland C++ Builder

Generating report of RaveReport type based on Microsoft Access table in Borland C ++ Builder

 

The task

  We have a database, that was created by Microsoft Access. Database is placed in the “udb2.mdb” file. Database contains three tables with the following structure:

05_01_00_003_00e

  The tables are linked by the following scheme (fig. 1):

05_01_00_003_01e

Fig. 1. The interconnection scheme between tables

 

  The ID_Student field of table “Student” is the lookup field for the ID_Student field of table “Session“. The ID_Subject field of table “Subject” is the lookup field for the ID_Subject field of table “Session“.

  Into tables of database already inputted about 5-6 strings.

  The task is to build the report using tools of C++ Builder, which is based on data from different   tables of database. For generating the report we will use “Rave Report” component, that is supplied with Borland C++ Builder. The structure of report is following:

05_01_00_003_report_e

 

Progress

  1. Load Borland C++ Builder.
  1. Creating a Windows-application.

Save the application.

  1. Connecting to database Microsoft Access.

  To connect to database file “udb2.mdb” we place on the form such components:

– from tab “dbGo” the component of TADOConnection type, which will be responsible of object variable named ADOConnection1;

– from tab “dbGo” the component of TADOQuery type (object ADOQuery1);

– from tab “Data Access” the component of TDataSource type (object DataSource1).

Main form of application has view (fig. 2).

05_01_00_003_02_

Fig. 2. Main form of application

Set up the properties of the component for access to database.

 Primarily, we configure the property ConnectionString of ADOConnection1 component. Select the ADOConnection1 component (fig. 3).

05_01_00_003_03_

Fig. 3. Property “ConnectionString” of “ADOConnection1” component

  In Object Inspector into the field of ConnectionString property click on the button ““. The window of wizard of connecting to database will be opened. Following step by step instructions of the wizard we configure the connection to the database Microsoft Access.

  Select such fields in the wizard:

– into the field “Provider OLE DB” we select the “Microsoft Jet 4.0 OLE DB Provider“;

– into the field “Connection” we select the name of database file “udb2.mdb“.

  After configuring the ConnectionString property in the component ADOConnection1, we connect this component with ADOQuery1 and DataSource1.

For this:

– the property Connection of component ADOQuery1 set to a value ADOConnection1;

– the property DataSet of component DataSource1 set to value ADOQuery1;

LoginPrompt property (component ADOConnection1) set to the value “false” (in order to avoid the constant query Login and password when you open the database).

  After actions, we have made the connection to the database “udb2.mdb“.

  With the help of ADOQuery1 component, now we can form a queries to the database and create own tables for generating of the report.

 

  1. The components from the Rave tab.

  At the Rave tab of components palette “Tool Palette” are shown the components for generating the reports. In our case we place on the form such components from the Rave tab:

– component of TRvProject type, which will be responsible to object (variable) RvProject1;

– component of TRvSystem type (object-variable RvSystem1);

– component of TRvDataSetConnection (object-variable RvDataSetConnection1).

05_01_00_003_04_

Fig. 4. Components RvProject1, RvSystem1, RvDataSetConnection1

 

Let us explain briefly the purpose of each component.

  TRvProject component connects with the file, in which the report is formed. The file has “*.rav” extension. In our case name of file has the same name as proposed by default, ie “Report1.rav“. The file has all the necessary tools to create the report: text fields, graphic objects, data fields for getting the information from database and others.

  TRvSystem component implements the customization capabilities of settings page, setting up of fields at print the report and more.

  The TRvDataSetConnection component is used for connecting between the report, that is in file “Report1.rav“, and database.

  In general, the scheme interconnection between components of application, database and file of report has the view (fig. 5).

05_01_00_003_05e

Fig. 5. The scheme of interconnection between components of application

 

  1. Creating the file of report.

  The work to creating a file of report is carried out with the help of a special editor, which is called from the context menu after clicking the right mouse button on the component RvProject1.

  In the context menu we select “Rave Visual Designer…” (fig. 6).

05_01_00_003_06_

Fig. 6. Selecting “Rave Visual Designer…” editor

  In general case, the window of editor “Rave Report” has the view (fig. 7).

05_01_00_003_07_

Fig. 7. The window of editor “Rave Report

  As we see, the sheet of report is empty yet and does not contain any information.

  On the next step, we have save the report by selecting the “Save” command from the “File” menu. As a result, the window “Save Project1.rav As” will be opened. In this window we select the folder in which will be saved file of report (fig. 8).

05_01_00_003_08_

Fig. 8. The window of saving the report

  It is recommended to save a file of report in the same directory with the files of developed application. We will do so, leaving filename “Project1.rav“.

  1. Setting up of component RvReport1.

Let’s configure the component RvReport1.

  At first, we configure the file path “Report1.rav“. For this, in the field of property “ProjectFile” we do click on the button “” and in the window of file selection we select the file “Project1.rav“, after selecting the folder in which this file was saved (see item 5).

  On the next step, we set the property “Engine” to value RvSystem1 (from dropdown list). It means, that the page settings and the view mode will be set for file of report “Report1.rav” (fig. 9).

05_01_00_003_09_

Fig. 9. Configuring the properties of RvProject1 component

 

  1. Setting up the connection between Rave Report and database.

  For setting up the connection between Rave Report and database use the RvDataSetConnection1 component.

  The property DataSet of this component set to value ADOQuery1 (fig. 10).

05_01_00_003_10_

Fig. 10. Setting up DataSet property of RvDataSetConnection1 component

 

  1. Setting the component ADOQuery1.

8.1. Forming a SQL-query.

  Since in the problem you need to create a report based on data from three tables, it is best suited for this request to the database the SQL language. This is the reason of using ADOQuery1 component in the program. Property “SQL” of component ADOQuery1 allows you to create such requests.

  In our case, the request at SQL-language will have view:

SELECT [Session].Year, [Session].Semester, 
       Student.Name, Subject.Name
FROM [Session], Student, Subject
WHERE ([Session].ID_Student=Student.ID_Student) AND             ([Session].ID_Subject=Subject.ID_Subject)

  As seen from text of SQL-query, the data are got from three tables and contain four fields under the terms of the problem.

  We type the above text into property “SQL” of component ADOQuery1. The editor for text is called by clicking the mouse on the button ‘‘ in the property “SQL” (fig. 11).

05_01_00_003_11_

Fig. 11. Editor for SQL-query of ADOQuery1 component

 

8.2. Activation ADOQuery1.

After that we need set the property “Active” to value “true“.

 

8.3. Adding the displaying fields to ADOQuery1.

Do the click of right button of mouse at component ADOQuery1. In the context menu select “Fields Editor…“. As a result the fields editor will be opened. Next in the editor window with the help of right button of mouse using command “Add All Fields” add all fields:

Year;

Semester;

Student.Name;

Subject.Name.

 

  1. Generating report with the help of wizard.

  Again, we call the editor of your report “Rave Reports” by calling “Rave Visual Designer …” from the context menu of component RvProject1 (see item 5). Automatically into editor of reports will be downloaded the file, which is connected with RvProject1 component (in our case file named “Project1.rav“).

 

9.1. Adding data to report in report file “Report1.rav”.

  In order for the database file was available in the report “Report1.rav” we need to create a new data object. This implemented by command “New Dataobject” from the menu File (fig. 12).

05_01_00_003_12_

Fig. 12. Creating a new data object

 

  As result the window “Data Connections” will be opened. In this window is proposed several cases of choice of type of data object. In our case we select “Direct Data View” and we click on the “Next >” button (fig. 13).

05_01_00_003_13_

Fig. 13. Selecting “Direct Data View” connection

  In the next window a list of active connections with data opens. In our case only the one connection is active, that corresponds to RvDataSetConnection1 component from main program. We confirm our choice by clicking on “Finish” button (fig. 14).

05_01_00_003_14_

Fig. 14. Selecting Active Data Connections

  On the right side of editor we can see, how was added “DataView1” object into “DataViewDictionary” list. If we

  If we will uncover this object (key “+”), we can see the newly created fields of table, which are based on SQL-query of component ADOQuery1 in main program.

05_01_00_003_15_

Fig. 15. The fields of DataView1 object

  For field “Year of putting session” corresponds the DataView1SessionYear object, for field “Semester” of table corresponds DataView1SessionSemester object etc.

  After that, we already have the link of report with database.

9.2. Calling of wizard of report building.

  The view of report and view of data in it we can form by using tools of “Rave Reports” editor, that are on the toolbox. But for work with data, which are received from database, is recommended to use the built-in wizard of report generating.

  This will greatly facilitate the process of linking of data with elements of the report. After calling of wizard, we can edit the view of report as we like.

 The data from database for report are taken from RvDataSetConnection1 component, which is formed on the query that is described in ADOQuery1 component of program.

  So, the wizard is called by sequence of clicking such commands (fig. 16):

“Tools –> Report Wizard –> Simple Table”

05_01_00_003_16_

Fig. 16. Calling the wizard of report building

  As result the window “Simple Table” will be open. In the window is proposed to select the element of data view. In our case we have only one object DataView1. Click on the “Next >” button (fig. 17).

05_01_00_003_17_

Fig. 17. The window of selecting of element “DataView1” of data view

  In the next window (fig. 18) is proposed to select the fields of DataView1 object, that will be used in the report. We select all fields, since they all have to be displayed in accordance with the condition of the problem. Click on the “Next >” button.

05_01_00_003_18e

Fig. 18. Selecting fields from DataView1 to display them in a report

  In the next window (fig. 19) is proposed to regularize the fields. We leave all as is. Click on the “Next >” button.

05_01_00_003_19e

Fig. 19. Selecting the display order of fields

  The next window of wizard (fig. 20) proposes to set the display options:

– report title (field “Report Title”);

– possibility of printing of report title (Print Report Title);

– limiting fields, which are related to page size, within which a report will be generated.

  We change only title, for example “My Rave Report”. Click on the “Next >” button.

05_01_00_003_20e

Fig. 20. Setting up the options of report display (title, printing fields)

  In the last window of wizard (fig. 21) is proposed to change the font of title, message font and main font. It is done by clicking on the one of the buttons with name “Change Font”. Click on the button “Generate”.

05_01_00_003_21e

Fig. 21. Changing the font of title, message font and font of main text

  After this the report will be generated (fig. 22).

05_01_00_003_22e

Fig. 22. The window of generated report

  1. Running the report.

  We run the report by “F9” button or by command “Execute Report” from the menu “File”. As a result, the window (fig. 23) will be shown, in which we confirm by clicking on “OK” button (view mode – Preview).

05_01_00_003_23_

Fig. 23. The window of executing of report

  On the next step the report will be displayed (fig. 24).

05_01_00_003_24e

Fig. 24. Display of the report on screen

  As see on figure, the report has not correct display, although the data is displayed well.

  With the help of tools of “Rave Report” editor we correct this data. In order to change properties any element (object) of report we need select it before. Then in the object editor (like to “Object Inspector”) we must change the corresponding property.

  For example, to change the text message, we must use the property “Text” (fig. 25).

05_01_00_003_25e

Fig. 25. Changing the text with the help of “Text” property

  At the top side of editor is placed tools palette. For example, to set the centering the object in it’s own limits we need use the tab “Fonts” from tools palette (fig. 26).

05_01_00_003_26e

Fig. 26. Tab “Fonts

  It is important here not to confuse the bands in the report. Is shown below the different types of bands (fig. 27).

05_01_00_003_27e

Fig. 27. Forming bands in the report

  At the figure with number 1 shown the band, that corresponds to title of report. At the number 2 is shown the band of fields titles of table. The number 3 shows the band, in which data are displayed, that were formed on SQL-based query in component ADOQuery1 of application.

  Change the report in more

 We change the report into a more intuitive view. Run the report again (press F9).

05_01_00_003_28e

Fig. 28. Final Report

  Save the report file as “Report1.rav”. Exit the Rave Report.

 

  1. Calling the report from the main program.

  After report creating we will realize a programming of an event of preview the report.

  For getting this we place on the form the component of TButton type (corresponds the variable-object Button1). The “Caption” property of the button we set to “Show report” value (fig. 29).

05_01_00_003_29e

Fig. 29. Main form of application (the final view)

  We program the event of clicking at the Button1. The program code of this event has next view:

TForm1::Button1Click(TObject *Sender)
{
  RvProject1->Execute();
}

So, to call the report enough call the method Execute() of RvProject1 component, that is linked with the report (the “Project1.rav” file).

The task is solved.

 

Summarize

  To form the report, in which data are getting from several tables, we need to form an SQL-query to database. This SQL-query will form a table, that is needed to form the report. Then, this table must be use for set up of report work and with the help of wizard in report editor we need to form the report.

  As a whole, the application uses following components:

– from the “dbGo” tab, the component of   TADOConnection type, which is needed for linking with database;

– from the “dbGo” tab, the component of TADOQuery type, which includes a SQL-query;

– component of TRvProject type, which links our application with the report file (“Report1.rav”);

– component of TRvSystem, which realizes page setup for report output to print;

– component of TRvDataSetConnection type, that realizes the connection between database and report;

– component of TButton type.

  For building the report file “Report1.rav” is used the editor “Rave Report 7”.