How long a SQL Agent job takes to run

USE msdb
select
j.job_id
,j.name as job_name
, s.step_name
, s.step_id
-- , s.command
,dbo.agent_datetime(run_date, 0) as step_start_date
,dbo.agent_datetime(run_date, run_time) as step_start_date_time
--,h.instance_id
,h.run_duration
,run_duration/10000 as run_duration_hours_only --hours
,run_duration/100%100 as run_duration_minutes_only --minutes
,run_duration%100 as run_duration_second --seconds
,(run_duration/10000 * 60 * 60) + -- hours as seconds
(run_duration/100%100 * 60) + --minutes as seconds
(run_duration%100 ) as run_duration_total_seconds --seconds
-- , h.*
from msdb.dbo.sysjobs j inner join
msdb.dbo.sysjobsteps s on j.job_id = s.job_id inner join
msdb.dbo.sysjobhistory h on s.job_id = h.job_id and s.step_id = h.step_id
where
1=1
and j.name like  'My Job Name'
--order by h.instance_id desc

Create a "LIKE " type filter for a string parameter in SSRS report

You can create a simple user defined filter parameter by applying a filter to the recordset and writing a bit of VB code

The VB code is as follows:

Public Function fnLike(ByVal val as string, ByVal Filter as string) AS Boolean

val = lcase(val)
Filter = lcase(Filter)

If len(trim(filter)) = 0 then
    Return True
End If

If val.contains(filter) Then
    Return True
Else
    Return False
End If
End Function

To implement simply put the following in to the Filter Expression for the recordset

=code.fnLike(Fields!EMPLOYEE_NAME.Value, Parameters!PractitionerName.Value)

For the other parameters for the filter set

• Type - Boolean
• Operator "="
• Value - True

Get date format sql is using

select dateformat from sys.syslanguages   where name = @@LANGUAGE

SSRS - Report Run History

Option Explicit

Dim objConnection, objCommand, objRootDSE, strDNSDomain, strQuery
Dim objRecordSet, strDN, objGroup
Dim sEmpID, aManager, sManager
Dim objDB
dim strODBC, oRS

'this constant is used to define ODBC Connection
strODBC  = "Data Source=CMS;Initial Catalog=CMS;Persist Security Info=True;User ID=cms;Password=cms"

' Use ADO to search Active Directory.
Set objConnection = CreateObject("ADODB.Connection")
Set objCommand = CreateObject("ADODB.Command")
objConnection.Provider = "ADsDSOObject"
objConnection.Open "Active Directory Provider"
Set objCommand.ActiveConnection = objConnection

' Search.
strQuery = ";(&(objectCategory=person)(objectClass=user)(name=*));displayName,Manager,employeeID,employeeNumber;subtree"
objCommand.CommandText = strQuery
objCommand.Properties("Page Size") = 100
objCommand.Properties("Timeout") = 30
objCommand.Properties("Cache Results") = False
objCommand.Properties("Sort On") = "sAMAccountName"

Set objRecordSet = objCommand.Execute
If objRecordSet.EOF Then
  Wscript.Echo "No users found"
  objConnection.Close
  Set objRootDSE = Nothing
  Set objConnection = Nothing
  Set objCommand = Nothing
  Set objRecordSet = Nothing
  Wscript.Quit
End If

set objDB = Nothing
set oRS = Nothing

'Connect to database then run the SQL to generate load
Set objDB = DBConnect()

'Wscript.Echo "Before loop"

Do Until objRecordSet.EOF
    'On Error Resume Next

    err.Clear
    sEmpID = objRecordSet.Fields("employeeID")
    aManager = ""
    sManager = ""

    If err.number = 0 Then
        If sEmpID <> "" Then

      on error resume next 'nulls causing problems here
            aManager = split(objRecordSet.Fields("Manager"), ",", -1)

            If len(aManager(0)) > 3 Then
                sManager = right(aManager(0), len(aManager(0)) -3)
            End If

      on error goto 0
      'the replace on displayName is to get rid of the comma in irish names like Louise O'Hanlon which kill the SQL
      WScript.Echo "EXEC SET_ADUser '" & Replace(objRecordSet.Fields("displayName"),"'","") & "'," & objRecordSet.Fields("employeeID") & ",'" & sManager & "','" & objRecordSet.Fields("employeeNumber") & "'"
            'Set oRS = objDB.Execute("EXEC SET_ADUser '" & Replace(objRecordSet.Fields("displayName"),"'","") & "'," & objRecordSet.Fields("employeeID") & ",'" & sManager & "','" & objRecordSet.Fields("employeeNumber") & "'")
            '   oRS.MoveFirst 'this forces load of total recordset


        End If
    End If

    objRecordSet.MoveNext
