Report View Sample Queries
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 make up 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 SMS 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 view by using the SecurityKey column from v_Report and InstanceKey from v_UserInstancePermNames. Because there may be other secured objects with the same value as the SecurityKey, the query also filters for only 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