Reporting on Activities

joedziedzicbbt · ‎12-20-2011

Does anyone know if i can report on Public Comments and IT Work Notes that are saved as an activity? I have tried setting up the reports, and have searched both the Incident and Task tables, and I can not seem to find them.

w_bouwer · ‎12-27-2011

The element ID is the sys_id of the record you are looking for. You could make a database view that connects sys_id with the task record:

1. Go to database views and create a new database view.
2. Add a view table 'sys_journal_field' with the view fields 'Element','Element_id','Value'. Variable prefix is 'jou'. Check left join to true.
3. Add another view tabel 'task' with the view fields 'Number','Sys ID'. Variable prefix is 'tsk'. Check left join to true.
4. Add a where clause to the task table 'tsk_sys_id = jou_element_id' (where task sys_id is equal to journal element sys_id).

Now when you test it out with the 'try it' UI action, you can add fields to the view of the records. You can report on this table as much as you want 🙂

Regards,

Wesley

View solution in original post

w_bouwer · ‎12-21-2011

I'm not 100 percent sure the two are always completely the same, but you are able to view all the entries from any journal fields in the table sys_journal_field.

You should also be able to report on it. The table will not show up directly when you create a new report, but when you saved it, you can go to sys_report, and change the table there. When you reopen it from the 'normal' reporting screen, you will be able to adjust the specs of the report based of the fields that are available on the table.

Regards,

Wesley

afofana · ‎12-21-2011

Probably best to create a custom table and populate that table based on a business rule for the fields you want.

joedziedzicbbt · ‎12-21-2011

Thank you both for the input, but I am having a heck of a time finding the sys_journal_field table. Is it a part of a separate table?

w_bouwer · ‎12-21-2011

It's a 'hidden' table. Some tables lack direct access, but are required for functionalities. This is one of those tables. To view and see these 'unaccessible' tables, you can use the Navigation filter. Enter 'sys_journal_field.list' and you will automatically be guided to the table.

Regards,

Wesley