Loop


Wscript.Echo "After loop"

' Clean up.
objConnection.Close
Set objRootDSE = Nothing
Set objGroup = Nothing
Set objConnection = Nothing
Set objCommand = Nothing
Set objRecordSet = Nothing


'This function sets up DB Connection using specified DSN
  Function DBConnect
  Set objDB = CreateObject("ADODB.Connection")
  objDB.Open strODBC 'change this for each server'
  Set DBConnect = objDB
End Function

SSRS - Report Run History

Option Explicit

Dim objConnection, objCommand, objRootDSE, strDNSDomain, strQuery
Dim objRecordSet, strDN, objGroup
Dim sEmpID, aManager, sManager
Dim objDB
dim strODBC, oRS

'this constant is used to define ODBC Connection
strODBC  = "Data Source=CMS;Initial Catalog=CMS;Persist Security Info=True;User ID=cms;Password=cms"

' Use ADO to search Active Directory.
Set objConnection = CreateObject("ADODB.Connection")
Set objCommand = CreateObject("ADODB.Command")
objConnection.Provider = "ADsDSOObject"
objConnection.Open "Active Directory Provider"
Set objCommand.ActiveConnection = objConnection

' Search.
strQuery = ";(&(objectCategory=person)(objectClass=user)(name=*));displayName,Manager,employeeID,employeeNumber;subtree"
objCommand.CommandText = strQuery
objCommand.Properties("Page Size") = 100
objCommand.Properties("Timeout") = 30
objCommand.Properties("Cache Results") = False
objCommand.Properties("Sort On") = "sAMAccountName"

Set objRecordSet = objCommand.Execute
If objRecordSet.EOF Then
  Wscript.Echo "No users found"
  objConnection.Close
  Set objRootDSE = Nothing
  Set objConnection = Nothing
  Set objCommand = Nothing
  Set objRecordSet = Nothing
  Wscript.Quit
End If

set objDB = Nothing
set oRS = Nothing

'Connect to database then run the SQL to generate load
Set objDB = DBConnect()

'Wscript.Echo "Before loop"

Do Until objRecordSet.EOF
    'On Error Resume Next

    err.Clear
    sEmpID = objRecordSet.Fields("employeeID")
    aManager = ""
    sManager = ""

    If err.number = 0 Then
        If sEmpID <> "" Then

      on error resume next 'nulls causing problems here
            aManager = split(objRecordSet.Fields("Manager"), ",", -1)

            If len(aManager(0)) > 3 Then
                sManager = right(aManager(0), len(aManager(0)) -3)
            End If

      on error goto 0
      'the replace on displayName is to get rid of the comma in irish names like Louise O'Hanlon which kill the SQL
      WScript.Echo "EXEC SET_ADUser '" & Replace(objRecordSet.Fields("displayName"),"'","") & "'," & objRecordSet.Fields("employeeID") & ",'" & sManager & "','" & objRecordSet.Fields("employeeNumber") & "'"
            'Set oRS = objDB.Execute("EXEC SET_ADUser '" & Replace(objRecordSet.Fields("displayName"),"'","") & "'," & objRecordSet.Fields("employeeID") & ",'" & sManager & "','" & objRecordSet.Fields("employeeNumber") & "'")
            '   oRS.MoveFirst 'this forces load of total recordset


        End If
    End If

    objRecordSet.MoveNext
Loop


Wscript.Echo "After loop"

' Clean up.
objConnection.Close
Set objRootDSE = Nothing
Set objGroup = Nothing
Set objConnection = Nothing
Set objCommand = Nothing
Set objRecordSet = Nothing


'This function sets up DB Connection using specified DSN
  Function DBConnect
  Set objDB = CreateObject("ADODB.Connection")
  objDB.Open strODBC 'change this for each server'
  Set DBConnect = objDB
End Function

Option Explicit

