/*dstMain*/

SELECT C.[ItemID]
      ,C.[Name]
      ,C.[Path]
   ,C.[Type]
   ,TypeName       = case C.[Type]
           when 1 then 'Folder'
           when 2 then 'Report'
           when 3 then 'Resources'
           when 4 then 'Linked Report'
           when 5 then 'Data Source'
           when 6 then 'Report Model'
           when 7 then 'Report Part (SQL 2008 R2, unverified)'
           when 8 then 'Shared Dataset (SQL 2008 R2)'
          end
       ,ExecutionCount    = COUNT(C.Name)
    ,SubscriptionCount   = COUNT(distinct SUB.[Description])
    ,LastRan      = MAX(el.TimeStart)
       --Total Time
    ,TimeDataRetrievalTotal  = SUM(el.TimeDataRetrieval)
       ,TimeProcessingTotal   = SUM(el.TimeProcessing)
       ,TimeRenderingTotal   = SUM(el.TimeRendering)
       ,TimeTotal     = SUM(el.TimeDataRetrieval)+SUM(el.TimeProcessing)+SUM(el.TimeRendering)
    --Average Time
     ,TimeDataRetrievalAverage = AVG(el.TimeDataRetrieval)
       ,TimeProcessingAverage  = AVG(el.TimeProcessing)
       ,TimeRenderingAverage  = AVG(el.TimeRendering)
       ,TimeTotalAverage   = AVG(el.TimeDataRetrieval)+AVG(el.TimeProcessing)+AVG(el.TimeRendering)
    --Max Time
     ,TimeDataRetrievalMax  = MAX(el.TimeDataRetrieval)
       ,TimeProcessingMax   = MAX(el.TimeProcessing)
       ,TimeRenderingMax   = MAX(el.TimeRendering)
       ,TimeTotalMax    = MAX(el.TimeDataRetrieval)+MAX(el.TimeProcessing)+MAX(el.TimeRendering)
    --Min Time
     ,TimeDataRetrievalMin  = MIN(el.TimeDataRetrieval)
       ,TimeProcessingMin   = MIN(el.TimeProcessing)
       ,TimeRenderingMin   = MIN(el.TimeRendering)
       ,TimeTotalMin    = MIN(el.TimeDataRetrieval)+MIN(el.TimeProcessing)+MIN(el.TimeRendering)
    ,ByteCountSum    = SUM(el.ByteCount)
       ,RowCountSum     = SUM(el.[RowCount])
   ,CreatedBy     = U.UserName
      ,CreationDate     = C.CreationDate
      ,ModifiedBy     = UM.UserName
      ,ModifiedDate     = C.ModifiedDate
  FROM dbo.Catalog C
  JOIN dbo.Users U
    ON C.CreatedByID = U.UserID
  JOIN dbo.Users UM
    ON c.ModifiedByID = UM.UserID
LEFT JOIN ExecutionLog el
       ON C.ItemID = el.ReportID
LEFT JOIN dbo.Subscriptions SUB
ON SUB.Report_OID = c.ItemID
WHERE C.[Type] = 2 --Reports
AND c.ItemID = @prmReport
AND el.TimeStart >= @prmDate
--AND Name like @ReportName
GROUP BY
C.[ItemID]
,C.[Name]
,C.[Path]
,C.[Type]
,U.UserName
,C.CreationDate
,UM.UserName
,C.ModifiedDate

/*dstReportSubscriptions*/
SELECT USR.UserName AS SubscriptionOwner, SUB.ModifiedDate, SUB.Description, SUB.EventType, SUB.DeliveryExtension, SUB.LastStatus, SUB.LastRunTime, SCH.NextRunTime,
                  SCH.Name AS ScheduleName, CAT.Path AS ReportPath, CAT.Description AS ReportDescription, SUB.SubscriptionID, SUB.MatchData,
