Map drill relationships between reports in Audit views
Answered
Hi, I'm wondering if there's a way to update the Content Audit view (from the Audit pack) to include drill through relationships between reports, so that we can show reports that drill to a given report, and any reports a report drills through to. This is to help users understand relationships between reports, especially if editing these reports and possibly breaking links.
I can see a drill code in the ReportHeader table, and a ReportInstanceDrill table in the source but it's not clear if/how it can be linked to find the related drill reports.
Before experimenting, is there a known way to achieve this?
Thanks,
Brendan
The same question
Hi Brendan,
Thanks for reaching out. While something like this may be possible, it's not really in the scope of Support to determine and provide every db table relationship. That said, I did take a look into this and it seems to me that it doesn't look like there's a way to map Drill Through relationships via the config db. While there is a DRILLTHROUGH DrillCode in the ReportHeader table, there's no indication of what report that drill relationship relates to.
To further test this, I went ahead and created two test reports - a Drill Parent Report, and a Drill Child Report. In searching my entire schema for CONTAINS 'child', there are results in several tables, but none of them link back to a reference to a parent report.
If you think about what's actually happening the application when setting up a Drill Through this all pretty much makes sense. When setting up a Drill Through and clicking a hyperlink you're opening up an entirely separate report, albeit a filtered one. The latter aspect is the reason I suspect mapping the relationships may be possible - there may be some way to link up the filter values to fields in the primary report, but I suspect should a solution arise, it'd be a rather complicated one to come to.
I'd recommend taking a simpler, though more manual, approach, such as perhaps creating a content folder dedicated to Child Reports, and maybe writing in their descriptions what the parent report is called, especially if the primary concern here is having users change child reports and thus breaking the link from the Parent Report. You can also even just indicate in the description that the report is a Child Report. Another idea would be to have stricter security on reports that have Parent/Child relationships, by perhaps making them read-only for most users, and allowing edit access only to a small, dedicated group who's intended to have knowledge of which reports feature Drill Through relationships.
Sorry there doesn't seem to be a simple answer for this, but hopefully one of these ideas gets you started!
Regards,
Mike
Hi Brendan,
Thanks for reaching out. While something like this may be possible, it's not really in the scope of Support to determine and provide every db table relationship. That said, I did take a look into this and it seems to me that it doesn't look like there's a way to map Drill Through relationships via the config db. While there is a DRILLTHROUGH DrillCode in the ReportHeader table, there's no indication of what report that drill relationship relates to.
To further test this, I went ahead and created two test reports - a Drill Parent Report, and a Drill Child Report. In searching my entire schema for CONTAINS 'child', there are results in several tables, but none of them link back to a reference to a parent report.
If you think about what's actually happening the application when setting up a Drill Through this all pretty much makes sense. When setting up a Drill Through and clicking a hyperlink you're opening up an entirely separate report, albeit a filtered one. The latter aspect is the reason I suspect mapping the relationships may be possible - there may be some way to link up the filter values to fields in the primary report, but I suspect should a solution arise, it'd be a rather complicated one to come to.
I'd recommend taking a simpler, though more manual, approach, such as perhaps creating a content folder dedicated to Child Reports, and maybe writing in their descriptions what the parent report is called, especially if the primary concern here is having users change child reports and thus breaking the link from the Parent Report. You can also even just indicate in the description that the report is a Child Report. Another idea would be to have stricter security on reports that have Parent/Child relationships, by perhaps making them read-only for most users, and allowing edit access only to a small, dedicated group who's intended to have knowledge of which reports feature Drill Through relationships.
Sorry there doesn't seem to be a simple answer for this, but hopefully one of these ideas gets you started!
Regards,
Mike
Hi Brendan,
Actually, I was incorrect. I have found that the parent/child relationship information is primarily contained in the 'reportassociate' table.
Here's my Drill Through Parent report in the 'reportheader' table:
I search that Id in the reportassociate table:
And here you can see the ChildReportId associated with this parent report.
If I search that ChildReportId in the reportheader table I can confirm it's the correct report:
So you'd be taking the DRILLTHROUGH DrillCode from the reportheader table, linking the ReportId from the reportheader table to the ParentReportId column in the reportassociate table.
Hopefully this helps!
Regards,
Mike
Hi Brendan,
Actually, I was incorrect. I have found that the parent/child relationship information is primarily contained in the 'reportassociate' table.
Here's my Drill Through Parent report in the 'reportheader' table:
I search that Id in the reportassociate table:
And here you can see the ChildReportId associated with this parent report.
If I search that ChildReportId in the reportheader table I can confirm it's the correct report:
So you'd be taking the DRILLTHROUGH DrillCode from the reportheader table, linking the ReportId from the reportheader table to the ParentReportId column in the reportassociate table.
Hopefully this helps!
Regards,
Mike
Hi Mike,
Brilliant! Thank you so much! I've been able to update the view to bring back both the parent report that drills to a given report and the child report it drills to. I had been working on the assumption these details would need to be maintained manually as you first suggested, but being able to report on these relationships and automatically reflect any changes is so much better.
Thanks for looking into it and testing for me - much appreciated.
Kind regards,
Brendan
Hi Mike,
Brilliant! Thank you so much! I've been able to update the view to bring back both the parent report that drills to a given report and the child report it drills to. I had been working on the assumption these details would need to be maintained manually as you first suggested, but being able to report on these relationships and automatically reflect any changes is so much better.
Thanks for looking into it and testing for me - much appreciated.
Kind regards,
Brendan
Hi Brendan,
You're welcome! I'll go ahead and close this case out this considered, but please don't hesitate to reach out with any other questions or concerns.
Regards,
Mike
Hi Brendan,
You're welcome! I'll go ahead and close this case out this considered, but please don't hesitate to reach out with any other questions or concerns.
Regards,
Mike
Replies have been locked on this page!