h_buz_activities Table

[Claire B]

Hi all,

When we tried to run a report with h_buz_activities table selected on Service Manager (within Reporting area), using "updated" column to filter out the report, it would not display anything. However if we used "Published" column, it does display data.

Is anyone experiencing similar issue that may be able to assist please?

Cheers,

Claire.

[Steve Giller]

I'm not sure what you're trying to achieve here, but reporting on this table is not recommended due to the size and complexity of the content, and because the restrictions we have in place to maintain performance will frequently cause reports to return an empty recordset as the query has timed out.

[Claire B]

Morning Steve,

We make use of data warehouse to pull data from Service Manager for PowerBi reporting with success, and use h_buz_activities table to display specific information not easily captured or displayed elsewhere (other tables).

Unfortunately for some unknown reason, in the last few weeks, we were not able to pull data from h_buz_activities, and upon some testing, found that "updated" column no longer show data. This is currently affecting a number of reports which relies on this particular table.

Cheers,

Claire.

[Val C]

Hi Steve

When we first set up the extract report, we used the Updated column and the Published column to pull rows that had been updated/published today.  This stopped returning rows as I assume that it is now timing out due to the restrictions you have in place.

The preview (limited to 500 rows) shows that the filter criteria does return rows, however when running the report, it results in a Processed [0] records message on completion.

Can you advise how we can extract a daily report for h_buz_activities so that we can load this data into our data warehouse using the goHornbillDataExport tool?

Thanks

Val 

[Steve Giller]

21 minutes ago, Val C said:

Can you advise how we can extract a daily report for h_buz_activities so that we can load this data into our data warehouse using the goHornbillDataExport tool?

Caveat: Please have this advice checked, because querying SQL isn't really my area, but I would guess that adding a date criteria to the query would achieve this.

As the goHornbillDataExport tool uses a Report, and Reporting has dynamic variables a daily schedule to add updates

BETWEEN {{DATE:startofyesterday}} AND {{DATE:endofyesterday}}

Should add the previous day's data to the existing table in your warehouse, for example.

 

[Val C]

We have used the dynamic variables, however where including updated used to return rows for the reports, we are now getting zero processed rows, but the data preview shows rows being returned.

Has the Updated column been changed in any way which would explain why the reports isn't processing any records.

To clarify, one day the report was working and the next it wasn't.

Any clarification would be greatly appreciated.

[Val C]

@Steve Giller
Are you able to tell me what might have changed to stop the above working.

Many thanks

[Steve Giller]

On 1/20/2023 at 11:34 AM, Val C said:

we are now getting zero processed rows, but the data preview shows rows being returned

That sounds like the full report is returning so much data that the query times out.

If you reduce the selected time range to a day, do you see results?

[Val C]

Hi,  We have limited it to a single day using your variables for start and end of today, and using an actual date 2023-03-10, and it produces the same result using the updated column.  However using the published column works and produces a reports with a couple of thousand rows.

Can you tell me if these columns are different in the way they are structured, or whether the updated date has changed in some way. 

I cannot see how one would work and the other would not.

Please help

Thank you

[Steve Giller]

On 3/10/2023 at 12:09 PM, Val C said:

Can you tell me if these columns are different in the way they are structured 

Yes, they are different, h_published is an Indexed field, and h_updated is not, so h_published will return results more quickly.

On 3/10/2023 at 12:09 PM, Val C said:

or whether the updated date has changed in some way

Any reply (comment) to the original Timeline Post will change the post's h_updated value, so yes, this is subject to change.