The Reports Designer

Index

Introduction

Reports Designer is a DbSchema tool featuring different types of charts and customizable database reports. The Report sheets can interact with one another.

Open the Sample Project

In the next slides, you will see examples created using the DbSchema sample project, available in the first DbSchema screen.
DbSchema Sample Project

The Reports Wizard

The Reports Designer can be started from the Tools menu or by right-clicking any table header. Here I used the 'country' table from the sample project.
Analyst BI Wizard

The Designer Structure

The designer contains the main panel 'Panel'. This contains cells. Inside any cell, we can place other panels or tables, with their own cells, etc. Like this, we can build a hierarchy of tables and panels. Different panels and tables are using different colors (green, blue, etc.).

Analyst BI Panel Hierarchy.

Our report contains a panel and a table. The table is placed in one of the panel cells. This cell is merged on the right with the other cells from the same row. Empty cells are marked with a solid line, and cells having a component with a dotted line.

Analyst BI Report.

Table Properties

We can edit the table properties and the data source by clicking the blue label 'Table'.
Edit table properties.

Insert Columns or Rows and Merge Cells

Right-clicking the chart cell you can add other rows or columns. You can select multiple cells and merge them.

Add column or row

Create New Components

Empty cells are marked with a solid line. Click one of them and the Component Palette will show on the right. Here you can choose new components to be created: charts, labels (text), buttons, text fields, combo boxes, radio buttons, etc.
Create new component.

Create a Report

Similar to a chart, you can create reports. This report was created using the 'offices' table wizard.

Analyst BI report.

Interaction Between Sheets

A sheet may access another sheet using buttons.
Sheet interaction using buttons.
Configure the sheet to open when the button is pressed and the variables to pass over to the target sheet.
Button properties.

Sheet Properties

Sheets can be open as dialogs. They also have an initialization script that can be used, for example, to modify data in the database. The input variables are used to make sure some variables are received from the caller sheet. If a variable is missing, you can configure an exception to be thrown.
Sheet properties.

Create a Master-Detail Report

From the wizard you can create master-detail reports. In the image below, we list offices with their employees.

Master-Detail database report.

As we can see, there are two tables imbricated one in another.

Master-Detail database report.

The first table data source query is:

SELECT * FROM "PUBLIC".offices

And the second table data source query is:

select * from employees where `officeCode`='${officeCode}'
The queries are parsed using Groovy templates, where the ${...} and <%....%> are considered Java expressions. These expressions are receiving the data from the parent containers (tables, panels) data source queries as variables. The officeCode variable is generated by the first table data source query and is used in the WHERE clause of the child table data source query. Notice the quotes: '${officeCode}', as this is a string in the query.

How to Create a Chart

From the Wizard you can choose to create a chart. The chart needs to be configured and is not yet visible. Click the red cell in the designer to configure the chart.

Configure chart in the Analyst BI Designer

Configure Chart Axis

Configure the chart axis variables. We can drag the name and population variables to the axis values.
Configure chart axis

Last Changes

Here I changed the chart type to 'Pie' and I edited the data source script to order the countries by population, with a limit of 20 countries. Press 'Apply' and you should see the chart below.

Configure chart axis

The data source looks like this:

Chart data source

View Chart in System Browser

From the menu, you can choose to view the chart in the system browser. Also, you can press the 'Design' button to leave the design mode.
View chart in the system browser.

Deploy Tomcat WAR

The Analyst BI sheets can be deployed as Tomcat WAR applications and can run independently. You can export the created application under any tomcat instance.

  • Download and install Tomcat. The installation file is usually a zip file that you have to unpack. Go to the 'bin' folder and execute 'startup.bat'. This will start the tomcat server. This may request to set the JAVA_HOME variable pointing to your current Java installation folder. Do this by pressing the 'Win' key ( with the Windows logo ) + Break, then 'Change settings' / 'System properties' / 'Environment Variables'. After setting JAVA_HOME you will have to close and reopen the command prompt and execute again 'startup.bat'.
  • Edit the tomcat-users.xml file from the tomcat conf folder.
    <role rolename="tomcat"/>
    <user username="tomcat" password="tomcat" roles="manager-gui"/>
            
  • Restart the Tomcat server.
  • Login on 'http://localhost:8080' and follow the link 'Manage App'. There you can upload the generated war file.
  • Browse to 'http://localhost:8080/war_file_name'