Dim objConnection, objCommand, objRootDSE, strDNSDomain, strQuery
Dim objRecordSet, strDN, objGroup
Dim sEmpID, aManager, sManager
Dim objDB
dim strODBC, oRS

'this constant is used to define ODBC Connection
strODBC  = "Data Source=CMS;Initial Catalog=CMS;Persist Security Info=True;User ID=cms;Password=cms"

' Use ADO to search Active Directory.
Set objConnection = CreateObject("ADODB.Connection")
Set objCommand = CreateObject("ADODB.Command")
objConnection.Provider = "ADsDSOObject"
objConnection.Open "Active Directory Provider"
Set objCommand.ActiveConnection = objConnection

' Search.
strQuery = ";(&(objectCategory=person)(objectClass=user)(name=*));displayName,Manager,employeeID,employeeNumber;subtree"
objCommand.CommandText = strQuery
objCommand.Properties("Page Size") = 100
objCommand.Properties("Timeout") = 30
objCommand.Properties("Cache Results") = False
objCommand.Properties("Sort On") = "sAMAccountName"

Set objRecordSet = objCommand.Execute
If objRecordSet.EOF Then
  Wscript.Echo "No users found"
  objConnection.Close
  Set objRootDSE = Nothing
  Set objConnection = Nothing
  Set objCommand = Nothing
  Set objRecordSet = Nothing
  Wscript.Quit
End If

set objDB = Nothing
set oRS = Nothing

'Connect to database then run the SQL to generate load
Set objDB = DBConnect()

'Wscript.Echo "Before loop"

Do Until objRecordSet.EOF
    'On Error Resume Next

    err.Clear
    sEmpID = objRecordSet.Fields("employeeID")
    aManager = ""
    sManager = ""

    If err.number = 0 Then
        If sEmpID <> "" Then

      on error resume next 'nulls causing problems here
            aManager = split(objRecordSet.Fields("Manager"), ",", -1)

            If len(aManager(0)) > 3 Then
                sManager = right(aManager(0), len(aManager(0)) -3)
            End If

      on error goto 0
      'the replace on displayName is to get rid of the comma in irish names like Louise O'Hanlon which kill the SQL
      WScript.Echo "EXEC SET_ADUser '" & Replace(objRecordSet.Fields("displayName"),"'","") & "'," & objRecordSet.Fields("employeeID") & ",'" & sManager & "','" & objRecordSet.Fields("employeeNumber") & "'"
            'Set oRS = objDB.Execute("EXEC SET_ADUser '" & Replace(objRecordSet.Fields("displayName"),"'","") & "'," & objRecordSet.Fields("employeeID") & ",'" & sManager & "','" & objRecordSet.Fields("employeeNumber") & "'")
            '   oRS.MoveFirst 'this forces load of total recordset


        End If
    End If

    objRecordSet.MoveNext
Loop


Wscript.Echo "After loop"

' Clean up.
objConnection.Close
Set objRootDSE = Nothing
Set objGroup = Nothing
Set objConnection = Nothing
Set objCommand = Nothing
Set objRecordSet = Nothing


'This function sets up DB Connection using specified DSN
  Function DBConnect
  Set objDB = CreateObject("ADODB.Connection")
  objDB.Open strODBC 'change this for each server'
  Set DBConnect = objDB
End Function

Option Explicit

Dim objConnection, objCommand, objRootDSE, strDNSDomain, strQuery
Dim objRecordSet, strDN, objGroup
Dim sEmpID, aManager, sManager
Dim objDB
dim strODBC, oRS

'this constant is used to define ODBC Connection
strODBC  = "Data Source=CMS;Initial Catalog=CMS;Persist Security Info=True;User ID=cms;Password=cms"

' Use ADO to search Active Directory.
Set objConnection = CreateObject("ADODB.Connection")
Set objCommand = CreateObject("ADODB.Command")
objConnection.Provider = "ADsDSOObject"
objConnection.Open "Active Directory Provider"
Set objCommand.ActiveConnection = objConnection

' Search.
strQuery = ";(&(objectCategory=person)(objectClass=user)(name=*));displayName,Manager,employeeID,employeeNumber;subtree"
objCommand.CommandText = strQuery
objCommand.Properties("Page Size") = 100
objCommand.Properties("Timeout") = 30
objCommand.Properties("Cache Results") = False
objCommand.Properties("Sort On") = "sAMAccountName"

