The information entered into DynaForm fields and case variables are stored as serialized strings, which are not easy to access with database queries. Report Tables are designed to export the data from cases either to the rp_<WORKSPACE> database, so that it can be easily accessed by an external applications and standard SQL queries, or wf_<WORKSPACE>. Report Tables also allows case data to be easily queried in DynaForm fields and to be shared with external reporting tools such as Crystal Reports, Jasper Reports and Pentaho Reporting.
It is worth mentioning that each time a new case is run, another record will be added to the table; that record will be updated each time a DynaForm is submitted in the case.
Recommendations while creating Report Tables
From version 2.0.43, validation for reserved words at the time of named fields where added, this will avoid any error or conflict while using Report tables during a case because data might not be saved in the database.
List of MySQL Reserved Words
Do not use the following words as field names for Report Tables:
ALTER, CLOSE, COMMIT, CREATE, DECLARE, DELETE, DROP, FETCH, FUNCTION, GRANT, INDEX, INSERT, OPEN, REVOKE, ROLLBACK, SELECT, SYNONYM, TABLE, UPDATE, VIEW, APP_UID, ROW, PMTABLE
For more information about reserved words please check this documentation
List of PHP Reserved Words
Do not use the following words as field names for Report Tables:
case, catch, cfunction, class, clone, const, continue, declare, default, do, else, elseif, enddeclare, endfor, endforeach, endif, endswitch, endwhile, extends, final, for, foreach, function, global, goto, if, implements, interface, instanceof, private, namespace, new, old_function, or, throw, protected, public, static, switch, xor, try, use, var, while
Creating Report Tables
From version 2.0 interface of Report Tables has changed giving a user-friendly handling to create Report Tables on an easy way. To create a Report Table, click on REPORT TABLES on the DESIGN tab, a window will display, click on the New link at the top of the list.
In the dialog box which appears, define the following properties for the new Report Table:
- Title: A title to identify the Report Table.
- Table Name: A name for the table, which will be created depending on the DB Connection
- Description: A brief description of the Report Table.
- Type: Select what type of DynaForm will be exported to the Report Table:
- Global: Export data entered in the fields in normal DynaForms and/or any case variables defined in triggers. This option allows the individual fields and case variables to be selected for export. If this option is selected for the Type dropdown box, then select which fields and/or case variables defined in triggers will be exported. To select multiple fields, hold down on the CONTROL or SHIFT keys while clicking on the field names and click on > to add the selected fields to the Custom Column, click on >> to add all fields to the Custom Column. If some field added was not the correct click on < to return it back to the list of Dynaform Fileds or click on << to return all back.
Note that grid fields and system variables will not appear in the list.
- Grid: Export data entered in a single grid form. This option will automatically export all the fields in the grid and does not allow individual fields to be selected. If this option is selected then select the name of the grid (which is embedded in a master form) to export, list of grids will display in a dropdown which will display once the Grid type is selected.
- DB Connection: Choose the type of connection where data will be stored:
- Workflow: data will be stored at wf_<WORKSPACE> database.
- REPORT: data will be stored in the rp_<WORKSPACE> database to hold the data for the Report Table.
When done defining the new Report Table, click on Save and the new table will be added to the list of available Report Tables.
A new table will be created depending on the type of DB Connection chose, the table will be renamed with the prefix PMT and the name will be PMT_workflow for the default "workflow" (workspace). When the table is created, it will be automatically populated with data from all the existing cases for the process.
In addition, each case will be identified in the table by their case UID in the APP_UID field and case number in the APP_NUMBER field and from version 2.5 APP_STATUS field to see the current status of the case.
Each time a new case is run, another record will be added to the table. That record will be updated each time a DynaForm is submitted in the case.
Exporting System Variables
System variables, such as @@TASK or @@USER_LOGGED, are not available for export in Report Tables. These values may be useful if needing to do queries based upon a particular user, task, process, etc.
To export a system variable, create a trigger which will assign the value of a system variable to a case variable. For example:
@@caseUserLogged = @@USER_LOGGED; @@caseUserName = @@USR_USERNAME; @%caseIndex = @%INDEX; @@caseTask = @@TASK; @@caseProcess = @@PROCESS;
Then, set the trigger to fire at various points in the process where the Export Table should be updated with new values for the system variables. Remember that the value of system variables change over the course of a case, so the trigger should be fired whenever the new values need to be exported to the Report Table.
So executing the trigger, data will not only be stored in the database but also it will be added on the Data option inside the Report Tables list:
Adding Fields in a Report Table
Usually Report Tables are created from Case Variables, but there is the option to add fields which are not case variables, those fields will be populated, for example, with a trigger. This is very useful if data which are not in a Dynaform have to be managed and stored on the database.
For instance, if we add a new field on the Report Table created:
And add that field as a variable in the trigger created previously
@@CASEUSERINFO = @@caseUserName;
Where the User Name will be obtain. Running a case and checking the database, CASEUSERINFO field was created with respective username of whom runs the case:
If a new field is added to a master DynaForm or its field name is changed, the definition of Report Table will not automatically update to include the new/changed field. The definition of the Report Table will have to be manually updated, to include the new/changed field. For example, using the trigger above, change the
caseTask name to
caseTaskID and also changing its name on the trigger to
@@caseTaskID; then go to REPORT TABLES choose the name of the one created and then click on Edit the modified field will dispaly on the list of Dynaforms Fields as it is taken as a new field:
So the new field has to be added and the old one removed from the list.
If a field is added or changed in a grid form, then the existing Report Table will have to be deleted and then recreated to include the new/changed fields. From recent versions of ProcessMaker it is not necessary to delete and create the Report Table, the new field or the information will be updated automatically on the database.
To delete a Report Table, open the process and click on REPORT TABLES in the top menu. In the list, click on the Delete button for the Report Table to remove it from the list and delete its table in the rp_<WORKSPACE> or wf_<WORKSPACE> database, depending on the DB Connection previously chosen.
Frequently, on a case running modifications on Dynaforms fields are common, however if a Report Table was created based on those fields, changes made during the case won't be reflected on the Report Table created. In this case it is necessary to regenerate it, with that option all cases will be updated and data will be stored on the Report Table again. Go to the list of Report Tables and choose the one to be regenerated by clicking on Data, the data for the specific Report Table will display, then click on Regenerate Data Report option on the upper side of the window
To import a Report Table, go to DESIGNER select the process where the report table will be imported, then click on REPORT TABLES option o the menu, the list of report tables available will display, choose one and click on Import a window will display click on button to select a report table form the computer. Remember that as well as PM tables, Report Tables have the .pmt extension.
Once the Report Table was imported automatically the corresponding table will be created on the wf_<WORKSPACE> or rp_<WORKSPACE> database, depending on the DB connection previously chosen
When importing if the option Overwrite if exist? is not checked and if the Report Table exists a new Report Table will be created with the current Date and Time, for example if The Report table PMT_RPT_CREDIT_CARD was created and the same is imported the Report Table will be created as PMT_RPT_CREDIT_CARD_20120824112519 where the name is divided as:
- Date: 20120824: Year-Month-Day
- Time: Hour-Minute-Seconds
To export a Report Table, go to DESIGNER select the process where the report table will be exported, then click on REPORT TABLES option on the menu, the list of report tables available will display, choose one and click on Export a window will display where there are two options to select at the moment of exporting the Report Table:
- Schema: checking this option Report Table will be exported with the structure of the fields but no data.
- Data: Data export for Report Tables is not allowed, since this data come from Cases and information stored on cases can't be exported.
Remember that as well as PM tables, Report Tables have the .pmt extension.