Download page Generating reports using BigPicture database tables.
Generating reports using BigPicture database tables
There are critical differences between different versions of the product. Keep in mind, depending on the version you are using you may need to modify steps listed below. Steps described below are applicable to version 8.0.X of BigPicture.
Please note that we do not recommend basing the automation of your business-critical processes in this way, simply because the structure of our plugin's database changes from release to release and your automation probably will not work with the next version of the plugin (it will need to further modified).
Keep in mind:
- Depending on the state of synchronization, data contained in the tables may not be up to date.
- Size of the tables may cause script performance issues - 'join' function is not an optimal way for handling large quantities of data.
- table names may vary depending on the version of application (go to Data Tables section of this article for more information)
Keep in mind, you may need to make adjustments as needed.
--- Field names select def."NAME" from "AO_0456E7_T_CORE_FIELD_DEF" def where "VISIBILITY" = 'GLOBAL';--- Template to get tasks with fields with global_fields as ( select val."VALUE", val."TASK_ID", def."NAME" from "AO_0456E7_T_CORE_FIELD_VAL_2" val join "AO_0456E7_T_CORE_FIELD_DEF" def on val."TASK_FIELD_DEF_ID" = def."ID" where "VISIBILITY" = 'GLOBAL' ) select split_part(f1."VALUE", '@@', 2) task_key, coalesce(resource.resource_name, '-Unassigned-') resource_name, f2."VALUE" start_date, f3."VALUE" end_date -- f4."VALUE" ---next-field-alias--- -- f5."VALUE" ---next-field-alias--- -- ... from "AO_0456E7_T_CORE_TASK_2" task left join (select field_val."TASK_ID", coalesce(resource."NAME", 'Unassigned') resource_name from "AO_0456E7_T_CORE_FIELD_VAL_2" field_val left join "AO_0456E7_T_CORE_FIELD_DEF" field_def on field_def."ID" = field_val."TASK_FIELD_DEF_ID" left join "AO_0456E7_RESOURCE_2" resource on 'EXT_USER@' || field_val."VALUE" = resource."KEY" where field_def."VISIBILITY" = 'GLOBAL' and field_def."NAME" = 'Assignee' ) resource on task."SURROGATE_ID" = resource."TASK_ID" left join global_fields f1 on f1."TASK_ID" = task."SURROGATE_ID" and f1."NAME" = 'Key' left join global_fields f2 on f2."TASK_ID" = task."SURROGATE_ID" and f2."NAME" = 'Start Date' left join global_fields f3 on f3."TASK_ID" = task."SURROGATE_ID" and f3."NAME" = 'End Date' --- left join global_fields f4 on f4."TASK_ID" = task."SURROGATE_ID" and f4."NAME" = '--- global field name name ---' --- left join global_fields f5 on f5."TASK_ID" = task."SURROGATE_ID" and f5."NAME" = '--- next global field name name ---'-- ... -- where -- ...
Task Key Format
The above will work when task keys in the table match the following format - 2@1@BPERF5-3398@@BPERF5-3398.
In this case, the resulting report file will return the key simply as - BPERF5-3398, and the report will be generated properly.
To use the template, modify the field names as needed to include more columns in the results. Keep in mind, if a given field doesn't have a value, it will be empty (this means, you could potentially end up with an empty column)
The column on the left will always contain the task key.
The next column is the resource name.
Any additional columns will display values of fields you specify.
In the example above, if you execute the SQL with values:
- f2 → start date
- f3 → end date
You will get the following result.
Keep in mind, data table names may vary. Make sure to adjust them accordingly.
Depending on the version of the application, you might encounter an old version of the table (that is no longer being updated) + a new table that contains current data.
Four tables have been used in the template:
For versions 8.0.0 - 8.0.18 you should find the following two tables:
For versions 8.0.19+ the same data will be in tables with a suffix:
In case of any problems
If you are still having trouble following these steps, or your problem is more complicated, you are always welcome to contact our Support Team via the Service Desk. We are always more than happy to help.