Thursday, March 26, 2015

SSRS Reporting Structure Mapping

Documenting the structure of the SSRS reporting is often.
The below would enable you to be provide with the output in the sample screenshot with much ease.



--QUERY 1
USE ReportServer
GO

SELECT 
CASE WHEN C.Name = '' THEN 'Home' ELSE C.Name END AS ItemName, 
C.Description as Report_Description,
LEN(C.Path) - LEN(REPLACE(C.Path, '/', '')) AS ItemLevel, 
CASE 
WHEN C.type = 1 THEN '1-Folder' 
WHEN C.type = 2 THEN '2-Report' 
WHEN C.type = 3 THEN '3-File' 
WHEN C.type = 4 THEN '4-Linked Report' 
WHEN C.type = 5 THEN '5-Datasource' 
WHEN C.type = 6 THEN '6-Model' 
WHEN C.type = 7 Then '7-ReportPart'
WHEN C.type = 8 Then '8-Shared Dataset'
ELSE '9-Unknown' END AS ItemType, 
CASE WHEN C.Path = '' THEN 'Home' ELSE C.Path END AS Path, 
ISNULL(CASE WHEN CP.Name = '' THEN 'Home' ELSE CP.Name END, 'Home') AS ParentName, 
ISNULL(LEN(CP.Path) - LEN(REPLACE(CP.Path, '/', '')), 0) AS ParentLevel,
ISNULL(CASE WHEN CP.Path = '' THEN ' Home' ELSE CP.Path END, ' Home') AS ParentPath 
FROM 
dbo.Catalog AS CP 
RIGHT OUTER JOIN
dbo.Catalog AS C ON CP.ItemID = C.ParentID


The below will provide a much more detailed report level including who created and modified the report, when was the last exection  etc

USE ReportServer
GO

SELECT
CAT_PARENT.Name AS ParentName,
CAT.Name AS ReportName,
ReportCreatedByUsers.UserName AS ReportCreatedByUserName,
CAT.CreationDate AS ReportCreationDate,
ReportModifiedByUsers.UserName AS ReportModifiedByUserName,
CAT.ModifiedDate AS ReportModifiedDate,
CountExecution.CountStart AS ReportExecuteCount,
EL.InstanceName AS LastExecutedServerName,
EL.UserName AS LastExecutedbyUserName,
EL.TimeStart AS LastExecutedTimeStart,
EL.TimeEnd AS LastExecutedTimeEnd,
EL.Status AS LastExecutedStatus,
EL.ByteCount AS LastExecutedByteCount,
EL.[RowCount] AS LastExecutedRowCount,
SubscriptionOwner.UserName AS SubscriptionOwnerUserName,
SubscriptionModifiedByUsers.UserName AS SubscriptionModifiedByUserName,
SUB.ModifiedDate AS SubscriptionModifiedDate,
SUB.Description AS SubscriptionDescription,
SUB.LastStatus AS SubscriptionLastStatus,
SUB.LastRunTime AS SubscriptionLastRunTime
FROM
dbo.Catalog CAT
INNER JOIN
dbo.Catalog CAT_PARENT
ON 
CAT.ParentID = CAT_PARENT.ItemID
INNER JOIN
dbo.Users ReportCreatedByUsers
ON 
CAT.CreatedByID = ReportCreatedByUsers.UserID
INNER JOIN
dbo.Users ReportModifiedByUsers
ON 
CAT.ModifiedByID = ReportModifiedByUsers.UserID
LEFT OUTER JOIN
(
SELECT
ReportID,
MAX(TimeStart) LastTimeStart
FROM
dbo.ExecutionLog
GROUP BY
ReportID
) AS LatestExecution
ON 
CAT.ItemID = LatestExecution.ReportID
LEFT OUTER JOIN
(
SELECT
ReportID,
COUNT(TimeStart) CountStart
FROM
dbo.ExecutionLog
GROUP BY
ReportID
) AS CountExecution
ON 
CAT.ItemID = CountExecution.ReportID
LEFT OUTER JOIN
dbo.ExecutionLog AS EL
ON 
LatestExecution.ReportID = EL.ReportID
AND 
LatestExecution.LastTimeStart = EL.TimeStart
LEFT OUTER JOIN
dbo.Subscriptions SUB
ON 
CAT.ItemID = SUB.Report_OID
LEFT OUTER JOIN
dbo.Users SubscriptionOwner
ON 
SUB.OwnerID = SubscriptionOwner.UserID
LEFT OUTER JOIN
dbo.Users SubscriptionModifiedByUsers
ON
SUB.ModifiedByID = SubscriptionModifiedByUsers.UserID
ORDER BY
CAT_PARENT.Name,
CAT.Name

No comments:

Post a Comment