ReportInstanceID and ReportBroadcastResult data

Hi Brendan,

I think, if we use advanced broadcast where recipients email addresses are fetched from another report's column, YF would reference the ReportID (from which the recipients addresses are fetched) and fetch the records directly from data source (not from config db)

In the above example, I have my recipient email addresses in field called 'email' from report id 101595. As we can see that RecipientID value of ReportBroadcastRecipient table is the same report id (101595) but not the actual value of the 'email' field.

Warm Regards,

Deepak

Hi Deepak, thanks for having a look. It's not the email field in the recipient report that I'm looking for (given that value is captured in the ReportBroadcastResult table by virtue of the recipient email).

What I'm looking for is the associated reportinstanceID, for when the report ran for that specific recipient, so that I can see what filter values were applied for that recipient email address in the ReportInstanceFilter table. The value records are there. As an example:

Advanced broadcast result for a specific email and broadcast today:

Associated reportinstance record (I've isolated the instance manually from other records for the same reportID):

And the resulting filter value that was applied to that instance:

The challenge is to automatically connect the broadcast result and report instance so that the broadcast recipient and filter value can be reported on together. Timestamps don't work because they're in different zones and formats (server vs GMT) and I've seen multiple results with the same timestamp.

Kind regards,

Brendan

Hi Brendan,

Unfortunately, we don't have a relation between ReportInstance table and ReportBroadcastResult table. So, I have created this as an internal request to add the ReportInstanceID to ReportBroadcastResult table. We can track the same in the idea post https://community.yellowfinbi.com/topic/add-a-relation-between-reportbroadcastresult-and-reportinstance-tables-in-yf-configdb.

I will mark this as answered and provide any updates to this request in the idea post created..

Best Regards,

Deepak

Hi Brendan, came across your post while searching for answers on another broadcast issue and though I'd share my experience attempting to do similar recently.

So I had requirement to check that bulk broadcasts were actually successful, so as you did, attempted to map ReportInstance to BroadcastResults to Events tables to validate. As has been confirmed there is no 100% accurate way to do this.

The workaround I eventually came up with is as follows:

• added a freehand SQL calculated field into report called "recipient email" with hardcoded value of "blank" (any text will do here).
• added this field as a free text filter with condition "Not equal to".
• This results in a 'dummy' filter that will never filter results unless you set value = "blank". What this now gives you is a filter that you can link to the recipient email when setting up the advanced broadcast.
• The result of this is that when the broadcast is run, the email address will now be captured in ReportInstanceFilters table along with the other filters used for each report instance run by the broadcast.
• The downside of this is that users may see the dummy filter, but in my case I created a report specifically for broadcast purposes.

This is of course only half of the problem, as we still want to check report instances against broadcast results.

There is still no perfect way of doing this, but now we have the email address of the broadcast recipient linked to the report instance we can use it to join to the broadcast results table.

So I join the ReportInstance table to BroadcastResults based on

• reportId = reportId
• broadcast recipient = report instance filter email value
• broadcast timestamp between report instance timestamp +/- 30 minutes (can adjust this time if desired depending on how many recipients and frequency of broadcast)

Again this is not perfect, and if the same email address has multiple reports broadcast, there will be duplicates, but the aim here is just to validate that all broadcasts have been sent. I have a report setup to display this and highlight recipients that have no report instance, or have a report instance but broadcast failed (eg. SMTP failure) so I can fix or manually resend where required.

Anyway, hope this is helpful.

Nick

Hi Nick,

Thank you very much for detailed explanation of a workaround.

I am sure it will help until we have actual enhancement in place. Updates will be posted here: https://community.yellowfinbi.com/topic/add-a-relation-between-reportbroadcastresult-and-reportinstance-tables-in-yf-configdb.

Warm Regards,

Deepak