Introduction
The Report Search Datalist Binder plugin is a database Binder Using JDBC with support for search filters before performing aggregate and pivot.
JDBC Datalist Database Binder (Report) Properties
Configure Database SQL Query
Figure 1: Configure JDBC Datalist Database Binder (Report)
| Name | Description |
|---|---|
| Datasource |
By selecting Default Datasource, the database your Joget is currently using will be selected. |
| Custom JDBC Driver | Custom JDBC Driver. This field is required when Custom Datasource is selected in Datasource above. Example: |
Custom JDBC URL | Custom JDBC URL. This field is required when Custom Datasource is selected in Datasource above. Example: |
Custom JDBC Username | Custom JDBC Username. This field is required when Custom Datasource is selected in Datasource above. |
Custom JDBC Password | Custom JDBC Password. This field is required when Custom Datasource is selected in Datasource above. Test the connection parameters Click on the "Test Connection" button at the bottom of the page to quickly test out your configurations. |
| SQL SELECT Query * | The SQL query specific to your database type (MySQL, MSSQL, Oracle, etc.). Use question mark ? in your query to represent the primary key or foreign key. Example SELECT * FROM app_fd_table WHERE id = ? If a column name contains reserved keywords, do ensure it is encapsulated properly. For example for MySQL, if the column identifier itself contains a dot symbol ( . ), it should be encapsulated like this: SELECT `myAppName.myColumn` FROM app_fd_myTable; Table & Column Naming
Sample: SELECT * FROM dir_user |
| SQL Select Query (Data) * | Sample: SELECT locale, active, COUNT(active) as `count` FROM #data# GROUP BY active, locale |
| Primary Key * | Primary key of the table. |
| Pivot Data | If checked, Pivot Column Name, Pivot Value Column Name, and Value to Fill Up Empty Cell will be shown. |
| Pivot Column Name * | Multiple columns supported. Separate multiple columns using semicolon. (e.g. year;customer) |
| Pivot Value Column Name * | Multiple columns supported. Separate multiple columns using semicolon. (e.g. outstanding;paid) |
| Value to Fill Up Empty Cell | e.g. 0.00 |