In this section, you will design a report similar to the report built in previous section, the difference is that this report will not contain a map. Furthermore, it will use non-spatial data from database; such that the report will show Parcel information (Parcel Number, Block Number, Area) in addition to parcel’s owners (many owners) that are stored in tabular format in PostGIS database.
Certainly, you can include a map in this report (As in the previous report), but for the scope of this section, a map will not be included.
Start with the report you created in previous example, and the first step will be adding fields from owners table in database, so start with connecting the report to database by clicking on Report Datasources button as shown below.
Click on “New” in the dialog that appears as shown below.
Select Database JDBC connection and click Next.
Enter connection Parameters:
- Name: Connection Name (Any name can be used)
- JDBC Driver: Driver for target database; in this example PostgreSQL (org.postgresql.Driver), since the data is in PostgreSQL database.
- JDBC URL: URL to connect to database (contains host name, database name, and port); in this example, the URL is jdbc:postgresql://localhost:5432/DatabaseName >> jdbc:postgresql://localhost:5432/geogate_DB
- Credentials: username and password for database; In this example the username is postgres, and password is 123
Please refer to PostgreSQL sections for more details about connecting to PostgreSQL database.
The following screenshot shows the database connection parameters.
You can see that the connection is added, so close the dialog as shown below.
Now click on Report Query button to write SQL command that will query owners’ data from Parcels_Owners table in database as shown below.
Write a query that will return Owner ID and Owner Name from Parcels_Owners table, the query should be like this:
select “id”, “OwnerName” from “Parcels_Owners”
This query will return all records from the table. In this example, you need only the data related to certain parcel. So, a condition (Where clause) must be added to the query.
The where clause should guarantee that the data returned should be for the parcel with ParcelNumber parameter (created in previous section), and Block with BlockNumber parameter (created in previous section).
So, start with writing:
And drag ParcelNumber Parameter to the query as shown below.
Also, add another condition on Block Number by writing
Similarly, drag BlockNumber Parameter to the query as shown below.
Now, the query is ready, so Click OK to save it, as shown below.
In the Report Inspector, expand the Fields group; in the fields, you can see the two fields you added from the query on the table (id and OwnerName) as shown below.
Now, start with designing a report using iReport Designer, this includes:
- Deleting the map
- Moving Parcel Attributes to Title band
- Adding Column Header band
- Minimizing Details band height
Start with deleting the map as shown below.
Move the main parcel attributes to the Title band as shown below.
Add Column Header Band as shown below.
Minimize the Column Header Band to 20 points.
Also, minimize detail height from 350 points to 20 points (Since the details height governs the vertical spacing between each row coming from database).
Now drag the fields from Report Inspector to details band as shown below.
The two fields will be added and in the column header band two static text items will be auto-generated for the field header as shown below.
Change the headers style (give it a grey backcolor, bold font), and change their text (change id to Owner ID, and OwnerName to Owner Name). Also, give the fields a bold style, as shown below.
Finally save the report.
In GeoGATE, go to reports templates and click on New Report button.
Type a name for the report (In this example the report name will be Parcel Owners Report), and click on Choose file in order to upload the report you designed as shown below.
Select the report you created.
Click on Get file button, to get the report parameters as shown below.
After getting the report parameters, select the database connection that will be used to connect to database to get tabular data; in this example you will use the connection created before (PostGIS). Also, uncheck User’s Input checkboxes for all parameters, since the parameters will be fetched from features automatically, not entered by user. This is shown in the screenshot below.
Save the report.
As mentioned in the previous section, Printing server will not recognize the report template until it is stopped and started; this is shown below.
So, stop and start the Printing Server in order to recognize the report as shown below.
Now, add the report to Parcels layer, by going to layer settings, and adding the report in the Reports tab, and save it as shown below.
Now, go to map, and identify a parcel feature, and open reports as shown below.
Select the report (Parcel Owners Report), and click run report as shown below.
Download the generated report and open it.