Forms & Reports

DbSchema integrates a very powerful and fully customizable Forms & Reports Engine. It can be used for:

Create a Form or Report using the Wizard

You can open the Forms & Reports engine either from the top menu How to generate a database report or by right-clicking on a table header.

There are 4 main ways of creating a form:

  1. Using data from a table;
  2. Build a query using Visual Query Builder and create the report from the result;
  3. Write a query using SQL Editor and create the report from the result;
  4. Create the report from scratch.

The first 3 ways are straightforward and intuitive, and the last one will be explained thoroughly in a standalone chapter.

After we open the Forms & Reports, the first window will open:

New Database Report Window
  1. Choose a name for the report
  2. Select the data source
  3. Choose the orientation. A report can contain multiple records per page or just one.

The next dialog is for selecting what columns will appear in the report

Form and Report Designer Component Dialog

Next, the report will open in a new browser window and will look like this:

Form and Report Designer

Designing a Report from Scratch

To start a design from scratch, choose the Empty option in the first dialog box. The reports engine is very intuitive and works with Panels, Tables, and Cells.

After choosing the Empty option, the designer will open at the bottom of the screen with 4 empty panels. Clicking on any panel will open the component menu from which you can any option.

Database Form and Reports Tables & Cells

To add a new column or row, right click on an existing cell. Create a new component by clicking on an empty cell.

New Form or Report Component

To merge cells, select the primary cell, then hold shift and select the rest.
To move the component of a cell to another one, simply drag & drop it.

Merge Form and Report Cells

Clicking on a cell with a component will open the editor. In the image bellow, we edit a Label component. The Label can contain variables ${column} that will be replaced with the variable data at the runtime.

Form or Report Label Editor

The Data Source from a table can be expressed with an SQL query, Groovy/ Java script or with a visual query. This can be selected from the edit menu of the table/panel. To enter de edit menu, click on the table/panel header.
In this example, the data source is a SELECT query.

Note! After writing the script, you have to run it.

Edit form or report Data Source

From here you can set a header and footer to the table or modify the look with CSS syntax.

Scripts

As you can see in the above image, the designer can run 3 types of scripts.

1. Data Source Script
The most important form script. This can be a simple SQL query as the following: 

SELECT city_id, city, country_id, inhabitants, last_update from city

The data source will create variables like ${city_id}, ${country_id}, ${country_id}, ${inhabitants}, ${last_update} which will be available inside the current pane or table and inside all children panels and tables. We can create then a label with text:

The city is ${inhabitants<1000?'small':'large'}. It has ${inhabitants} people.

The label text will be evaluated, the ${..} will be evaluated as Java expression and will generate this:
The city is small. It has 800 people.

2. Initialization Scripts
This can be a Groovy script that will be executed when the report is open. It can be used to implement some initial logic for the form, authentication, etc.

3. After Each Row Script
This can be a Groovy script used to compute totals, average, etc. over the data delivered by the data source.

Master-Detail Relations

Panels or tables can be embedded. The master shown below is using the data source country_id=${country_id} where the variable ${..} is provided by the master table.

Master Detail Database Report

Input Variables

Besides labels, forms may contain also text fields, buttons, radio buttons, charts, etc. Buttons can open other form pages and send data as variables. In the target form the variables are received as input variables.
Add a new input variable by clicking the form title in the designer.

Database Report Input Variable

The variable can be edited. Choose a name, a data type, default value and an error message in case the variable should be mandatory and it's left empty.

In the next image there is a form with two labels, input text fields and a button. The button has an action set to open the DbSchema homepage when clicked. This will automatically send the variables email and password.

Create button in a database form

Deploy Tomcat WAR

The forms applications can be deployed as Tomcat WAR applications. You can export the created application under any tomcat instance. For this you will need: