Copyright: (C) 2008 QBS Software Ltd,  http:
Citation: (C) 2008 QBS Software Ltd,  http:


Content
Content	License- and copyright information for the report designer

Introduction to the report designer

Basics

•	Definition of terms

•	Sections of a report (report bands)

•	Report elements

Report design

•	Create a new report with the report expert

•	Add a new report element

•	Edit a report element's properties

•	Toolbar

•	Using the mouse

•	Using the keyboard

Databases

•	Database setup

Step-by-step examples

•	Modify an existing report

•	A simple address listing

•	An advanced address listing

Report elements and their properties:

Report bands

•	Standard bands (Title, Detail, ...)

•	Subdetail band

•	Group band

•	Child band

Report elements:

•	Data field

•	Label

•	Memo

•	Image

•	Image from data field

•	Expression field

•	Shape

•	System field

•	Richtext

•	Richtext from data field

Advanced topics

•	Formatting numerical fields and date or time fields

•	Expression syntax

•	Using parameters in SQL statements

Other

•	Menu item "View|Options"

•	Menu item "Report|Options"

•	Questions and answers, tips and tricks


Definition of terms

Reports and databases

Report

The term "Report" describes a printout of data records from a database. The report defines the look of the printout (layout, fonts, arrangement of data fields, etc.). For example if you want to print a list of addresses, you use a report to tell the computer which fields of your address database to print and in which order. A report can print data as a list, page by page or any other way you want your data to be printed.

Table

A file with data records of the same type is called table, e.g. a file with address information, parts or invoice items. A table can be a single file, for example a DBase or Paradox file, or it can be part of a database (see below).

Query

A query consists of SQL statements, which are used to access a table or database and to retrieve a group of data records. SQL means "Structured Query Language" and is a kind of programming language for databases. A complete description of SQL is beyond the scope of this manual, but there are many books available on this topic.

Dataset

A dataset is used as a collective name for tables and queries.

Database

Queries and tables can be grouped together in a database, which can be a local file on your harddisk or a database server in a network. To access a table you need either its directory if it is a single file, or you need the database name if the table is part of a database.

Alias

Aliases are descriptive names available as placeholders for pathnames or databases.


Report sections (bands)

The image below shows the first page of a sample report, which prints country names, their capitals and their populations. This report can be divided into several sections:

These sections are used for report designing and are called "Bands"; the report is a so called "banded" report. For example there is a "Footerband" for all text in the page footer and a "Titleband" for the report title on the first page. The most important band is the "Detailband". The detailband is printed once for each data record (for each country in the above example), which results in a list of the data. The detailband's layout is the same for each record, so it must be created only once, for one record, with placeholders for each data field. For every record the detailband is printed after the placeholders have been replaced with the current record's data.


Report elements

Apart from report bands, which divide the report into logical sections, a report consists of report elements placed on the report bands. These elements define what exactly is printed on each band.

The two most important report elements are "Labels" and "Datafields". Labels are used for printing text - for example a title - that has no connection to a database. Datafields are placeholders for fields from a dataset. Whenever a data field is to be printed, the report engine gets the field content from the dataset and puts this text where you placed the datafield.

Usually labels are add-ons for datafields, to make the report look better and more understandable by not only listing data, but also showing which kind of data is printed too.

The following picture shows the report definition for the above country listing:

The following types of report elements are available when designing a report:

•	Datafields to display text from a dataset

•	Labels to display fixed text

•	Systemfields (date, time, page number, ...)

•	Shapes (circle, rectangle, line)

•	Image (Windows bitmap file)

•	Image from a data field

•	Expression (calculated field, mathematical calculations, ...)

•	Memo (text with multiple lines)

•	RTF (Richtextformat = formatted text, only available with 32-bit applications)

•	RTF from a data field (only available with 32-bit applications)

•	Chart (charts, graphs, ...)

Each report element has specific properties (font, color, ...), which you can edit. This is further described in the chapters about the different elements.


Create a new report with the report expert

Choose the menu item "File|New" to create a new report, or press the corresponding speed button. The report expert will be shown, which you can use for fast and easy creation of a first report design.

The report expert asks you which kind of report you want to create, which dataset to use and how to layout the report. The needed report elements will be added automatically to the report. After the expert has finished you can continue editing the report to make it fit your needs.

If you select "New empty report" as the report type, a completely empty report will be created and the report expert will exit. You must then add all report bands and datasets manually.


Add a new report element

Buttons for adding report elements to the report can be found on the left side of the designer window:

 Reportband	 Childband	 Subdetailband

 Groupband	 Label	 Memo

 Image/Bitmap	 Shape	 Systemfield

 Datafield	 Image from data field	 Expression field

The availability of the following report elements depends on the used platform (16 or 32 bit) and on the application in which the report designer is used:

 Richtext field	 Richtext from data field	 Chart

To add an element to the report you must click its button and then click on the report where you want the element to be inserted. Of course you can still move a report element to another position later.

When adding new report bands it doesn't matter where you click on the report, because the position of each band is determined by its band type (page header, title band, ...) automatically.


Edit a report element

The individual properties of a report element can be changed anytime during report design. Select the desired element and either double-click with the left mouse button, or press the right mouse button and choose "Properties" from the popup menu. The property dialog for the selected report element will open, which is the same dialog as the one that shows up when inserting a new report element. The following picture shows the property dialog for a label:

Edit the element properties to your needs and press the "OK" button to close the dialog and accept your changes. If you press "Cancel", all your changes are dropped and if you were inserting a new element, it will not be added to the report.

Information about the different properties for each report element can be found at the individual element descriptions.


Toolbar

You can change many report element properties directly with the toolbar instead of using the element's property dialog. Additionally you can use it to modify multiple elements at once.

This buttons are for creating a new report, loading a report file and saving a report.

Use this buttons to print the report or to view a preview of the printout.

With this buttons report elements can be cut to, copied to or pasted from the report designer's clipboard.

