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


See Also


Reporting Views