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

Example

Data from Resources module is generated live and saved in tables. To generate a report, you need 4 tables with information about bots resources and tasks. See the list below:
AO_0456E7_RESOURCE_2
AO_0456E7_T_CORE_TASK
AO_0456E7_T_CORE_FIELD_VAL
AO_0456E7_T_CORE_FIELD_DEF

+ field definition table: AO_0456E7_T_CORE_FIELD_DEF, where you will find fields as Assignee, Status, etc. 

--- 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" val
       join "AO_0456E7_T_CORE_FIELD_DEF" def on val."TASK_FIELD_DEF_ID"=def."ID"
   where "VISIBILITY"='GLOBAL'
)
select task.*, coalesce(resource.resource_name, '-Unassigned-') resource_name,
       f1."VALUE" start_date,
       f2."VALUE" end_date,
       f3."VALUE" ---next-field-alias---
...
from "AO_0456E7_T_CORE_TASK" task
        left join (select field_val."TASK_ID", coalesce(resource."NAME", 'Unassigned') resource_name
                   from "AO_0456E7_T_CORE_FIELD_VAL" 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."ID" = resource."TASK_ID"
        left join global_fields f1 on f1."TASK_ID"=task."ID" and f1."NAME"='Start Date'
        left join global_fields f2 on f1."TASK_ID"=task."ID" and f2."NAME"='End Date'
        left join global_fields f3 on f1."TASK_ID"=task."ID" and f3."NAME"='--- name ---'
...
where
...
;
CODE