This buttons are used to set a report element to the background or bring it in front of all other report elements, in case of overlapping elements.

Report elements can be arranged with this buttons. Some of the buttons are only enabled when multiple elements are selected (for example to align the left edges).

This part of the toolbar shows the type of the currently selected report element and its main property (text if it is a label, data field, band type, ...). You can edit this property directly with the toolbar, without the need to open the element's property dialog.

Here you can set font and font styles of the selected report elements (font name, font size, bold, underline, italic).

This buttons are for aligning text within a report element. Text can be aligned left, right or centered. Of course this only makes sense if the report element's size is bigger than its text, and if "autosize" is deactivated.

With this button you can open a dialog to set the frame options for the selected element.


Using the mouse

Select a report element

Report elements are selected by clicking them with the left mouse button. A selection is shown with eight small black boxes around the element:

Change the size of an element

You can use the small boxes of a selected element to change its size. If the mouse cursor is positioned over such a box, the cursor will change to show in which direction the mouse can be moved while the left mouse button is pressed down. The element will be resized accordingly. Please note that for report bands only the height can  be changed, but the width is set to the report width automatically.

Select multiple elements (a)

You can select multiple elements by holding down the [Shift] key while selecting them with the left mouse button. This way the previous selection will remain when a new element is selected.

Select multiple elements (b)

You can also select multiple elements by using a so-called "rubber band". Hold down [Ctrl] together with the left mouse button and drag a frame around the elements that you want to select. After releasing the mouse button all elements within the frame are selected.

Move elements:

You can move report elements with the left mouse button. Hold down the button, drag the element to the desired location and release the mouse button. Report bands can not be moved because they are positioned automatically according to their band type.

Move and resize elements regardless of the current grid setting:

If you want to manipulate a report element regardless of the current grid (menu item "View|Options") and move or resize by one pixel, then hold down the [Shift] key while moving the mouse.

Edit element properties:

If you double-click a report element with the left mouse button, or if you select "Edit" from the element's popup menu (which can be accessed with the right button), a dialog will be shown where all element properties can be edited.


Using the keyboard

The report designer can be used with the mouse most of the time. Some functions can also be accessed with the keyboard:

Enter:

Show the element's property form

Cursor keys:

Move a report element

Shift + Cursor keys:

Resize a report element

Del:

Delete a report element

Tab and Shift + Tab:

Select next or previous report element


Datasets

In  most cases each report needs at least one dataset, which supplies the data records for the report.  You can define all datasets and their relationships via the database setup (menu item "Report|Datasets").

Note: The "Property" button shows a dialog with the settings of the currently selected table or query. If the dialog elements in that dialog are disabled, you are not allowed to change the dataset's settings.

This dialog window shows the main dataset for the report, which is the dataset that the report runs through and prints all data records from. Below the main report table this is a list of all datasets available to the report. Currently there is only the country table, which is the main dataset, but you can add more tables or queries that you can use in your report too.

To add a table or query, use the corresponding button.

New table:

 If you press this button the following dialog will be shown:

This is a common file open dialog where you can select the desired table. Additionally you can use aliases instead of directory paths if available.

New query:

The following dialog will open if you press the "New query" button:

Here you can supply a query in SQL language. If your query has parameters, you can set their values with the "Parameter" button. If the parameters should be retrieved from another dataset, select this table or query in the "master" combobox.

Data fields:

If you select the "Fields" button, a list with all available fields of the currently selected dataset will be shown.

Note: The buttons in this dialog are disabled if you are not allowed the edit the dataset's fields.

You can remove (=hide) single fields from the dataset, so that they will not be available in the report editor anymore, or you can edit a field's properties:

A display label can be set for each data field, which is used as an alias name in the report editor. This way you can have descriptive field names even if your physical dataset has not, for example "CustNo" can be replaced with "Customer".

Use the "format" edit line to format numerical fields (see Formatting of numerical fields). "Precision" determines how many digits after the decimal separator are displayed.


Modifying an existing report

This chapter shows a simple example on how to use the report designer. An existing report with datasets and report bands already set up will be modified. Look at the following example:

This report layout will result in the following printout:

Change the height of the data lines:

Let's say the data lines are too close and you want them to be further apart. Each line gets its layout from the report's detail band. For each data record the detail band is printed once, so if you change the height of the detail band, the space for each data record will be increased. To do this, in the report designer first select the detail band by clicking it with the left mouse button. Small black boxes will appear at the corners of the band, which are used to resize report elements. Left-click the middle box at the bottom of the band, hold down the mouse button and move the mouse cursor down. This way the detail band will be resized, and after releasing the mouse button the new band height is set.

You may now press the "Preview" button or select "Report|Preview" to check the new report. You will see that now there is more empty space between each line of data.

Move report elements:

Maybe you don't like the positioning of some of the report elements, and for example you want the first data field to be slightly more to the right. You can change this by just clicking the data field with the left mouse button (and holding the button down) and dragging it to its new position on the band. Please note that you can not move  elements to another band, and you can not move report bands because their position is determined automatically by their band type (e.g. page header at the top of the page).

Change fonts:

If you want to change the font of a report element, first select it (you may also select multiple elements at once) and then use the toolbar to change the font settings:

You can select a font name, font size and style (bold, underline, italic). The available fonts depend on the installed fonts on your computer.

Add and change label:

Press the "label" button  [image] to insert static text into your report. After pressing the button click on the report where you want to add new text. The label property dialog will be shown, where you can type in your text and set some other options. After hitting the OK button the text will be inserted into the report and will later be printed out at this position.

If you want to change the text (or any other property, e.g. text alignment) of an existing label, select the report element with your left mouse button and reopen the property dialog, either with a double click or by selecting "Edit" from the popup menu (accessible with the right mouse button).


A simple address listing

