Generating reports using BigPicture database tables
- Anna Ciepłota
- Piotr Koszela
There are critical differences between different versions of the product. Remember, depending on the version you are using, you may need to modify the steps listed below. The steps described below apply 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 modify further).
Keep in mind:
Depending on the synchronization state, data contained in the tables may not be up to date.
The size of the tables may cause script performance issues - the 'join' function is not an optimal way to handle large quantities of data.
table names may vary depending on the version of the application (go to the Data Tables section of this article for more information)
The template:
Keep in mind that 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 correctly.
Field Values
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 a blank column)
The column on the left will always contain the task key.
The next column is the resource name.
Any additional columns will display the values of the 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.
Data tables
Keep in mind data table names may vary. Make sure to adjust them accordingly.
Depending on the application's version, 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:
AO_0456E7_RESOURCE_2
AO_0456E7_T_CORE_FIELD_DEF
AO_0456E7_T_CORE_TASK_2
AO_0456E7_T_CORE_FIELD_VAL_2
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.