Audit views - limiting ReportBroadcast table to IpOrg/OrgName

Brendan Codrington shared this question 6 days ago
Awaiting Reply

Unless I've missed an obvious field/link, is there a way to link the ReportBroadcast table to the Organisation table based on the orgname/IpOrg the broadcast is created in? There's no IpOrg field in the table. I can apply an access filter for the OrgName to Reports, Data Sources and Content Folder tables as they have an IpOrg field to join with.


I can't do it through these other tables as it may be that broadcasts are set up in the child org against reports that are stored in the parent client org/built on a parent org data source.


Thanks,

Brendan

Comments (5)

photo
1

Hi Brendan,

Thank you for reaching out to us.

The "ReportBroadcast" table has an field called reportid as a forgien key of "ReportHeader" table, from this table you can use iporg to join this to the organisation table.

Please let me know if you have any further questions relating to this query.

Kind regards,

Nathan Goddard

photo
1

Hi Nathan,


Is that meant to work wth reports that are in the parent org but the broadcast is set up in a child org? When I apply the access filter to the org name linked to iporg in the reportheader table, it only shows me the broadcasts against reports in the child org, not the broadcasts set up in the child org but on reports in the parent org. (I would get the same outcome doing it through content folder or data source.)


Thanks,

Brendan

photo
1

Hi Brendan,

Sorry for the delay in getting back to you, I have looking into your feedback to see what options we have.

As you know we can join the Reportbroadcast table to the reportheader to allow you to access the OrgId

SELECT * FROM reportbroadcast a LEFT JOIN reportheader b ON (a.ReportId = b.ReportId) WHERE b.IpOrg OR c.IpOrg = '1'

There does not seem to be a simple method for getting the child org that is linked to the broadcast. Based on what I have found so far is that it is possible to join from the reportbroadcast table to the reportbroadcastrecipient then to person then the person org relationship table which should allow to join the childiporg.

Does what I have described above sound like a possible solution?

Please let me know what you think.

Kind regards,

Nathan Goddard

photo
1

Hi Nathan, thanks for having a look into this. I had considered that as well but it would only work for recipients that are users in that child org, and this may well cover off a majority of scenarios but won't include those where a parent org report is being broadcasted from the child org to an email address.


I might consider this as a workaround for now though, assuming the likelihood that this scenario will occur should be low (or, at a stretch, limiting these broadcasts manually to users only), but it will make the view/access filter config a little clunkier than expected and with a caveat on the reports using them.


Is it possible to raise this scenario as an enhancement idea, to directly link the broadcast table with iporg/orgname in the future?


Thanks,

Brendan

photo
1

Hi Brendan,

Thank you for coming back to me, before potenitally progressing this to an enchancement could you share with me the tables and fields you are trying to join and any SQL or psudo sql you have for a desired query.

I would like to use this to reach out the wider team to confirm if this would need to be an enhancement or if there are any options available e.g. joining to the same table twice using a join on OrgId and childOrgId then using a where clause to extract the correct data from each join.

Kind regards,

Nathan Goddard