The following sample queries demonstrate how to join the most common report views to other views.
Joining Report Views
The following query lists all dashboards by ID and name, and all reports that are part of the dashboard. The v_Report view is joined to the v_ReportDashboardReports view by using the ReportID column. The v_ReportDashboardReports view is joined to the v_ReportDashboard view by using the DashboardID column.
SELECT RD.DashboardID, RD.Name AS DashboardName,
REP.ReportID,
REP.Name AS ReportName
FROM v_Report REP INNER JOIN
v_ReportDashboardReports RDR
ON REP.ReportID = RDR.ReportID INNER JOIN
v_ReportDashboard RD
ON RDR.DashboardID = RD.DashboardID
ORDER BY RD.DashboardID, REP.ReportID
Joining Report and Security Views
The following query lists user instance rights for reports in the Configuration Manager 2007 hierarchy. The query returns the report ID and name, user name, and instance permission name. The v_Report view is joined to the v_UserInstancePermNames security view by using the SecurityKey column from v_Report and InstanceKey from v_UserInstancePermNames. Because there might be other secured objects with the same value as the SecurityKey, the query also filters specifically for report objects by using the WHERE clause and an ObjectKey value of 8.
SELECT REP.ReportID, REP.Name, UIP.UserName,
UIP.PermissionName
FROM v_Report REP INNER JOIN
v_UserInstancePermNames UIP
ON REP.SecurityKey = UIP.InstanceKey
WHERE UIP.ObjectKey = 8
ORDER BY REP.ReportID, UIP.UserName