Designing a Report

After you have specified the parameters of the new connection, click the Connect button to establish the connection. If the connection succeeds, your report is ready to run. Before running the report, we rename our connection to northwind; the connection name of our data source must be changed to northwind as well. To do so, open the Report  Data sources…​ dialog again and change the connection name of the data source to northwind.

The defined connections are also listed in the Report  Database connections submenu and on the Database Connections toolbar.

Each SQL data source has its own connection name, which is used for running its SQL query. The QSqlDatabase connection must have the same name as the one set in the data source. In the Designer, create a connection in the connection manager with the same connection name you defined in the report.

When the SQL data source does not have a connection name, the report engine runs the SQL query using the default database connection. To test such a report, create a connection in the Designer’s connection manager with default as the connection name. This connection is opened as the default connection, so you can run your report from the Designer with an empty connection name in the data source.

The core information in a report is displayed in its Detail section. This is the most important section of the report, since it contains the row-by-row data from the data source. The Detail section has the following characteristics:

The page header contains the page headings. The page header has the following characteristics:

Most reports need a page header. To add or remove the page header, select Report  Report and page settings…​; a dialog then appears in which you can set the page options of the current report. To enable or disable the page header, use the Page header check box on the Sections tab.

Page footers are commonly used to close the pages. The page footer has the following characteristics:

The page footer is usually used to display information such as the page number, the report title, and so on. Most reports need a page footer. To enable or disable the page footer, use the Page footer check box in the Report  Report and page settings…​ dialog.

The report header contains the report headings. The report header has the following characteristics:

To enable or disable the report header, use the Report header check box in the Report and page settings dialog, which you can open by selecting Report  Report and page settings…​.

The report footer is commonly used to close the report. The report footer has the following characteristics:

To enable or disable the report footer, use the Report footer check box in the Report and page settings dialog, which you can open by selecting Report  Report and page settings…​.

These options are used when the report type is Label:

In label mode, the label size and the number of rows and columns must be set, and the page and report headers and footers must be disabled; otherwise the dialog displays an error message.

The following buttons are available for applying or canceling the settings:

Specify the report page properties in the Report and page settings dialog and confirm the settings by clicking the OK button.

In our example, we choose the SQL query data source type. After you click the Create button, a new SQL query data source is added to the list in the dialog. You can then specify the data source options. The following properties are available for data sources:

In our example, we keep the default data source ID and choose the Static location type. We set the connection name to Con0. Then we specify the SQL query.

Let’s use this simple query:

The following buttons are available for applying or canceling settings:

Confirm the data source settings by clicking the OK button.

First, let’s add the labels that represent the column headers of the data rows. To create a new Label object, select the Label tool button or the Insert  Label menu item. The cursor then changes to a crosshair. Click in the section where you want the label to be located. (In our example, we add the labels to the page header.) Doing so creates the Label object in that section and opens the label settings dialog, in which you can set the properties of the label.

The following options are available for labels:

The following buttons are available for applying or canceling settings:

Add the following labels to the page header: Product ID, Product name, Unit Qty, Unit price, and Value, and move them to the location you want by drag and drop. To move a label, drag it with the left mouse button and drop it at the new location. To delete a label, select it and press the Delete key.

To create a new Line object, select the Line button in the toolbar or the Insert  Line menu item. The cursor then changes to a crosshair. Click in the section where you want the line to start and drag the line to the end position. To move a line, drag it with the left mouse button and drop it at the new location. To delete a line, select it and press the Delete key.

The style, width, and color of the selected line can be set using the Shape properties toolbar. To open the line properties dialog, double-click the line. The dialog provides the following option:

Now we add the most important items to the report. Field objects contain dynamic information retrieved from a data source, a parameter, or a variable. To create a new Field object, select the Field tool button or the Insert  Field menu item. The cursor then changes to a crosshair. Click in the section where you want the field to be located, which is generally the Detail section. Doing so creates the Field object in that section at the clicked position and opens the field settings dialog, in which you can set the properties of the field.

The following options are available for fields:

The following table summarizes the various formulas you can specify in fields as the field column expression. The formula depends on which field source type you use.

Some properties are available only for certain data types. They are located on separate tab pages of the dialog (Text, Numeric, and Date/Time). The following additional options are available for numeric fields:

The following buttons are available for applying or canceling settings:

To continue our example report, add the following fields to the detail section, using these column names and data types: ProductID (Numeric), ProductName (Text), QuantityPerUnit (Numeric), UnitPrice (Numeric), and Value (Numeric).

Before we add a variable field to the report, let’s look at how variables are handled in NCReport. Variables are special items used for providing counts, totals, and other aggregate values. Each variable has a name, a function type, and a data type, and is based on an expression, usually a data source column. To add a variable, select Report  Variables…​. A dialog then appears in which you can manage variables. The variables are listed in a table with the columns ID, Expression, Function, Scope, and Init Value, which can be edited directly in the table.

The following options are available for variables:

The variables added to the report are shown in the variable table. Clicking a row makes that variable active. The following buttons are available in the dialog:

Add a new variable by clicking the Add button, and then specify the options as follows: Variable ID: total_value, Variable expression: value, Function type: SUM, Reset scope: Group. To print the total, we first have to add a new group to the detail. In the next section, we explain how to use the grouping feature.

While many reports can be defined using a single Detail section with multiple columns and rows of data, others, like our example report, require summary data such as totals and subtotals. For such reports, NCReport supports group sections. Group sections have the following characteristics:

In the group dialog, the groups of the detail are shown in the order you added them. The added group sections appear in the Designer after you apply the group settings. Groups are structured hierarchically: the first group is the primary (outermost) group level, the second one is the second level, and so on.

To add a new group to the detail, select Report  Details and grouping…​. The Detail settings dialog then appears. Select the Detail1 detail in the list, then click the Groups…​ button to open the Data grouping dialog, which always belongs to the selected detail. To add a new group, click the Add button.

The following options are available for a group:

The groups added to a detail appear in the group list. Clicking a list item makes that item active. To delete the selected group, click the Remove button. The following buttons are available in the dialog:

Now let’s add a new group with the following settings: Group ID: Group0, Group expression: %CONST, group header and footer enabled, and the total_value variable reset. After you click the OK button, the new group sections (header and footer) appear in the report. Close the Detail settings dialog by clicking the OK button.