Set objRecordSet = objCommand.Execute
If objRecordSet.EOF Then
  Wscript.Echo "No users found"
  objConnection.Close
  Set objRootDSE = Nothing
  Set objConnection = Nothing
  Set objCommand = Nothing
  Set objRecordSet = Nothing
  Wscript.Quit
End If

set objDB = Nothing
set oRS = Nothing

'Connect to database then run the SQL to generate load
Set objDB = DBConnect()

'Wscript.Echo "Before loop"

Do Until objRecordSet.EOF
    'On Error Resume Next

    err.Clear
    sEmpID = objRecordSet.Fields("employeeID")
    aManager = ""
    sManager = ""

    If err.number = 0 Then
        If sEmpID <> "" Then

      on error resume next 'nulls causing problems here
            aManager = split(objRecordSet.Fields("Manager"), ",", -1)

            If len(aManager(0)) > 3 Then
                sManager = right(aManager(0), len(aManager(0)) -3)
            End If

      on error goto 0
      'the replace on displayName is to get rid of the comma in irish names like Louise O'Hanlon which kill the SQL
      WScript.Echo "EXEC SET_ADUser '" & Replace(objRecordSet.Fields("displayName"),"'","") & "'," & objRecordSet.Fields("employeeID") & ",'" & sManager & "','" & objRecordSet.Fields("employeeNumber") & "'"
            'Set oRS = objDB.Execute("EXEC SET_ADUser '" & Replace(objRecordSet.Fields("displayName"),"'","") & "'," & objRecordSet.Fields("employeeID") & ",'" & sManager & "','" & objRecordSet.Fields("employeeNumber") & "'")
            '   oRS.MoveFirst 'this forces load of total recordset


        End If
    End If

    objRecordSet.MoveNext
Loop


Wscript.Echo "After loop"

' Clean up.
objConnection.Close
Set objRootDSE = Nothing
Set objGroup = Nothing
Set objConnection = Nothing
Set objCommand = Nothing
Set objRecordSet = Nothing


'This function sets up DB Connection using specified DSN
  Function DBConnect
  Set objDB = CreateObject("ADODB.Connection")
  objDB.Open strODBC 'change this for each server'
  Set DBConnect = objDB
End Function

SELECT    ReportName,[Path]
             ,Runs
             , DATEDIFF(dd, (select Min(timestart)  from ExecutionLogStorage),GETDATE())+1 AS DaysHistoryPresent
             ,CASE
                WHEN  DATEDIFF(dd, (select Min(timestart)  from ExecutionLogStorage),GETDATE())+1 = 0 OR Runs = 0 THEN 0
                ELSE  Runs/(DATEDIFF(dd, FirstDay,LastDay)+1)
             END AS ApproxRunsPerDay
             , Runs/(DATEDIFF(dd, FirstDay,LastDay)+1)*30  AS ApproxRunsPerMonth
       FROM
       (
          SELECT
             c.Name AS ReportName
             ,c.Path
             ,Count(1) AS Runs
             ,MIN(el.TimeStart) AS FirstDay
             ,MAX(el.TimeStart) AS LastDay
          FROM ExecutionLogStorage EL WITH(NOLOCK)
             LEFT OUTER JOIN Catalog C WITH(NOLOCK) ON (EL.ReportID = C.ItemID)
          WHERE   el.Source <> 6
          AND [Name] NOT IN ('Global', 'Default','dsRptFmt','dsRptFmt_Internal','Dummy')
          GROUP BY c.Name,c.Path
       ) AS a
       ORDER BY
       CASE
          WHEN DATEDIFF(dd, FirstDay,LastDay)+1 = 0.000 OR Runs = 0 THEN 0.000
          ELSE (Runs/ DATEDIFF(dd, (select Min(timestart)  from ExecutionLogStorage),GETDATE())+1)
       END DESC

sp_helptext to get sql behind INFORMATION_SCHEMA views

To see the SQL behind information_schema views

exec sp_helptext  'INFORMATION_SCHEMA.COLUMNS'

Code for a View / Function comination to gernerate random numbers in tSQL

The RAND function does not behave as you would expect in SQL Server. If it is included in a select statement then it will generate a single number and populate every row with that value.

