Advanced Reports - Timeline Update column not updating

[Daniel]

Hi All,

In "Advanced" Reports (Home>Applications>Hornbill Service Manager>Reports) , column: h_buz_activities.h_content (Timeline Update) does not reflect the latest timeline posts for some reason. It only shows the "Request has been logged" event. Any idea why?

Thanks

Daniel 

[Daniel]

the attached pictures show difference between Report and Timeline. The "Last Timeline Update" column (h_buz_activities.h_content) in report doesn't show the latest update from the Timeline in Request.

Could you help, please

thanks
Daniel

[Daniel]

Hi @Bob Dickinson,

are you able to help please?

thanks Daniel

[Bob Dickinson]

Hi @Daniel

Could you download your report defintion from Hornbill, and send it to me via a private message if possible? I'll take a look to see what is happening here?

Kind Regards

Bob

[Bob Dickinson]

Hi @Daniel

Unfortunately we do not have the ability in the Reporting Functionality to extract this report in the format that you require it (as this involves some complex SQL to achieve). This is certainly something that we will look to add as an "Out of the box" report in the future using our in-app reporting. 

In the meantime - as a workaround we have been able to provide the SQL that would be required to gather this information from Database Direct on an ad hoc basis. Obviously this cannot be scheduled or exported to a PDF (CSV only), but if you are looking to get the data out of Hornbill, this is a potential way of doing it. 
Please keep in mind that as this is covering a large dataset, we would advise adding date criteria, a results limit and/or other filtering to keep the results to a minimum wherever possible - and this should only be run/refreshed infrequently (i.e. not every few mins) to prevent any potential performance issues. 

 

SELECT
                h_itsm_requests.h_datelogged
                , h_itsm_requests.h_pk_reference
                , h_itsm_requests.h_fk_user_name
                , h_itsm_requests.h_summary
                , h_itsm_requests.h_description
                , h_buz_activities.h_content
                , h_buz_activities.h_updated
                , h_itsm_requests.h_ownername
                , h_itsm_requests.h_fk_priorityname
                , h_itsm_requests.h_status
                , h_sys_groups.h_name
FROM h_itsm_requests
JOIN h_sys_account_groups ON h_itsm_requests.h_fk_user_id = h_sys_account_groups.h_user_id
JOIN h_sys_groups ON h_sys_account_groups.h_group_id = h_sys_groups.h_id
JOIN h_buz_activities ON h_itsm_requests.h_activity_stream_id = h_buz_activities.h_target
WHERE h_buz_activities.h_id = (
                SELECT bAb.h_id
                FROM h_buz_activities bAb
                WHERE bAb.h_target = h_buz_activities.h_target
                ORDER BY bAb.h_ustamp DESC
                LIMIT 1
)
AND h_sys_groups.h_type = 2
AND h_itsm_requests.h_status IN ('status.new', 'status.open', 'status.onhold')
AND (
                h_sys_groups.h_name = 'Scheduling and Business Support'
                OR h_sys_groups.h_name = 'Repairs'
)
ORDER BY h_itsm_requests.h_datelogged DESC

 

I hope this helps

Kind Regards

Bob

[Daniel]

Thank you @Bob Dickinson

Regards,

Daniel