Creating an Azure SQL Server DB and Connecting via Management Studio

To create a new SQL Server database instance in Azure follow these steps:

1. Click the SQL databases instance

2. Select Add and enter all database details

You have now created your first Azure SQL Server database. Now in order to connect for Management studio so you can edit the database follow the following steps


3. Click on the database you have created and on the top select Set server firewall

Add Client IP address and set allow access to Azure Services to ON

4. Finally get the details to connect to the server by going to the properties of the database

5. Enter these details with the password you have set into management studio and you should now be able to connect.

Resize iFrame dynamically based on content

iFrame will resize dynamically for content on the same domain.

 

 

<style>.woocommerce-product-gallery{ opacity: 1 !important; }</style>

<script type='text/javascript' src='http://ajax.googleapis.com/ajax/libs/jquery/1.3.2/jquery.min.js?ver=1.3.2'></script>
<script type='text/javascript'>
   
    $(function(){
   
        var iFrames = $('iframe');
     
            function iResize() {
           
                    for (var i = 0, j = iFrames.length; i < j; i++) {
                      iFrames[i].style.height = iFrames[i].contentWindow.document.body.offsetHeight + 'px';}
                }
               
                if ($.browser.safari || $.browser.opera) {
               
                   iFrames.load(function(){
                       setTimeout(iResize, 0);
               });
           
                   for (var i = 0, j = iFrames.length; i < j; i++) {
                                var iSource = iFrames[i].src;
                                iFrames[i].src = '';
                                iFrames[i].src = iSource;
               }
              
                } else {
                   iFrames.load(function() {
                       this.style.height = this.contentWindow.document.body.offsetHeight + 'px';
                   });
                }
       
        });

</script>

 

 

 

<style>.woocommerce-product-gallery{ opacity: 1 !important; }</style>

<iframe src="content.html" class="iframe" scrolling="no" frameborder="0"></iframe>

/*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