Here is a bit of a complex way of getting around this problem. Wrapping the RAND function in a view and calling via a UDF.

Modify as required but remember be careful of resource usage calling functions in select statement

CREATE VIEW vw_RandNumber_tempDEVOnly
AS

--gerard 28/10 this is a temporary setup to generate random data to allow me to develop a report in a system where we haven't put the required grouping data
SELECT RAND() as RandNumber

ALTER FUNCTION fRandNumber_tempDEVOnly(@Min int, @Max int)
RETURNS float
AS
--gerard 28/10 this is a temporary setup to generate random data to allow me to develop a report in a system where we haven't put the required grouping data

 BEGIN
 RETURN ROUND(@Min + (select RandNumber from vw_RandNumber_tempDEVOnly) * (@Max-@Min),0)
 END


--call like this
 SELECT dbo.fRandNumber_tempDEVOnly(1,3)

SSRS subreports inserting pagebreaks.

It can be frustrating when a subreport (or any report item) inserts pagebreaks a and you have to go through and get the KeepTogether and KeepWithGroup properties correct AND correct at the right level.

KeepWithGroup in the parent report is the key property and the key level is at the static row group the sub report is in for the Parent Report.

If you are having pagebreaks inserted a rule of thumb is

• Make sure KeepTogether is set to False everywhere it is not explicitly required
• KeepWithGroup = After for the report item that is creating the pagebreaks (in this case the sub-report)

SSRS - Add a label listing items selected from a multi select parameter

The following code in ds_Vendor_Type_SelectedList returns a string that can be used in the report heading to describe the values selected in a multi select parameter.

A simple alternative might be to use the VB code
=Join(Parameters!ParameterName.Label, ",")
But in large parameter selections this does not indicate to the user if ALL the parameters have been selected.

If all parameters are selected then the following structure returns 'ALL' or alternatively, if a subset is selected then it lists the actual items. This is a more intuitive and user friendly approach.

The second data set called ds_Vendor_Type_SelectedList is populated based on the user selection of the parameter (@VendorTypes parameter variable)



Parameter Name = @VendorTypes

List populated by following Dataset
ds_VendorType_List

SELECT     VENDTYP_CODE,VENDTYP_DESC
FROM APL_VENDTYP
WHERE INACTIVE = 'N'
ORDER BY VENDTYP_DESC


ds_Vendor_Type_SelectedList

DECLARE @CountSelected AS INT
DECLARE @CountAvailable AS INT
DECLARE @ReturnString AS VARCHAR(MAX)

SET @CountAvailable =
    (SELECT COUNT(VENDTYP_CODE)
    FROM APL_VENDTYP
    WHERE INACTIVE = 'N')
SET @CountSelected =
    (SELECT COUNT(VENDTYP_CODE)
    FROM APL_VENDTYP
    WHERE VENDTYP_CODE  IN (@VendorTypes))

IF @CountAvailable = @CountSelected
    BEGIN
        SET @ReturnString = 'All Vendors Selected'
    END
ELSE
    BEGIN
        SET @ReturnString = 'Vendors Selected : ' +
        (STUFF(( SELECT ', ' + VENDTYP_DESC
        FROM APL_VENDTYP WHERE VENDTYP_CODE IN (@VendorTypes)
        FOR XML PATH('')), 1, 2, ''))
    END
   
SELECT  @ReturnString AS VendorsSelected  

Handy code snippits for exploring the ssrs database

/*
    Ran instances oof reports with the parameters
*/
SELECT    x.TimeStart
        ,c.Name
        ,x.USERNAME
        ,x.Parameters
        ,c.Type
        ,x.TimeDataRetrieval
        ,x.TimeProcessing
        ,x.TimeRendering
        ,x.ByteCount
        ,x.[RowCount]
        ,c.Path
       
               ,'XX' as 'Catelog_Fields_Start_Here'
               ,c.*
               ,'XX' as 'ExecutionLog_Fields_Start_Here'
               ,x.*
          FROM Catalog c
               INNER JOIN
               ExecutionLog x
                 ON c.ItemID = x.ReportID
         WHERE Type = 2 AND x.USERNAME like '%gerard%'
         order by x.TimeStart DESC
/*
    code to get xml of report from content field
*/
        
