Audit views - limiting ReportBroadcast table to IpOrg/OrgName

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

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

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

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

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

Hi Nathan,

No problems, here's the SQL for the modified admin audit view and the broadcast report I've been testing with. Note the org name in this one is linked to the data source but same behaviour as if via report or content folder.

Thanks,

Brendan

Hi Brendan,

Thank you for sending this through, I will be working through this and will reach out to the wider team for additional input. I will be sending you an update as soon as I have investigated further.

Kind regards,

Nathan Goddard

Hi Brendan,

Thank you for your patience, I have been working through this and would like to make sure that I am working on the same path as yourself. Would you be able to provide a use case from what you are wanting to achieve by joining ReportBroadcasts to ClientOrgID?

I'm hoping that with this explanation I can establish there is a potential for an alternative method.

Kind regards,

Nathan Goddard

Hi Nathan, the use case is pretty much as outlined above - I have reports that are saved in the parent client org that other child client orgs can access, run and set up broadcasts against. As a child client org user I want to have visibility of all of the broadcasts set up in my client org, regardless of where the report/content folder/source/view is, ie the orgID against all other parameters might belong to the parent client org, but because the broadcast is set up in my child client org (to users and/or email addresses) I need to see it with all other child client org broadcasts.

Thanks,

Brendan

If I could throw in a related query while we're looking at the broadcast table - is there a field that represents the broadcast frequency?

Thanks,

Brendan

Sorry, I noticed something else - do the broadcast records include the history for now-deleted broadcasts, and if they do is there a way to identify deleted vs active broadcasts, or does the existence of records imply the broadcast is active and they will disappear when the broadcast is no longer active?

Thanks,

Brendan

Hi Brendan,

I have summarized your points below and am currently working with the team to provide some answers.

Summary:

1. I have reports that are saved in the parent client org that other child client orgs can access, run and set up broadcasts against. As a child client org user I want to have visibility of all of the broadcasts set up in my client org, regardless of where the report/content folder/source/view is, ie the orgID against all other parameters might belong to the parent client org, but because the broadcast is set up in my child client org (to users and/or email addresses) I need to see it with all other child client org broadcasts.

2. Broadcast Table - is there a field that represents the broadcast frequency?

3. Do the broadcast records include the history for now-deleted broadcasts, and if they do is there a way to identify deleted vs active broadcasts, or does the existence of records imply the broadcast is active and they will disappear when the broadcast is no longer active?

Kind regards and thank you for your patience.

Nathan Goddard

Hi Brendan,

Hope you are well.

I have had some feedback from the wider team on your points below;

1. I have reports that are saved in the parent client org that other child client orgs can access, run and set up broadcasts against. As a child client org user I want to have visibility of all of the broadcasts set up in my client org, regardless of where the report/content folder/source/view is, ie the orgID against all other parameters might belong to the parent client org, but because the broadcast is set up in my child client org (to users and/or email addresses) I need to see it with all other child client org broadcasts.

The 'TaskSchedule' table should allow you to do this, The IpORG will be based on where the schedule lives, so if the broadcast was created in client1, then the IpOrg will show for that Client1

2. Broadcast Table - is there a field that represents the broadcast frequency?

The frequency type code and values are also in this table. Information about this table and understanding the frequency type code and values are explained here.

3. Do the broadcast records include the history for now-deleted broadcasts, and if they do is there a way to identify deleted vs active broadcasts, or does the existence of records imply the broadcast is active and they will disappear when the broadcast is no longer active?

I'm afraid that once a broadcast is deleted it will not be shown anywhere.

Please let me know if you have any questions regarding the above.

Kind regards,

Nathan Goddard

Hi Nathan, ah, fantastic! I'll check the TaskSchedule table out for both of these items as I was hoping there would be an existing way to do it. I'll let you know how I go on both counts.

Thanks,

Brendan

Hi Brendan,

Please do let me know how you get on.

I look forward to hearing from you.

Kind regards,

Nathan Goddard

Hi Brendan,

Hope you are well.

I wanted to check in to see if the use of the TaskSchedule table has helped.

Please let me know if you have any other questions.

Kind regards,

Nathan Goddard

Hi Nathan,

We have a winner! I can now see the broadcasts in a child client org even if it is on a report from the parent.

So that opens up another question (sorry!). I can see the access filter in the view which allows me to do this against the org name in that context (OR others - as I can only use the access filter once). I now need to duplicate that filter so I can choose which org name context (report, task schedule, etc) needs to be applied to different reports on the same view. Problem is I can't see that Client Reference ID access filter against the source (I've triple checked i'm looking at the right source!) in order to replicate it.

Any thoughts - have I missed a different way to access it?

Thanks,

Brendan

Hi Brendan,

Sorry for the delay in getting back to you.

Can you confirm, are you trying to filter by the Client viewing the content or the user viewing the content?

In the first screen show it appears that you are configuring this to filter access at the client level which pulls the client ID of the current user which is why you dont see this against the data source because a look up is not required due the data being pulled in from the current users session information.

Whereas if you where filtering at the user level you would need to create a lookup so that the client ID can be select, you would then see this against the data source.

Kind regards,

Nathan Goddard

Hi Nathan, ok that makes sense, in that yes I'm filtering by the client so it appears to be an automatic filter, however does that mean I can only use it on one orgname field per view, not multiple (and then choose which orgname context to filter by at the report level)? If so, I guess I'll have to split the views ie have a separate one for broadcasts.

Thanks,

Brendan

Hi Brendan,

Thank you for your update.

To confirm are you filtering the view or wanting to filter the reports based on the unfiltered view?

If you are filtering at the view then you would need to create multiple views per client org, whereas if you do it at the report level with an unfiltered view then you can create a filter with a user prompt and select which client org you wish to filter by.

Doing this at the report level would also allow you to create defined filters for reports so that if you wanted you could create a report per client org.

I hope that makes sense.

Kind regards,

Nathan Goddard

Thanks Nathan,

For our purposes we need the access filter control to be at the view level so that individual client orgs can run the same report but automatically only see their data. Based on this, I'll create a separate view for each different orgname-based access filter context (broadcast, report etc).

Thanks for all your help with this query!

Kind regards,

Brendan

Hi Brendan,

You are very welcome, glad we could help.

You may already be aware but just a friendly reminder that if you need to reproduce the same view you can use the 'Make a Copy' Function when on the browse view page.

Hopefully using this will save you some time on creating these for each client org.

Is there any further you require assistance on, or are you happy for me to mark this question as answered?

Kind regards,

Nathan Goddard

Thanks Nathan, yep mark as answered and close away!

Kind regards,

Brendan

Hi Brendan,

That's great news, this has now been marked as answered.

Have a fantastic week.

Kind regards,

Nathan Goddard