Problem with Scheduled report

[Andrew Parsons]

I have a scheduled report set up that emails out on a regular basis (every 2 weeks). It joins the following tables:

 

 

The criteria are as follows: (usernames blanked out, and before you ask, I'm trying to get a report of all activities by members of a certain team, and this was the only way I could see to do this, by looking for entries in the buz activities table - open to suggestions of how I could do it better!)

 

  

I set up the report, got it working, and scheduled. When the first scheduled report ran, the output was blank.

I checked the report, and when I went to preview it was also blank (despite having worked before when I first set it up)

I removed the time frame (the "updated value is greater than..."  This time when I hit preview, I got results:

However the report is only supposed to look at recent activity, hence the "updated value..." filter. As you can see from the above, some of those activities are from Feb 2024.

Next, I re-added the same "updated value..." filter, and previewed again. This time I got results, and the filter was working as they were only updates added in the past few weeks.

 

I even manually ran the report to generate the output, and got the expected results. I scheduled a date for a few minutes time, and the output came through without issue. I reset the report back to the required schedule.

 

The problem is, I've done the above at least 3 times now. Every time I do, the report appears to be fixed, but the next time the schedule runs, it fails.  If I go back into the same report a few mins later, it still seems to run without issue, but to be honest I forget to check it again before it goes to the next schedule.

Having just fixed the report again, I'll set myself a reminder to keep checking it over the next few days to see if it reverts to non-working state, and will post the result here

 

In the mean time, if anyone else has had the same issue, is aware of a fix, or can see anything wrong in my criteria above, please let me know!

 

[Steve Giller]

Firstly - can you clarify what you mean by "it fails" please?
E.g. there is a failure indicator and an error message? The report is simply blank? Something else?

Secondly, and possibly more importantly, the "buz" tables are optimised for performance and not reporting, and can be huge. It is strongly recommended that you do not report on these tables, and doing so will frequently result in empty results as the query will time out before completing.

59 minutes ago, Andrew Parsons said:

I'm trying to get a report of all activities by members of a certain team ... open to suggestions of how I could do it better!

I'm not able to suggest a "Do it better" option but it may be worth rolling back further than that and reviewing what you're trying to achieve here - it's possible that rather than a better way of gathering the Timeline data there's a better way to find the information you need.

[Andrew Parsons]

Hi Steve,

1) Apologies I thought I made it clear with this sentence

"When the first scheduled report ran, the output was blank."

Screenshot of recent scheduled 'failed' report:

 

From the report history tab, which seems to suggest it has run without issue

So the schedule runs as planned, its just there's no output. And if you read the above, editing the report and removing / re-adding the date filter seems to fix it, until the next time it runs on schedule

 

2) While I take your comment about the buz table not being optimised for reporting, this doesn't explain why after my workaround above, it reports whenever I run it manually, but not on next schedule. If it were simply a performance issue, surely it would never work? Also at the end of the day, I'm querying a table of data, so how does this differ from reporting on the Requests table?

 

3) As per my earlier comments, after 'fixing' it again this morning..... I went back into the report minutes after saving, the preview continued to work, and manually running the report worked also.  However I've just checked it again at approx 13:50 (so around 2 hours later) and once again the preview is returning no results, despite working earlier:

 

4) As for what I'm trying to achieve:

We would want to report on an individual's activity within Hornbill. As an example, in a team of 5 support staff, we want to visibly see that each member of the team are contributing similar efforts to ticket resolutions. Currently, we can only report on who a ticket is currently assigned to,  or who it was resolved by. However, a single ticket may have had 3 members of support staff working on it, yet it is only resolved by 1. This is why a list of all tangible actions (eg: updates, reassignments, user contact) in the system for each team member over a given time period will be useful.

Short of opening every ticket that might have been through a team and manually looking for updates, to me a report seems the best solution, but happy to explore alternatives.