ExecuteText = 'exec dbo.AddEvent @EventType=''SharedSchedule'', @EventData=' + cast(SUB.MatchData as nvarchar(max)) + ''''
FROM     Subscriptions AS SUB INNER JOIN
                  Users AS USR ON SUB.OwnerID = USR.UserID INNER JOIN
                  Catalog AS CAT ON SUB.Report_OID = CAT.ItemID INNER JOIN
                  ReportSchedule AS RS ON SUB.Report_OID = RS.ReportID AND SUB.SubscriptionID = RS.SubscriptionID INNER JOIN
                  Schedule AS SCH ON RS.ScheduleID = SCH.ScheduleID
WHERE  (CAT.Type = 2) AND (CAT.ItemID = @prmReport)
ORDER BY SubscriptionOwner, ReportPath

/*dstDataSource*/
DECLARE @SQL nvarchar(max)
Declare @Namespace NVARCHAR(500)
SELECT @Namespace= SUBSTRING(
                   x.CatContent  
                  ,x.CIndex
                  ,CHARINDEX('"',x.CatContent,x.CIndex+7) - x.CIndex
                )
      FROM
     (
         SELECT CatContent = CONVERT(NVARCHAR(MAX),CONVERT(XML,CONVERT(VARBINARY(MAX),C.Content)))
                ,CIndex    = CHARINDEX('xmlns="',CONVERT(NVARCHAR(MAX),CONVERT(XML,CONVERT(VARBINARY(MAX),C.Content))))
           FROM Reportserver.dbo.Catalog C
          WHERE C.Content is not null
            AND C.Type  = 2
     ) X

SELECT @Namespace = REPLACE(@Namespace,'xmlns="','') + ''
SELECT @SQL = 'WITH XMLNAMESPACES ( DEFAULT ''' + @Namespace +''', ''http://schemas.microsoft.com/SQLServer/reporting/reportdesigner'' AS rd )
                SELECT  ReportName         = name
                       ,DataSourceName     = x.value(''(@Name)[1]'', ''VARCHAR(250)'') 
                       ,DataProvider     = x.value(''(ConnectionProperties/DataProvider)[1]'',''VARCHAR(250)'')
                       ,ConnectionString = x.value(''(ConnectionProperties/ConnectString)[1]'',''VARCHAR(250)'')
                  FROM (  SELECT C.Name,CONVERT(XML,CONVERT(VARBINARY(MAX),C.Content)) AS reportXML
                           FROM  dbo.Catalog C
                          WHERE  C.Content is not null
                            AND  C.Type  = 2
                            AND  C.ItemID  = ''' + @prmReport + '''
                  ) a
                  CROSS APPLY reportXML.nodes(''/Report/DataSources/DataSource'') r ( x )
                ORDER BY name ;'

EXEC(@SQL)

/*dstDataSets*/
DECLARE @SQL nvarchar(max)
Declare @Namespace NVARCHAR(500)
SELECT @Namespace= SUBSTRING(
                   x.CatContent  
                  ,x.CIndex
                  ,CHARINDEX('"',x.CatContent,x.CIndex+7) - x.CIndex
                )
      FROM
     (
         SELECT CatContent = CONVERT(NVARCHAR(MAX),CONVERT(XML,CONVERT(VARBINARY(MAX),C.Content)))
                ,CIndex    = CHARINDEX('xmlns="',CONVERT(NVARCHAR(MAX),CONVERT(XML,CONVERT(VARBINARY(MAX),C.Content))))
           FROM Reportserver.dbo.Catalog C
          WHERE C.Content is not null
            AND C.Type  = 2
     ) X

SELECT @Namespace = REPLACE(@Namespace,'xmlns="','') + ''
SELECT @SQL = 'WITH XMLNAMESPACES ( DEFAULT ''' + @Namespace + ''', ''http://schemas.microsoft.com/SQLServer/reporting/reportdesigner'' AS rd )
SELECT  distinct
        ReportName        = name
       ,DataSetName        = x.value(''(@Name)[1]'', ''VARCHAR(250)'') 
       ,DataSourceName    = x.value(''(Query/DataSourceName)[1]'',''VARCHAR(250)'')
       ,CommandText        = x.value(''(Query/CommandText)[1]'',''VARCHAR(max)'')
       --,Fields            = df.value(''(@Name)[1]'',''VARCHAR(250)'')
       --,DataField        = df.value(''(DataField)[1]'',''VARCHAR(250)'')
       --,DataType        = df.value(''(rd:TypeName)[1]'',''VARCHAR(250)'')
  FROM (  SELECT C.Name,CONVERT(XML,CONVERT(VARBINARY(MAX),C.Content)) AS reportXML
           FROM  dbo.Catalog C
          WHERE  C.Content is not null
            AND  C.Type = 2
            AND  C.ItemID  = ''' + @prmReport + '''
       ) a
  CROSS APPLY reportXML.nodes(''/Report/DataSets/DataSet'') r ( x )
  --CROSS APPLY x.nodes(''Fields/Field'') f(df) 
ORDER BY name '
EXEC(@SQL)

/*dstTimings*/
DECLARE @prmReport nvarchar(max) = '6D0597A6-5A64-4AF5-8AB8-AE5E29B78363'
DECLARE @prmDate datetime = '2017-01-01'
SELECT  [Typeorder]=1
       ,[Type] = 'Max'
       ,TimeDataRetrievalMax  = MAX(el.TimeDataRetrieval)
       ,TimeProcessingMax   = MAX(el.TimeProcessing)
       ,TimeRenderingMax   = MAX(el.TimeRendering)
       ,TimeTotalMax    = MAX(el.TimeDataRetrieval)+MAX(el.TimeProcessing)+MAX(el.TimeRendering)
  FROM dbo.Catalog C
LEFT JOIN ExecutionLog el
       ON C.ItemID = el.ReportID
WHERE C.[Type] = 2 --Reports
AND cast(c.ItemID as nvarchar(max)) = @prmReport
AND el.TimeStart >= @prmDate
UNION ALL
SELECT  [Typeorder]=2
       ,[Type] = 'Min'
       ,TimeDataRetrievalMax  = MIN(el.TimeDataRetrieval)
       ,TimeProcessingMax   = MIN(el.TimeProcessing)
       ,TimeRenderingMax   = MIN(el.TimeRendering)
       ,TimeTotalMax    = MIN(el.TimeDataRetrieval)+MIN(el.TimeProcessing)+MIN(el.TimeRendering)
  FROM dbo.Catalog C
LEFT JOIN ExecutionLog el
       ON C.ItemID = el.ReportID
WHERE C.[Type] = 2 --Reports
AND cast(c.ItemID as nvarchar(max)) = @prmReport
AND el.TimeStart >= @prmDate
UNION ALL
SELECT  [Typeorder]=3
       ,[Type] = 'Average'
       ,TimeDataRetrievalMax  = AVG(el.TimeDataRetrieval)
       ,TimeProcessingMax   = AVG(el.TimeProcessing)
       ,TimeRenderingMax   = AVG(el.TimeRendering)
       ,TimeTotalMax    = AVG(el.TimeDataRetrieval)+AVG(el.TimeProcessing)+AVG(el.TimeRendering)
  FROM dbo.Catalog C
LEFT JOIN ExecutionLog el
       ON C.ItemID = el.ReportID
WHERE C.[Type] = 2 --Reports
AND cast(c.ItemID as nvarchar(max)) = @prmReport
AND el.TimeStart >= @prmDate
UNION ALL
SELECT  [Typeorder]=4
       ,[Type] = 'Cached'
       ,TimeDataRetrievalMax  = AVG(el.TimeDataRetrieval)
       ,TimeProcessingMax   = AVG(el.TimeProcessing)
       ,TimeRenderingMax   = AVG(el.TimeRendering)
       ,TimeTotalMax    = AVG(el.TimeDataRetrieval)+AVG(el.TimeProcessing)+AVG(el.TimeRendering)
  FROM dbo.Catalog C
LEFT JOIN [dbo].[ExecutionLog3] el
       ON C.[Path] = el.ItemPath
WHERE C.[Type] = 2 --Reports
AND cast(c.ItemID as nvarchar(max)) = @prmReport
AND el.TimeStart >= @prmDate
AND [Source] = 'Cache'  

Report Log 3 Report Subscriptions

SELECT USR.UserName AS SubscriptionOwner, b.name AS JobName, CAT.Name AS ReportName, SUB.ModifiedDate, SUB.Description, SUB.EventType, SUB.DeliveryExtension,
                  SUB.LastStatus, SUB.LastRunTime, SCH.NextRunTime, SCH.Name AS ScheduleName, CAT.ItemID, CAT.Path AS ReportPath, CAT.Description AS ReportDescription,
                  SUB.SubscriptionID, SUB.MatchData, 'exec dbo.AddEvent @EventType=''SharedSchedule'', @EventData=' + CAST(SUB.MatchData AS nvarchar(MAX))
                  + '''' AS ExecuteText
