ReportInstanceID and ReportBroadcastResult data

Brendan Codrington shared this idea 16 months ago
Idea Logged

Hi, I'm looking for a way to connect the filter value applied to each advanced broadcast recipient in the metadata.

I can get the filter values applied to each instance from the reportinstancefilter table, and use the reportinstance table to connect that to the original reportid.

However, I can't find a corresponding value in the broadcast data and specifically the ReportBroadcastResult table to link the reportinstance to the broadcast result/recipient/runUUID/timestamp.

If I could, I'd be able to list each advanced broadcast recipient and the value they were filtered against for each broadcast run.

This could then be developed further to track which recipients have been matched per value and shouldn't receive the advanced broadcast when it runs again (for cases where they should only get it once).

Is there another table or way to connect broadcast result/recipient with reportinstanceID?

Thanks,

Brendan

Replies (5)

photo
1

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)

/077567520b8157d35fad932c7876719b


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

photo
1

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:

/332c1528b07be8ea2851131ed671aed5

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

/e3d34f1eec1512d5545e6bc76dae43dd

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

/486336654511587828e552bb7cf33cee


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

photo
1

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

photo
1

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. 7d43318ed092975609e6d5254ed13a84
  • 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


4c0beb712cae8b5f195f73893491d283

photo
1

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

Leave a Comment
 
Attach a file