This chapter is a step-by-step instruction to create a simple report that prints an address list. A table is used which contains all address records. You can follow all steps with the table available to you because the type of data is not important for this example. If you don't want to create a complete new report for your application, but only want to modify existing reports, than you may skip the first step and load a report instead.

Step 1: Creating a new report with the report expert

Step 2: Edit the report layout

Step 3: Preview and save the report


Step 1: Creating a new report

Select the menu item "File|New" or press the corresponding speed button. The report expert will show up and you can select the type of report that you want to create. For our example we want to print an address list, so we use "List style":

Press the "Continue" button.  You can now set the report's main data source. Type in the path and filename of the table you want to use, or press the "Search" button to select the desired file:

On the next page of the report expert you must select which fields to use. You select which fields from your table are added to the report layout automatically. You can drag fields from one listbox to the other with your mouse, or you can use the buttons located between the listboxes. In the right listbox you can drag fields up and down with your mouse to change the field order.

The next page of the expert lets you select the report bands that you want to use:

Afterwards you can set some general options, for example how many columns your report should have:

The following page determines the look of the report. You can set default fonts for text and data fields, and you can have the expert insert lines between the columns and rows of the report:

Now the report has been setup. You can either select "Finish" to return to the report editor, where you can edit the created report and save it, or you can select "Preview" to check the report printout first. You will see a preview of the report on screen like it would be printed. If you don't like what you see, you can use the "Back" button to change the settings you have made for the report expert.

Continue with Step 2: Edit the report layout


Step 2: Editing the report

Preview:

You are now in the report editor and can see the report layout. Press the "Preview" button [image] to get a first look at the resulting report printout if have not done this at the end of the report expert.

Change the column captions:

Obviously the column captions for the data fields don't look very good ("Addr1" and "Addr2"). The report expert has used the field names of the data fields automatically, which doesn't  always lead to optimal results. To change this, leave the preview and select the desired label with the left mouse button. The toolbar will now show you the element type, e.g. "Label". Additionally you can see the element's most important property, which for a label is its text of course. You can activate this edit line with your mouse and directly edit the label text there, or you can open the property dialog for the report label by opening its popup menu with the right mouse button and selecting "Edit". The following shows the label's property form:

Edit the element properties and type in a more descriptive text for the column header, for example "Address". Leave the dialog with "OK" afterwards.

Change size and position:

If you don't like the position of a report element, simply move it around with your left mouse button. Please note that elements can not be moved out of their report band.

To change the size of a report element, first select it and then drag one of the eight small black boxes to resize the element.

Add another report element:

For demonstration purpose we will now add a circle around the report title. Please click the speed button [image] to insert a shape.

Now click on the report band that contains the report title. It is not important to click on the exact position where you want to place the circle, because you can easily move the report element later. After clicking on the report band the property dialog of the new shape will be shown. Select "circle" from the "Type" combobox:

Press the color button from within the "Brush" area and select yellow color to draw a yellow filled circle. After leaving the dialog with "OK" a yellow circle will appear on the report layout:

Unfortunately the circle is in front of the title and conceals part of it. Press the button  [image] from the toolbar at the top of the designer window to move the circle to the background. Now move the title so it is positioned over the circle:

Of course it still does not look good because the white background of the label covers the circle. Open the property dialog of the title label (right mouse button, "Edit") and activate the "Transparent" option. The background of the label will now be transparent (not in design mode, but you can see this when using the preview):

Lastly we will do some arranging of the title to move it exactly to the center of the circle. First select the label as usual, then hold down the [Shift] key and select the circle. By using the [Shift] key the last selection will not be lost when selecting a new element, so multiple elements can be selected at the same time, provided they are on the same report band:

Now press the [image] button to align the horizontal centers of the report elements.

You can also center the two elements on the band by using the  [image] button.

Continue with Step 3: Preview and save the report


Step 3: Previewing and saving the report

While creating your report you can always look at a preview by choosing "File|Preview" or pressing the preview button  [image] . The report will be shown on screen like it would be printed and you can check the report layout.

If you are done with your report editing, press the save button  [image] to save your report.


An advanced address listing

The previous example showed how to create a simple report by using the report expert. In order to better understand the layout of reports and the purpose of the various band types, we will now create a report from scratch without the report expert. This example is more abstract than the last one.

Step 1: Create a new report

Step 2: Database setup

Step 3: Insert a detail band

Step 4: Add a group band

Step 5: Page header, title band and summary band


Step 1: Creating a new report

Select "File|New" or press the corresponding button to create a new report. The report expert will be shown and the option "New blank report" is selected by default. Now just press the "Continue" button, and the report expert will exit, leaving you with a completely empty report.

Continue with Step 2: Database setup


Step 2: Database setup

Adding a dataset:

First we will now setup the report's main dataset. Select the menu item "Report|Datasets", which shows the database setup dialog. Press the "New table" button and select the desired table for your report. The table will appear in the list of available datasets.

Properties:

You can select the new table and press the "Properties" button to show a dialog window with various settings. You can set the table's name, which is used by the report designer when a dataset needs to be selected. This name is an alias and does not affect the physical filename. By default the name is set the filename with the suffix "_Table", for example "CUST_Table" if your file is "CUST.DB". You may want to set the name to "Customers" to be more descriptive. The name does not affect the report printout, it is only used during design time.

Additionally you can set the table's active key (if there is more than one), so you can choose in which order the data records are printed.

Exit the properties dialog using the OK button to return to the dataset listing.

Main report table:

Since a report can use many datasets you must tell the report which table to use as the main report table, the table that is run through from the first to the last record. Use the combobox at the top of the database setup dialog to set this table. Select the table that you have just added.

Continue with Step 3: Add a detail band


Step 3: Adding a detail band

Add a detail band

After you have selected the dataset for your report, you must setup the report layout. The main part of a report is the detail band. This band is printed once for each data record. To add a detail band press the [image] button and click somewhere on your report. The band's property dialog will open. Now select "Detail" as the band type.

Add data fields:

After adding a detail band to the report you must now insert the data fields which you want to print. Press the  [image] button and click on the detail band afterwards to insert a data field report element. Select the data field you want to use and set its alignment, font and other options if needed. You can now move and resize the data field on the detail band with your mouse.

Repeat these steps with all data fields that you want to print. As an example, the detail band could look like this after inserting some data fields:

You can use the alignment buttons to align report elements relatively to each other. Let's say you want to make sure that all elements have the same vertical position. First select all elements you want to align ([Shift] + left mouse button or hold down [Ctrl] while drawing a selection frame with the left button). Then press the  [image] button, so that the top edges of all selected elements are aligned.

Preview:

While designing the report you should often check its resulting printout by using the preview button

Continue with Step 4: Add a group band


Step 4: Adding a groupband

You can make a list much more readable by dividing it into paragraphs. For example the address list can be structured by the first letter of the name. Each letter should have its own paragraph and start on a new page. To accomplish this you can use a group band:

To insert a group band, press the  [image] button and click on your report. The group band property form will be shown, where you must set the table on which the group should work. Since we only have one table with address data, which is the main report table, the report itself must be selected as the group's "master". Now each time a record from this table is to be printed, the report engine will check the group expression and compare its result with the result of the previous record. Only if the results are not the same the group band will be printed. If you want to group our sample address table by the first letter of the company name, the expression must be "Copy(Customers.Company,1,1)" (see the chapter on expressions for more information). Leave the dialog window with OK now.

You have now added a group band to the report. Take a look at the report preview and you will see that the group band is inserted before each new first letter of the company name. The group band will only result in some empty space because there are no report elements on it, but you can add labels, expression fields or any other elements to the group band if you like. For example you could add an expression field with the group's expressions, so that the first letter is printed before each group of data records. The right image shows a list without grouping, the left one shows a list with such a group band:

Continue with Step 5: Page header, title band and summary band


Step 5: page header, title and summary band

Lets assume you we have created a report which lists addresses from a table, the report layout is already quite useable and the preview might look like this:

What is missing is some "decoration", like a title on the first page and a page header with column captions for each data field. Also a summary at the end of the report, presenting the total number of printed records for example, would look good.

Page header:

Each page of the report printout should have a page header with column headings for each column of data in the address list, i.e. there should be a big "Name" label above the column of company names etc.

First we insert a new band for the page header by using the  [image] button and selection "Page header" as the band type. Now we add labels for each column, with captions corresponding to the field names.

Example:

To set a bigger font size, bold font style or another font you must select all labels and set the desired font attributes with the toolbar.

Title band:

A title for the first page of the report can be added by inserting a new report band and settings its band type to "Title band". Then add a label to the title band and set its font size to something big, like 24 points for example. Additionally you could add the current date and time to the report header, so you can see when the report was printed for each printout. To do this use the button [image] to add a system field to the top right corner of the title band, and set its type to "Date/Time". Now the title band looks the following way:

When previewing the report you will notice the following problem: The page header appears on the first page before the title band (just like the two bands are positioned in the report editor). You can correct this by printing the page header only on the second page and following, but not on the first page, and by adding the now missing column captions to the bottom of the title band. First select the page header band and open its property dialog. Select the option titled "not on first page" to prevent the band from being printed on the first report page. Then resize the title band to make room below the title label for the content of the page header. You don't need to recreate all report elements for the title band that are on the page header, but you can select them on the page header band, copy them to the clipboard, select the title band and paste the clipboard elements there. Now that all elements are copied from the page header to the title band, they are still selected and you can move them to the bottom all together.

Summary band:

To add a summary to the end of the report, insert a new band and set its type to "Summary". You can now add report elements to this new band, for example you can use a "Detail count" system field  and set the field's label to "Number of records printed:". The number of records will now be printed at the end of the report, e.g. "Number of records printed: 78" if you have 78 data records in your report table.

The complete report layout could look like this:

The following printout would be generated:


Standard band types (Title, Detail, ...)

All standard report bands are added with the same button (see above). The band type is selected with the band's property dialog.

The following band types are available:

•	Title: The title band will be printed on the first page of the report as a report title.

•	Page header: The page header is printed at the top of each report page

•	Column header: The column header is used with reports that have multiple columns (can be set with "Report|Options").

•	Detail: The detail band is the most important report band. It is printed once for each data record from the main report table.

•	Group footer: The group footer is printed at the end of a group, before a new group starts. You can find more information on groups at the chapter describing the group band.

•	Summary: The summary band is printed at the end of the report, after all data records have been printed.

•	Page footer: The page footer is printed at the bottom of each report page.

The band properties for these bands are all set with the same property dialog. The following options are available:

Type: Band type (see above)

Color:

This defines the background color of the report band.

Print

•	not on first page: prints the band only on the second page and following

•	not on last page: suppresses printing of the band on the last report page

•	even page numbers only: only prints the band on even page numbers

•	odd page numbers only: only prints the band on odd page numbers

•	at bottom of page: moves the band to the bottom of the page before printing it

•	force new page: starts a new page before printing the band

•	force new column: starts a new column before printing when using reports with multiple columns

Only print band if expression is true:

An expression can be used to determine whether the band should be printed or not. The expression must have a logical result of "true" or "false". Press the button on the right side of the expression edit line to use the expression expert.

Frame:

A frame or single frame lines can be drawn around the report band. You can select which lines should be drawn, in which color, style and width.


Subdetailband

A subdetail band is a sort of detail band, but it is subordinate to the real detail band. For example if you want to print a list of audio CDs, and for each CD a title list should be printed, you would use a detail band for printing the CD names and connect a subdetail band to the titles dataset, so separate lists of titles are printed below each CD name. Please note that in order to use such a connection you must create a corresponding table connection (called "master-detail-relationship") as well by using the database setup.

The following options are available for the subdetail band:

Table:

This is the dataset that is used by the subdetail band.

Color:

This defines the background color of the report band.

Master:

The master is the table superior to the subdetail table. For each record in the master table a subdetail list is printed.

Only print if expression is true: see  Standard bands

Print

•	before master-detailband: prints the subdetail records before printing their master record from the detail band

•	at bottom of page: moves the band to the bottom of the page before printing it

•	print header/footer even if dataset is empty: header and footer bands of the subdetail band will be printed even if there are no subdetail data records to print

•	force new page: starts a new page before printing the band

•	force new column: starts a new column before printing the band (when using reports with multiple columns)

Headerband:

This band is printed as a header before the subdetail data records are printed.

Footerband:

This band is printed as a footer after the subdetail data records have been printed.

Frame: see Standard bands


Groupband

A group band is used to structure data into groups of data records. For example if you want to print a list of addresses, you can add some space before each new first letter starts and insert some caption, or you can group addresses by city or state.

Example

Internally groups are handled the following way: Before printing a data record, the report engine checks if the expression result of the group band is different from the last data record's expression result. If this is the case, the group band will be printed, else it won't. This way data can be grouped with great flexibility, because you can use the expression for nearly any kind of calculation.

The following options are available:

Master:

Defines which is the master band for the group. This can be either the detail band or a subdetail band. The group is checked each time the master band is printed.

Color:

This defines the background color of the report band.

Expression:

This expression determines whether a group band is printed or not. The band will be printed each time the result of the expression changes.

Print

•	at bottom of page: moves the band to the bottom of the page before printing it

•	force new page: starts a new page before printing the band

•	force new column: starts a new column before printing when using reports with multiple columns

Footerband:

This band is printed at the end of each group, before the next group band is printed.

Frame: see Standard bands


Groupband example
The following picture shows a report with a group band (the bold printed single character is located on the group band) and a report without a group band on the right side.


Childband

A childband is a band which is attached to another report band and which will always be printed below its parent band. This way you can print an additional band after each detail band for example. The advantage of using child bands instead of just resizing the parent band to make room for more report elements is that there can be a page break between a band and its child if needed, and the child band can be printed independently from its parent band, even if the parent band is not printed.

You must use child bands if you have report elements with "Autostretch" set to true and you want to print element below such auto-resizing fields. In this case place all the elements which should be moved down automatically because of autostretching elements before them on a child band.

The following options are available:

Parent Band: The band to which the child band is attached

Color:

This defines the background color of the report band.

Print

•	not on first page: prints the band only on the second page and following

•	even page numbers only: only prints the band on even page numbers

•	odd page numbers only: only prints the band on odd page numbers

•	at bottom of page: moves the band to the bottom of the page before printing it

•	force new page: starts a new page before printing the band

•	force new column: starts a new column before printing when using reports with multiple columns

Only print band if expression is true:

An expression can be used to determine whether the band should be printed or not. The expression must have a result of "true" or "false". Press the button on the right side of the expression edit line to use the expression expert.

Frame:

A frame or single frame lines can be drawn around the report band. You can select which lines should be drawn, in which color, style and width.


Datafield

A datafield displays data from a table or query. This can be numerical data, characters or multiple lines of text.

The following options are available:

Data field:

Select the data field you want to print.

Alignment:

The report element's text can be aligned left, right or centered.

Format:

Numerical data fields can be formatted by setting a format definition.

Font:

Selects the font for the report element

Color:

Defines the background color for the report element (the font color can be set with the "Font" button).

Automatic word-wrap:

Set this option if you want the text to wrap to the next line if it does not fit into the given width (similar to a word processor software).

Transparent:

The report element's background will not be printed if this option is activated. This way you can place text on top of an image for example, without hiding the image under a white box with text.

Autosize width:

This option resizes the report element to make room for its complete text. If "Autosize" is not set and the text is larger than the element size then text will be clipped off.

Autostretch height:

This option is for report elements with multiple lines, e.g. memo fields. The height of the elements will be stretched to make room for all lines. If needed, the report band will be stretched too.

Suppress printing of repeated values

Activate this option if you do not want  to print data fields with the same values repeatedly. The report engine will only print the data field for the first data record, and leave all following prints of this data field empty if the field's content hasn't changed.

Suppress printing if value of datafield is 0

This option suppresses printing of numerical fields if their value equals zero.

Reprint on new page

If you have activated "Suppress printing of repeated values", you can use this option to reprint a field if a new page starts, even if it would be suppressed normally because of equal values.

Reprint on new group

Same as "Reprint on new page" but for groups.


Label

A label is for printing static text, i.e. text that is printed exactly like it is displayed during report design.

The following options are available:

Text:

This is the text that should be printed. You can only type in one line of text. Memos can be used for multi-line text.

Rotation:

Use this to rotate your text. Rotation can be set to anything between 0 and 360 degrees. 90 degree means displaying the text vertically for example.

Font:

Selects the font for the report element

Color:

Defines the background color for the report element (the font color can be set with the "Font" button).

Alignment:

The report element's text can be aligned left, right or centered.

Automatic word-wrap:

Set this option if you want the text to wrap to the next line if it does not fit into the given width (similar to a word processor software).

Transparent:

The report element's background will not be printed if this option is activated. This way you can place text on top of an image for example, without hiding the image under a white box with text.

Autosize width:

This option resizes the report element to make room for its complete text. If "Autosize" is not set and the text is larger than the element size, text will be clipped off.

Autostretch height:

This option is for report elements with multiple lines, e.g. memo fields. The height of the elements will be stretched to make room for all lines. If needed, the report band will be stretched too.


Memo

A memo is used to print text that has more than one line. Just like a label a memo displays text in one color and font. If you want to use formatted text, you can use a Richtext field.

The following options are available:

Text:

This is the text that will be printed.

Font:

Selects the font for the report element.

Color:

Defines the background color for the report element (the font color can be set with the "Font" button).

Alignment:

The report element's text can be aligned left, right or centered.

Automatic word-wrap:

Set this option if you want the text to wrap to the next line if it does not fit into the given width (similar to a word processor software).

Autosize width:

This option resizes the report element to make room for its complete text. If "Autosize" is not set and the text is larger than the element size, text will be clipped off.

Autostretch height:

This option is for report elements with multiple lines, e.g. memo fields. The height of the elements will be stretched to make room for all lines. If needed, the report band will be stretched too.


Image

An image element is a bitmap from a BMP file that is inserted into the report.


Image from data field

While a datafield is for displaying text from a database record, you can use this report element to display images that are stored in a database.

The following options are available:

Datafield:

The field that contains the image (if there is no bitmap in this field, nothing will be printed). If the datafield property is set to a database field which is not of type "bitmap", the report engine will try to find a bitmap file whose name equals that of the data field's content and load it.

Stretch picture automatically:

Activate this option to stretch the picture so it fits exactly into the report element's size, else the picture will be cut off if there is not enough room, or space will be left empty if there is too much.

Center picture:

If the report element is bigger than the picture and the "Stretch" option is not activated, the picture will be centered in the report element instead of being placed in the top left corner.

Alignment:

Aligns the image on the report band.


Expression field

A calculated field (expression field) is used for displaying text or data that is calculated by an expression or formula. You can do numerical calculations, string manipulations, concatenate data fields and much more.

Expression:

This expression is evaluated each time the calculated field is going to be printed (see the chapter on expression syntax).

Format:

Numerical fields can be formatted by setting a format definition.

Font:

Selects the font for the report element

Color:

Defines the background color for the report element (the font color can be set with the "Font" button).

Master:

If your expression uses any aggregated functions, you must link the "Master" property to the dataset that will be used to update the expression. Each time a new data record from this dataset is selected the expression will be recalculated.

Alignment:

The report element's text can be aligned left, right or centered.

Rotation:

Use this to rotate your text. Rotation can be set to anything between 0 and 360 degrees. 90 degree means displaying the text vertically for example.

Autosize width:

This option resizes the report element to make room for its complete text. If "Autosize" is not set and the text is larger than the element size, text will be clipped off.

Autostretch height:

This option is for report elements with multiple lines, e.g. memo fields. The height of the elements will be stretched to make room for all lines. If needed, the report band will be stretched too.

Reset after print:

If you are using calculations like sums or counting of data record etc., you can use this option to reset the value of the calculation to zero after the report element has been printed.

Automatic word-wrap:

Set this option if you want the text to wrap to the next line if it does not fit into the given width (similar to a word processor software).

Transparent:

The report element's background will not be printed if this option is activated. This way you can place text on top of an image for example, without hiding the image under a white box with text.


Aggregated function
An aggregated function is a function which calculates a value over multiple data record and each record is used for the result, for example "Sum" or  "Average".


Shape

This report element can be used to draw (horizontal or vertical) lines, circles and rectangles.

The following options are available:

Type:

Selects the shape, which can be a circle, rectangle, horizontal line, vertical line or top/bottom and left/right lines.

Brush:

•	Style: different styles to fill a circle or rectangle

•	Color: the color that is used to fill the shape

Pen:

•	Width: line width of the shape

•	Mode: various line drawing modes

•	Style: solid, dotted or dashed lines


Systemfield

This report element is used to display various system data like current time or date, page number etc.

Available options:

Text:

This text will be displayed before the actual system data. For example you can use the text "Report printed on: " when printing the current date.

Type:

The type of system data to print. The following types are available:

•	Date: the current date when printing the report

•	Time: the current time when printing the report

•	Date/Time: the current date and time

•	Detail count: total number of data records

•	Detail number: number of the current data record

•	Page number: current page number of the printout

•	Report title: the report title which can be set via "Report|Options"

Autosize width:

This option resizes the report element to make room for its complete text. If "Autosize" is not set and the text is larger than the element size it will be clipped off.

Transparent:

The report element's background will not be printed if this option is activated. This way you can place text on top of an image for example, without hiding the image under a white box with text.

Font:

Selects the font for the report element

Color:

Defines the background color for the report element (the font color can be set with the "Font" button).


Richtext field

Note: Richtext is only available with 32 bit applications, i.e. applications for Windows 95 and Windows NT.

The richtext element is a report element that can display multi-line text with different fonts, colors and formatting. In the richtext property dialog press the "Edit" button to show a text editor with richtext editing capabilities.

Alignment:

sets the text alignment if no alignment has been set with the editor

Font:

sets the font if no font has been specified with the editor

Color:

sets the text color if no color has been set with the editor

Autostretch height:

This option is for report elements with multiple lines, e.g. memo fields. The height of the elements will be stretched to make room for all lines. If needed, the report band will be stretched too.


Richtext from data field

Note: Richtext is only available with 32 bit applications, i.e. applications for Windows 95 and Windows NT.

This report element displays richtext from a data field. The following options are available:

Data field:

Select the data field to use by the report element.

Alignment:

sets the text alignment if no alignment has been set with the editor

Font:

sets the font if no font has been specified with the editor

Color:

sets the text color if no color has been set with the editor

Autostretch height:

This option is for report elements with multiple lines, e.g. memo fields. The height of the elements will be stretched to make room for all lines. If needed, the report band will be stretched too.


Chart

Note: The chart element is not available in all report editor versions. Please ask your software developer.

The chart element can be used to display various types of graphs and charts. There are many options available, which would be too much to describe here. Please ask your software developer for more information.


Formatting of fields

Numerical fields and date or time fields can be displayed in many different ways. This can be controlled with format strings, which represent the output formatting in a symbolical way.

numerical fields:

Specifier	Represents
0	Digit place holder. If the value being formatted has a digit in the position where the '0' appears in the format string, that digit is copied to the output string. Otherwise, a '0' is stored in that position in the output string.
#	Digit placeholder. If the value being formatted has a digit in the position where the '#' appears in the format string, that digit is copied to the output string. Otherwise, nothing is stored in that position in the output string.
.	Decimal point. The first '.' character in the format string determines the location of the decimal separator in the formatted value; any additional '.' characters are ignored. The actual character used as the decimal separator in the output string is specified in the Number Format of the International section in the Windows Control Panel.
,	Thousand separator. If the format string contains one or more ',' characters, the output will have thousand separators inserted between each group of three digits to the left of the decimal point. The placement and number of ',' characters in the format string does not affect the output, except to indicate that thousand separators are wanted. The actual character used as the thousand separator in the output is specified in the Number Format of the International section in the Windows Control Panel.
E+	Scientific notation. If any of the strings 'E+', 'E-', 'e+', or 'e-' are contained in the format string, the number is formatted using scientific notation. A group of up to four '0' characters can immediately follow the 'E+', 'E-', 'e+', or 'e-' to determine the minimum number of digits in the exponent. The 'E+' and 'e+' formats cause a plus sign to be output for positive exponents and a minus sign to be output for negative exponents. The 'E-' and 'e-' formats output a sign character only for negative exponents.
'xx'/"xx'	Characters enclosed in single or double quotes are output as-is, and do not affect formatting.
;	Separates sections for positive, negative, and zero numbers in the format string.

The locations of the leftmost '0' before the decimal point in the format string and the rightmost '0' after the decimal point in the format string determine the range of digits that are always present in the output string.

The number being formatted is always rounded to as many decimal places as there are digit placeholders ('0' or '#') to the right of the decimal point. If the format string contains no decimal point, the value being formatted is rounded to the nearest whole number.

If the number being formatted has more digits to the left of the decimal separator than there are digit placeholders to the left of the '.' character in the format string, the extra digits are output before the first digit placeholder.

To allow different formats for positive, negative, and zero values, the format string can contain between one and three sections separated by semicolons.

•	One section: The format string applies to all values.

•	Two sections: The first section applies to positive values and zeros, and the second section applies to negative values.

•	Three sections: The first section applies to positive values, the second applies to negative values, and the third applies to zeros.

If the section for negative values or the section for zero values is empty, that is if there is nothing between the semicolons that delimit the section, the section for positive values is used instead.

If the section for positive values is empty, or if the entire format string is empty, the value is formatted using general floating-point formatting with 15 significant digits. General floating-point formatting is also used if the value has more than 18 digits to the left of the decimal point and the format string does not specify scientific notation.

Date/Time:

The following formatting options are available for date and time:

Specifier	Displays
c	Displays the date using the format given by Windows' default short date format , followed by the time using the format given by the default Windows long time format. The time is not displayed if the fractional part of the DateTime value is zero.
d	Displays the day as a number without a leading zero (1-31).
dd	Displays the day as a number with a leading zero (01-31).
ddd	Displays the day as an abbreviation (Sun-Sat).
dddd	Displays the day as a full name (Sunday-Saturday).
ddddd	Displays the date in short format
dddddd	Displays the date in long format.
m	Displays the month as a number without a leading zero (1-12). If the m specifier immediately follows an h or hh specifier, the minute rather than the month is displayed.
mm	Displays the month as a number with a leading zero (01-12). If the mm specifier immediately follows an h or hh specifier, the minute rather than the month is displayed.
mmm	Displays the month as an abbreviation (Jan-Dec).
mmmm	Displays the month as a full name (January-December).
yy	Displays the year as a two-digit number (00-99).
yyy	Displays the year as a four-digit number (0000-9999).
h	Displays the hour without a leading zero (0-23).
hh	Displays the hour with a leading zero (00-23).
n	Displays the minute without a leading zero (0-59).
nn	Displays the minute with a leading zero (00-59).
s	Displays the second without a leading zero (0-59).
ss	Displays the second with a leading zero (00-59).
t	Displays the time using in short format.
tt	Displays the time in long format.
am/pm	Uses the 12-hour clock for the preceding h or hh specifier, and displays 'am' for any hour before noon, and 'pm' for any hour after noon. The am/pm specifier can use lower, upper, or mixed case, and the result is displayed accordingly.
a/p	Uses the 12-hour clock for the preceding h or hh specifier, and displays 'a' for any hour before noon, and 'p' for any hour after noon. The a/p specifier can use lower, upper, or mixed case, and the result is displayed accordingly.
ampm	Uses the 12-hour clock for the preceding h or hh specifier, and displays the contents of the TimeAMString global variable for any hour before noon, and the contents of the TimePMString global variable for any hour after noon.
/	Displays the date separator character given by the Windows country settings.
:	Displays the time separator character given by the Windows country settings.
'xx'/"xx"	Characters enclosed in single or double quotes are displayed as-is, and do not affect formatting.

Format specifiers may be written in upper case as well as in lower case letters--both produce the same result.


Expression syntax

Expressions are used for calculating numerical values, strings or logical values ("true" or "false"). There are various operators and functions available for this, comparable to a programming language ("Pascal" in this case), which can be used to do many kinds of calculations. You can do operations with data fields, for example calculate the tax for a given amount, or merge two data fields into one string. Please note that if the expression is a property of a report band, the result of the expression must be a logical value. Only the group band expects the result to be a string or a number, just like the expression-field report-element.

Composing expressions

The expression evaluator is working with four data types: Strings, Integer, Float and Boolean. Binary data and memo fields are not supported in expressions. Below is a list of how database fields are converted to report data types:

Data type	Field type
String	String fields, date and time fields
Integer	SmallInt fields, byte fields, integer fields
Float	Float fields, currency fields
Boolean	Boolean (logical) fields

Report expression syntax is very much like Object Pascal. Below is a list of supported operators:

Operator	Description
+	Add
-	Subtract
*	Multiply
/	Divide
()	Parentheses
And	Logical AND
Or	Logical OR
Not	Logical NOT
=	Equal
<	Less than
>	Greater than
<=	Less than or equal
>=	Greater than or equal
<>	Not equal