FROM     Subscriptions AS SUB INNER JOIN
                  Users AS USR ON SUB.OwnerID = USR.UserID INNER JOIN
                  Catalog AS CAT ON SUB.Report_OID = CAT.ItemID INNER JOIN
                  ReportSchedule AS RS ON SUB.Report_OID = RS.ReportID AND SUB.SubscriptionID = RS.SubscriptionID INNER JOIN
                  Schedule AS SCH ON RS.ScheduleID = SCH.ScheduleID LEFT OUTER JOIN
                  msdb.dbo.sysjobs AS b ON CAST(RS.ScheduleID AS nvarchar(1000)) = CAST(b.name AS nvarchar(1000))
WHERE  (CAT.Type = 2) AND (replace(CAT.Path, CAT.Name, '') = CASE WHEN @prmPath = 'All' THEN replace(CAT.Path, CAT.Name, '') ELSE @prmPath END)
ORDER BY SubscriptionOwner, ReportPath

SSRS Log: 2 Report Summary

/*dstMain*/
SELECT    
      DATEPART(Year, TimeStart) AS ReportYear
    , DATEPART(Month, TimeStart) AS ReportMonth
    , DATEPART(Day, TimeStart) AS ReportDay
    , DATEPART(Hour, TimeStart) AS ReportHour
    , format(TimeStart,'yyyy-MM-dd') as FullDate
    , Type
    , COUNT(Name) AS ExecutionCount
    , SUM(TimeDataRetrieval) AS TimeDataRetrievalSum
    , SUM(TimeProcessing) AS TimeProcessingSum
    , SUM(TimeRendering) AS TimeRenderingSum
    , SUM(TimeDataRetrieval) + SUM(TimeProcessing) + SUM(TimeRendering)  As TotalTime
    , SUM(ByteCount) AS ByteCountSum
    , SUM([RowCount]) AS RowCountSum