--The first CTE gets the content as a varbinary(max)
--as well as the other important columns for all reports,
--data sources and shared datasets.
WITH ItemContentBinaries AS
(
    SELECT
    ItemID,Name,[Type]
        ,CASE Type
        WHEN 2 THEN 'Report'
        WHEN 5 THEN 'Data Source'
        WHEN 7 THEN 'Report Part'
        WHEN 8 THEN 'Shared Dataset'
        ELSE 'Other'
    END AS TypeDescription
    ,CONVERT(varbinary(max),Content) AS Content
    FROM ReportServer.dbo.Catalog
    WHERE Type IN (2,5,7,8)
),
--The second CTE strips off the BOM if it exists...
ItemContentNoBOM AS
(
    SELECT
    ItemID,Name,[Type],TypeDescription
    ,CASE
        WHEN LEFT(Content,3) = 0xEFBBBF
        THEN CONVERT(varbinary(max),SUBSTRING(Content,4,LEN(Content)))
    ELSE
            Content
        END AS Content
    FROM ItemContentBinaries
)
--The old outer query is now a CTE to get the content in its xml form only...
,ItemContentXML AS
(
    SELECT
    ItemID,Name,[Type],TypeDescription
    ,CONVERT(xml,Content) AS ContentXML
    FROM ItemContentNoBOM
)
--now use the XML data type to extract the queries, and their command types and text....
SELECT
    ItemID,Name,[Type],TypeDescription,ContentXML
    ,ISNULL(Query.value('(./*:CommandType/text())[1]','nvarchar(1024)'),'Query') AS CommandType
    ,Query.value('(./*:CommandText/text())[1]','nvarchar(max)') AS CommandText
FROM ItemContentXML
--Get all the Query elements (The "*:" ignores any xml namespaces)
CROSS APPLY ItemContentXML.ContentXML.nodes('//*:Query') Queries(Query)

-- dsMain
SELECT
ReportID,
Name AS ReportName,
InstanceName,
COALESCE(C.Path, 'Unknown') AS ItemPath,
UserName,
ExecutionId,
CASE(RequestType)
  WHEN 0 THEN 'Interactive'
  WHEN 1 THEN 'Subscription'
  WHEN 2 THEN 'Refresh Cache'
  ELSE 'Unknown'
END AS RequestType, -- SubscriptionId,
Format, Parameters,
CASE(ReportAction) WHEN 1 THEN 'Render'
  WHEN 2 THEN 'BookmarkNavigation'
  WHEN 3 THEN 'DocumentMapNavigation'
  WHEN 4 THEN 'DrillThrough'
  WHEN 5 THEN 'FindString'
  WHEN 6 THEN 'GetDocumentMap'
  WHEN 7 THEN 'Toggle'
  WHEN 8 THEN 'Sort'
  WHEN 9 THEN 'Execute'
  ELSE 'Unknown'
END AS ItemAction,
TimeStart,
TimeEnd,
TimeDataRetrieval,
TimeProcessing,
TimeRendering,
CASE(Source)
  WHEN 1 THEN 'Live'
  WHEN 2 THEN 'Cache'
  WHEN 3 THEN 'Snapshot' WHEN 4 THEN 'History'
  WHEN 5 THEN 'AdHoc'
  WHEN 6 THEN 'Session'
  WHEN 7 THEN 'Rdce'
  ELSE 'Unknown'
END AS Source,
CASE (Status)

  WHEN 'rsHttpRuntimeClientDisconnectionError' THEN 'HttpRuntimeClientDisconnectionError'
  WHEN 'rsReportServerDatabaseError' THEN 'ReportServerDatabaseError'
  WHEN 'rsProcessingAborted' THEN 'Processing Aborted'
  WHEN 'rsSuccess' THEN 'Success'
  WHEN 'rrRenderingError' THEN 'Rendering Error'
  ELSE 'Unknown'
END AS Status,
ByteCount,
[RowCount],
AdditionalInfo
FROM ExecutionLogStorage EL WITH(NOLOCK)
LEFT OUTER JOIN Catalog C WITH(NOLOCK) ON (EL.ReportID = C.ItemID)
WHERE ReportID = @ReportList
;

--dsSSRSReportList
Select    ItemID
        ,[Name]  