The standard functions included are:

Function	Description
Date	Return current date as a string
Time	Return current time as a string
Str(Number)	Converts the numeric argument to a string
Copy(Str,s,l)	Returns a substring of str (starting from the s-th character, returning a maximum of l characters)
Int(Number)	Returns the integer part of a number
Frac(Number)	Returns the fractional part of a number
If (Expr, r1, r2)	Returns r1 or r2 depending on the boolean expr
TypeOf(Expr)	Returns the data type of expr
Sqrt(Number)	Returns the square root of a number
True	Logical value True
False	Logical value False
Sum(Expr)	Returns the sum of expr
Count	Returns the number of entires
Min(Expr)	Returns the lowest value of expr
Max(Expr)	Returns the highest value of expr
Average(Expr)	Averages the expr

Using database fields in expressions

Any field in any table referenced in your report can be accessed in an expression. Field names can be referenced either just by the field name itself (e.g. Name) or by the table name followed by a dot and the field name (e.g. Customers.Name). If you do not specify a table name the report engine will search for the field in all available data sets and use the first instance found.

The current version of the expression evaluator does not support field names with embedded special characters like blank, "/", dot, dollar sign and so on.

Using Strings in Expressions

Strings in expression should be put in single quotes. The following is a valid expression:

"Computers are great!"

Maximum string length is 255 characters.

Expression Examples

Below are some examples of expressions:

Expression	Description
1	Integer constant, returns 1
1.5	Floating point constant, returns 1.5
"Delphi"	String constant, returns "Delphi" as a string
True	Logical constant, returns True
1 + 2	Numeric calculation, returns 3
2 * (3 + 2.5)	Numeric calculation, returns 11
"Delphi" + " is great"	String calculation, returns "Delphi is great"
Name	Returns the value of the field Name if it exists
Customer.Name	Returns the value of the field Name in the Customer table
Name + " " + Contact	Adds the Name field, a blank and the Contact field
AmountPaid * TaxRate / 100	Numeric field calculation
"Printed "+ Date	String calculation
"Total amount paid is " + str(AmountPaid)	String calculation
if(AmountPaid > 5000, "Large order", "Small order")	Returns "Large order" if AmountPaid is greater than 5000, else "Small order"
if(CheckField, "X", " ")	Prints an X if CheckField is True

Compose expressions visually

Expressions can be typed in directly to the corresponding edit line, or you can press the button ".." to the right of the edit line to show the expression builder dialog. You can then visually design your expression with your mouse. If a used function has parameters, like "Copy" or "Sum", another copy of the dialog will be opened to define those parameters separately.

.


Using parameters in SQL statements

If you are accustomed to SQL,  you know that a SQL statement can have parameters. The report designer allows using parameters too. The following example shows such a SQL query, which has a parameter "CustomerNo" (preceded by a ":" as usual):

You can now press the "Parameter" button to open the following dialog:

Here you can define the type of the parameter:

Value: The supplied value is used for the parameter when generating the report.

Ask for value before print: Use this option to make the report engine ask for the parameter value each time before the report is printed. A dialog with an input line for the parameter value will be shown. This way the report can be filtered individually before printing for example.

Assign value from master dataset: This option is for retrieving the parameter value from another dataset, which means the parameter name corresponds to the field name in another table or query. In the current query's property dialog you must set the "Master" property to the master dataset.


Menu item "View|Options"

Show bandruler

Activate this option to show grid lines when editing the report. These lines make it easier to move and align report elements.

Unit

This is the unit that the report designer uses for measuring and drawing of the ruler and grid lines. Millimeter, Inch and Characters are available.

Show band names

This option is for showing the band name on the band in design mode. The band names are not printed of course.

Font

The font to display the band name.

Grid size

This sets the (invisible) grid to which report elements are aligned automatically. You can use it for easier alignment of report elements to the same horizontal or vertical position. Note: You can move a report element by one pixel even if a bigger grid size is set if you hold down the [Shift] key while moving the element.

Keyboard grid size

Same as "Grid size", but for keyboard movement with the cursor keys instead of the mouse.

Show component frames at design time:

If this option is activated frames will be drawn around each report element at design time. This way report elements can be displayed more distinctly. The frames are only drawn in design mode, not during printout.


Menu item "Report|Options"

Report title

This is the report's title, which appear in Windows' print manager and which can be used in the report via a system field.

Portrait/Landscape

This option defines the page layout. The report designer's size will change accordingly.

Font

This font is used as the default font for all new report elements and for elements for which no individual font has been set.

Paper size

Defines the report's paper size. Please note that not all printer drivers support all paper sizes.

Height, Width

Set these sizes if you are using the "custom" paper size.

Margins (Top, Bottom, Left, Right)

These are the paper margins. Note that the minimum values depend on your printer hardware.

Column space

This is the empty space between columns if you are using a multi-column report.

Number of columns

This is the number of columns used for the report.

Frame

This frame will be drawn around each printed page.


Questions & Answers, Tips & Tricks

How can I print the current selection criteria (i.e. query parameters)?

Add an expression component to your report and use its "QueryParam" function, e.g. QueryParam('CustomerQuery','StartDate').

When I preview a report I don't see all horizontal lines as they are on the report layout. Why?

The horizontal lines are still there and will be printed, but the report preview must scale the report output down to make it fit to the screen. Because of this scaling, thin horizontal lines may disappear.

How can I print only one data record?

Leave the main report dataset empty in "Report|Datasets...".

How can I use Inches instead of MM in the report editor?

This can be set with the menu item "View|Options".


License- and copyright information

The report designer ("QRDesign") and this file are used under license and are Copyright 2008 by

QBS Software Limited
7 Wharfside, Rosemont Road
Wembley, Middlesex HA0 4QB

Email: sales@qbssoftware.com

Web: http://www.qbssoftware.com

QRDesign is currently sold as part of Quickreport 5 Professional

Web: http://www.quickreport.co.uk