FROM
(
    SELECT TimeStart, Catalog.Type, Catalog.Name, TimeDataRetrieval,
    TimeProcessing, TimeRendering, ByteCount, [RowCount]
    FROM Catalog
 INNER JOIN ExecutionLog ON Catalog.ItemID =
 ExecutionLog.ReportID LEFT OUTER JOIN
    Users ON Catalog.CreatedByID = Users.UserID
    WHERE ExecutionLog.TimeStart >= @prmDate
) AS RE
GROUP BY
      DATEPART(Year, TimeStart)
    , DATEPART(Month, TimeStart)
    , DATEPART(Day, TimeStart)
    , DATEPART(Hour, TimeStart)
   , format(TimeStart,'yyyy-MM-dd')
    , Type
ORDER BY
      ReportYear
    , ReportMonth
    , ReportDay
    , ReportHour
    , format(TimeStart,'yyyy-MM-dd')
    , Type

/*dstTop5Used*/
--Top 5 used reports
SELECT TOP 5  
      COUNT(Name) AS ExecutionCount
    , Name
    , SUM(TimeDataRetrieval) AS TimeDataRetrievalSum
    , SUM(TimeProcessing) AS TimeProcessingSum
    , SUM(TimeRendering) AS TimeRenderingSum
   ,SUM(TimeDataRetrieval)+SUM(TimeProcessing)+SUM(TimeRendering) As TotalTime
    , SUM(ByteCount) AS ByteCountSum
    , SUM([RowCount]) AS RowCountSum
FROM
(
    SELECT TimeStart, Catalog.Type, Catalog.Name,
      TimeDataRetrieval, TimeProcessing, TimeRendering, ByteCount, [RowCount]
    FROM
    Catalog INNER JOIN ExecutionLog ON Catalog.ItemID = ExecutionLog.ReportID
    WHERE ExecutionLog.TimeStart >= @prmDate AND Type = 2
) AS RE
GROUP BY
        Name
ORDER BY
        COUNT(Name) DESC
       ,Name

/*dstUnusedReports*/
SELECT distinct Name, Path, UserName
FROM Catalog INNER JOIN dbo.Users ON Catalog.CreatedByID = Users.UserID
WHERE Type = 2 AND
    Catalog.ItemID NOT IN
    (
        SELECT ExecutionLog.ReportID
        FROM ExecutionLog
        WHERE ExecutionLog.TimeStart >= @prmDate
    )
    ORDER BY Name

