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)

The template:

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
--     ...
CODE

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.


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 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. 

Data tables 

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:

  • 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:

AO_0456E7_T_CORE_TASK
AO_0456E7_T_CORE_FIELD_VAL
CODE

For versions 8.0.19+ the same data will be in tables with a suffix:

AO_0456E7_T_CORE_TASK_2
AO_0456E7_T_CORE_FIELD_VAL_2
CODE