From    [Catalog] c
Where  [Type] = 2
AND [Hidden] <> 1
AND SubString([Path],1,Len([Path])-(CharIndex('/',Reverse([Path]))-1))  = @FolderList
ORDER BY c.[Name]

--dsSSRSFolderList
--Returns a list of the folder structure on SSRS Server
SELECT DISTINCT SubString([Path],1,Len([Path])-(CharIndex('/',Reverse([Path]))-1)) As [Path] From    [Catalog] c



create VIEW [dbo].[vw_REPORT_JWS_SSRS_ReportUsage]
AS
       SELECT    ReportName,[Path]
             ,Runs
             , DATEDIFF(dd, (select Min(timestart)  from ExecutionLogStorage),GETDATE())+1 AS DaysHistoryPresent
             ,CASE
                WHEN  DATEDIFF(dd, (select Min(timestart)  from ExecutionLogStorage),GETDATE())+1 = 0 OR Runs = 0 THEN 0
                ELSE  Runs/(DATEDIFF(dd, FirstDay,LastDay)+1)
             END AS ApproxRunsPerDay
             , Runs/(DATEDIFF(dd, FirstDay,LastDay)+1)*30  AS ApproxRunsPerMonth
       FROM
       (
          SELECT
             c.Name AS ReportName
             ,c.Path
             ,Count(1) AS Runs
             ,MIN(el.TimeStart) AS FirstDay
             ,MAX(el.TimeStart) AS LastDay
          FROM ExecutionLogStorage EL WITH(NOLOCK)
             LEFT OUTER JOIN Catalog C WITH(NOLOCK) ON (EL.ReportID = C.ItemID)
          WHERE   el.Source <> 6
          AND [Name] NOT IN ('Global', 'Default','dsRptFmt','dsRptFmt_Internal','Dummy')
          GROUP BY c.Name,c.Path
       ) AS a
       ORDER BY
       CASE
          WHEN DATEDIFF(dd, FirstDay,LastDay)+1 = 0.000 OR Runs = 0 THEN 0.000
          ELSE (Runs/ DATEDIFF(dd, (select Min(timestart)  from ExecutionLogStorage),GETDATE())+1)
       END DESC
GO
        

SSRS get the run history and parameters used for individual report

USE [ReportServer]
/*
useful code for getting the run history and parameters used of a report
*/
DECLARE @ReportName AS VARCHAR(MAX)
SET @ReportName = '%Report1%'--report name hers surrounded by wildcards

SELECT * FROM
(
    SELECT
    InstanceName,
    COALESCE(C.Path, 'Unknown') AS ItemPath,
    UserName,
    ExecutionId,
    CASE(RequestType)
      WHEN 0 THEN 'Interactive'
      WHEN 1 THEN 'Subscription'
      WHEN 2 THEN 'Refresh Cache'
      ELSE 'Unknown'
    END AS RequestType, -- SubscriptionId,
    Format, Parameters,
    CASE(ReportAction) WHEN 1 THEN 'Render'
      WHEN 2 THEN 'BookmarkNavigation'
      WHEN 3 THEN 'DocumentMapNavigation'
      WHEN 4 THEN 'DrillThrough'
      WHEN 5 THEN 'FindString'
      WHEN 6 THEN 'GetDocumentMap'
      WHEN 7 THEN 'Toggle'
      WHEN 8 THEN 'Sort'
      WHEN 9 THEN 'Execute'
      ELSE 'Unknown'
    END AS ItemAction,
    TimeStart,
    TimeEnd,
    TimeDataRetrieval,
    TimeProcessing,
    TimeRendering,
    CASE(Source)
      WHEN 1 THEN 'Live'
      WHEN 2 THEN 'Cache'
      WHEN 3 THEN 'Snapshot' WHEN 4 THEN 'History'
      WHEN 5 THEN 'AdHoc'
      WHEN 6 THEN 'Session'
      WHEN 7 THEN 'Rdce'
      ELSE 'Unknown'
    END AS Source,
    Status,
    ByteCount,
    [RowCount],
    AdditionalInfo
    FROM ExecutionLogStorage EL WITH(NOLOCK)
    LEFT OUTER JOIN Catalog C WITH(NOLOCK) ON (EL.ReportID = C.ItemID)
)AS a
WHERE ItemPath like @ReportName