SSRS Log:1 Report List

/*dstMainQuery*/
SELECT C.[ItemID]
      ,C.[Name]
      ,C.[Path]
   ,C.[Type]
   ,TypeName       = case C.[Type]
           when 1 then 'Folder'
           when 2 then 'Report'
           when 3 then 'Resources'
           when 4 then 'Linked Report'
           when 5 then 'Data Source'
           when 6 then 'Report Model'
           when 7 then 'Report Part (SQL 2008 R2, unverified)'
           when 8 then 'Shared Dataset (SQL 2008 R2)'
          end
       ,ExecutionCount    = COUNT(C.Name)
    ,SubscriptionCount   = COUNT(distinct SUB.[Description])
    ,LastRan      = MAX(el.TimeStart)
       --Total Time
    ,TimeDataRetrievalTotal  = SUM(el.TimeDataRetrieval)
       ,TimeProcessingTotal   = SUM(el.TimeProcessing)
       ,TimeRenderingTotal   = SUM(el.TimeRendering)
       ,TimeTotal     = SUM(el.TimeDataRetrieval)+SUM(el.TimeProcessing)+SUM(el.TimeRendering)
    --Average Time
     ,TimeDataRetrievalAverage = AVG(el.TimeDataRetrieval)
       ,TimeProcessingAverage  = AVG(el.TimeProcessing)
       ,TimeRenderingAverage  = AVG(el.TimeRendering)
       ,TimeTotalAverage   = AVG(el.TimeDataRetrieval)+AVG(el.TimeProcessing)+AVG(el.TimeRendering)
    --Max Time
     ,TimeDataRetrievalMax  = MAX(el.TimeDataRetrieval)
       ,TimeProcessingMax   = MAX(el.TimeProcessing)
       ,TimeRenderingMax   = MAX(el.TimeRendering)
       ,TimeTotalMax    = MAX(el.TimeDataRetrieval)+MAX(el.TimeProcessing)+MAX(el.TimeRendering)
    --Min Time
     ,TimeDataRetrievalMin  = MIN(el.TimeDataRetrieval)
       ,TimeProcessingMin   = MIN(el.TimeProcessing)
       ,TimeRenderingMin   = MIN(el.TimeRendering)
       ,TimeTotalMin    = MIN(el.TimeDataRetrieval)+MIN(el.TimeProcessing)+MIN(el.TimeRendering)
    ,ByteCountSum    = SUM(el.ByteCount)
       ,RowCountSum     = SUM(el.[RowCount])
   ,CreatedBy     = U.UserName
      ,CreationDate     = C.CreationDate
      ,ModifiedBy     = UM.UserName
      ,ModifiedDate     = C.ModifiedDate
  FROM dbo.Catalog C
  JOIN dbo.Users U
    ON C.CreatedByID = U.UserID
  JOIN dbo.Users UM
    ON c.ModifiedByID = UM.UserID
LEFT JOIN ExecutionLog el
       ON C.ItemID = el.ReportID
LEFT JOIN dbo.Subscriptions SUB
ON SUB.Report_OID = c.ItemID
WHERE C.[Type] = 2 --Reports
AND replace(C.[Path],C.[Name],'') = case when @prmPath = 'All' then replace(C.[Path],C.[Name],'') else @prmPath end
AND el.TimeStart >= @prmDate
--AND Name like @ReportName
GROUP BY
C.[ItemID]
,C.[Name]
,C.[Path]
,C.[Type]
,U.UserName
,C.CreationDate
,UM.UserName
,C.ModifiedDate

/*dstFilterReportPath*/
SELECT OrderId=1,'All' as ShortPath
UNION ALL
SELECT distinct
       OrderId = 2
    ,ShortPath = replace(C.[Path],C.[Name],'')
FROM dbo.Catalog C
WHERE [Type] = 2
order by OrderId, ShortPath

/*dstConstants*/
SELECT @@SERVERNAME AS ServerName, DB_NAME() AS DatabaseName, @@VERSION AS Version, GETDATE() AS ExecutionTime

Example

DECLARE @ReportPath varchar(max) = '/Italy/Strade_Province'
--DECLARE @ReportPath varchar(max) = '/Italy/Strade_Region'
DECLARE @ReportDate datetime = dateadd(day,-5,getdate())
declare @ParameterName varchar(50)
declare @ParameterValue varchar(max)
declare @S varchar(max)
declare @R varchar(max)
declare @rw int
CREATE TABLE #tempUncachedReports
(
RowNo int
,ReportName varchar(1000)
,ParameterValue varchar(max)
)
CREATE TABLE #temp
(
 ReportName varchar(1000)
,ParameterName varchar(1000)
,ParameterValue varchar(max)
,RowNo int
)
/*Insert uncached not in report*/
INSERT INTO #tempUncachedReports
SELECT rank() over(order by ItemPath,ParameterValues) as rowNo,ItemPath,ParameterValues
from (
select distinct ItemPath,replace(replace(replace(replace(replace(cast([Parameters] as nvarchar(max)),'&','#'),'%5B','['),'%5D',']'),'%26','&'),'%20',' ') as ParameterValues
from dbo.ExecutionLog3
where Timestart >= @ReportDate
and ItemPath like @ReportPath
--and format = 'RPL'
and Source = 'Live'
) as a

DECLARE @LoopCounter int = 1
DECLARE @MaxVal int = (select max(RowNo) from #tempUncachedReports)
WHILE (@LoopCounter <=@MaxVal)
BEGIN
    SET @R = (select ReportName from #tempUncachedReports where RowNo=@LoopCounter)
 SET @S = (select ParameterValue from #tempUncachedReports where RowNo=@LoopCounter)
 SET @rw = (select RowNo from #tempUncachedReports where RowNo=@LoopCounter)
 while len(@S) > 0
  begin
    set @ParameterName = left(@S, charindex('=', @S+'=')-1)
    set @ParameterValue = replace(left(@S, charindex('#', @S+'#')-1), @ParameterName + '=','')
    set @S = stuff(@S, 1, charindex('#', @S+'#'), '')
   
    insert into #temp
    (
    ReportName
    ,ParameterName
    ,ParameterValue
    ,RowNo
    )
    SELECT @R as ReportName,@ParameterName as ParameterName,@ParameterValue as Parameter,@rw as RowNo
  end
  SET @LoopCounter += 1
END
--Takes into account multiselect parameters
select Main.ReportName, Main.ParameterName, ParameterValue=replace(Left(Main.ParameterValue,Len(Main.ParameterValue)-1),'&amp;','&'),RowNo
from
(
select distinct ReportName, ParameterName,RowNo,
replace(replace((
 SELECT a1.ParameterValue + ',' as ParameterValue
 from #temp a1
 where a1.ReportName = a2.ReportName
 and   a1.ParameterName = a2.ParameterName
 and   a1.RowNo = a2.RowNo
 ORDER BY a1.ParameterName
 FOR XML Path('')
),'<ParameterValue>',''),'</ParameterValue>','') as ParameterValue
from #temp a2
) Main
order by RowNo,ParameterName
--Regular, no multiselect
--select * from #temp
DROP TABLE #temp
DROP TABLE #tempUncachedReports

Single cache script

USE [ReportingSubscriptions]
GO
IF OBJECT_ID('tempdb..#temp') IS NOT NULL
 DROP TABLE #temp
IF OBJECT_ID('tempdb..#tempUncachedReports') IS NOT NULL
 DROP TABLE #tempUncachedReports
DECLARE @ReportPath varchar(max) = '/Italy/Strade_Province'
DECLARE @ReportDate datetime = dateadd(day,-1,getdate())
declare @ParameterName varchar(50)
declare @ParameterValue varchar(max)
declare @S varchar(max)
declare @R varchar(max)
declare @rw int
CREATE TABLE #tempUncachedReports
(
RowNo int
,ReportName varchar(1000)
,ParameterValue varchar(max)
)
CREATE TABLE #temp
(
 ReportName varchar(1000)
,ParameterName varchar(1000)
,ParameterValue varchar(max)
,RowNo int
)
/*Insert uncached not in report*/
INSERT INTO #tempUncachedReports
SELECT rank() over(order by ItemPath,ParameterValues) as rowNo,ItemPath,ParameterValues
from (
select distinct ItemPath,replace(replace(replace(replace(replace(cast([Parameters] as nvarchar(max)),'&','#'),'%5B','['),'%5D',']'),'%26','&'),'%20',' ') as ParameterValues
from [ReportServer].dbo.ExecutionLog3
where Timestart >= @ReportDate
and ItemPath like @ReportPath
--and format = 'RPL'
and Source = 'Live'
) as a

DECLARE @LoopCounter int = 1
DECLARE @MaxVal int = (select max(RowNo) from #tempUncachedReports)
WHILE (@LoopCounter <=@MaxVal)
BEGIN
    SET @R = (select ReportName from #tempUncachedReports where RowNo=@LoopCounter)
 SET @S = (select ParameterValue from #tempUncachedReports where RowNo=@LoopCounter)
 SET @rw = (select RowNo from #tempUncachedReports where RowNo=@LoopCounter)
 while len(@S) > 0
  begin
    set @ParameterName = left(@S, charindex('=', @S+'=')-1)
    set @ParameterValue = replace(left(@S, charindex('#', @S+'#')-1), @ParameterName + '=','')
    set @S = stuff(@S, 1, charindex('#', @S+'#'), '')
   
    insert into #temp
    (
    ReportName
    ,ParameterName
    ,ParameterValue
    ,RowNo
    )
    SELECT @R as ReportName,@ParameterName as ParameterName,@ParameterValue as Parameter,@rw as RowNo
  end
  SET @LoopCounter += 1
END
--select * from #temp
--select * from #tempUncachedReports
set @rw = (select max(RowNo) from #temp)
set @LoopCounter = 1
WHILE (@LoopCounter <=@MaxVal)
BEGIN
INSERT INTO [Italy].[ALD_Province]
           ([WholesalerWholesaler]
           ,[WholesalerSubGroup]
           ,[MarketCategory]
           ,[PackCategory]
           ,[GenericStatus]
           ,[VALUEUNITS]
           ,[Region]
           ,[PERIOD]
           ,[RANGE]
           ,[MICROBRICK]
           ,[Province]
           ,[MICROBRICKSelected]
           ,[PrescriptionStatus]
           ,[ReimbursementStatus]
           ,[Manufacturer]
           ,[ProductType]
           ,[Product]
           ,[ActiveFlag]
           ,[Ordering]
           ,[NewRow]
           ,[WID]
     )
select distinct
           WholesalerWholesaler
           ,WholesalerSubGroup
           ,MarketCategory
           ,PackCategory
           ,GenericStatus
           ,VALUEUNITS
           ,Region
           ,PERIOD
           ,[RANGE]
           ,MICROBRICK
           ,Province
           ,MICROBRICKSelected
           ,PrescriptionStatus
           ,ReimbursementStatus
           ,Manufacturer = null
           ,ProductType = null
           ,Product = null
           ,1 as ActiveFlag
           ,1 as Ordering
           ,1 as NewRow
           ,CONVERT(UNIQUEIDENTIFIER, WID) AS WID
from
(
  select ParameterValue as value, ParameterName as columnname
  from #temp
  WHERE RowNo = @LoopCounter
) d
pivot
(
  max(value)
  for columnname in (
  GenericStatus
  ,Manufacturer
  ,MarketCategory
  ,MICROBRICK
  ,MICROBRICKSelected
  ,PackCategory
  ,PERIOD
  ,PrescriptionStatus
  ,Product
  ,ProductType
  ,Province
  ,RANGE
  ,Region
  ,ReimbursementStatus
  ,VALUEUNITS
  ,WholesalerSubGroup
  ,WholesalerWholesaler
  ,WID
  )
) piv
where not exists(
select *
from [Italy].[ALD_Province] a
WHERE
a.WholesalerWholesaler = piv.WholesalerWholesaler
and isnull(a.WholesalerSubGroup,'') = isnull(piv.WholesalerSubGroup,'')
and isnull(a.MarketCategory,'') = isnull(piv.MarketCategory,'')
and isnull(a.PackCategory,'') = isnull(piv.PackCategory,'')
and isnull(a.GenericStatus,'') = isnull(piv.GenericStatus,'')
and isnull(a.VALUEUNITS,'') = isnull(piv.VALUEUNITS,'')
and isnull(a.Region,'') = isnull(piv.Region,'')
and isnull(a.PERIOD,'') = isnull(piv.PERIOD,'')
and isnull(a.[RANGE],'') = isnull(piv.[RANGE],'')
and isnull(a.MICROBRICK,'') = isnull(piv.MICROBRICK,'')
and isnull(a.Province,'') = isnull(piv.Province,'')
and isnull(a.MICROBRICKSelected,'') = isnull(piv.MICROBRICKSelected,'')
and isnull(a.PrescriptionStatus,'') = isnull(piv.PrescriptionStatus,'')
and isnull(a.ReimbursementStatus,'') = isnull(piv.ReimbursementStatus,'')
and isnull(a.Manufacturer,'') = isnull(piv.Manufacturer,'')
and isnull(a.ProductType,'') = isnull(piv.ProductType,'')
and isnull(a.Product,'') = isnull(piv.Product,'')
AND TRY_CONVERT(UNIQUEIDENTIFIER, piv.WID) is not null
)
--)
 SET @LoopCounter += 1
END

DROP TABLE #temp
DROP TABLE #tempUncachedReports

gET lIST CACHE SCRIPT 1

DECLARE @ReportPath varchar(max) = '/Italy/Strade_Province'
--DECLARE @ReportPath varchar(max) = '/Italy/Strade_Region'
DECLARE @ReportDate datetime = dateadd(day,-5,getdate())
declare @ParameterName varchar(50)
declare @ParameterValue varchar(max)
declare @S varchar(max)
declare @R varchar(max)
declare @rw int
CREATE TABLE #tempUncachedReports
(
RowNo int
,ReportName varchar(1000)
,ParameterValue varchar(max)
)
CREATE TABLE #temp
(
 ReportName varchar(1000)
,ParameterName varchar(1000)
,ParameterValue varchar(max)
,RowNo int
)
/*Insert uncached not in report*/
INSERT INTO #tempUncachedReports
SELECT rank() over(order by ItemPath,ParameterValues) as rowNo,ItemPath,ParameterValues
from (
select distinct ItemPath,replace(replace(replace(replace(replace(cast([Parameters] as nvarchar(max)),'&','#'),'%5B','['),'%5D',']'),'%26','&'),'%20',' ') as ParameterValues
from dbo.ExecutionLog3
where Timestart >= @ReportDate
and ItemPath like @ReportPath
--and format = 'RPL'
and Source = 'Live'
) as a

DECLARE @LoopCounter int = 1
DECLARE @MaxVal int = (select max(RowNo) from #tempUncachedReports)
WHILE (@LoopCounter <=@MaxVal)
BEGIN
    SET @R = (select ReportName from #tempUncachedReports where RowNo=@LoopCounter)
 SET @S = (select ParameterValue from #tempUncachedReports where RowNo=@LoopCounter)
 SET @rw = (select RowNo from #tempUncachedReports where RowNo=@LoopCounter)
 while len(@S) > 0
  begin
    set @ParameterName = left(@S, charindex('=', @S+'=')-1)
    set @ParameterValue = replace(left(@S, charindex('#', @S+'#')-1), @ParameterName + '=','')
    set @S = stuff(@S, 1, charindex('#', @S+'#'), '')
   
    insert into #temp
    (
    ReportName
    ,ParameterName
    ,ParameterValue
    ,RowNo
    )
    SELECT @R as ReportName,@ParameterName as ParameterName,@ParameterValue as Parameter,@rw as RowNo
  end
  SET @LoopCounter += 1
END
--Takes into account multiselect parameters
select Main.ReportName, Main.ParameterName, ParameterValue=replace(Left(Main.ParameterValue,Len(Main.ParameterValue)-1),'&amp;','&'),RowNo
from
(
select distinct ReportName, ParameterName,RowNo,
replace(replace((
 SELECT a1.ParameterValue + ',' as ParameterValue
 from #temp a1
 where a1.ReportName = a2.ReportName
 and   a1.ParameterName = a2.ParameterName
 and   a1.RowNo = a2.RowNo
 ORDER BY a1.ParameterName
 FOR XML Path('')
),'<ParameterValue>',''),'</ParameterValue>','') as ParameterValue
from #temp a2
) Main
order by RowNo,ParameterName
--Regular, no multiselect
--select * from #temp
DROP TABLE #temp
DROP TABLE #tempUncachedReports