Raport subscriptii reporting services

Configurare noua (How To)

Situatie

Mai jos aveti o procedura ce se ruleaza pe serverul unde este instalat Reporting Servies, pe baza ReportServer, si procedura returneaza un raport cu toate subscriptiile setate pentru rapoarte. Ca si coloane returneaza: calea in care este raportul, data la care a fost facuta subscriptia, descrierea, ultimul status, tipul de livrare al subscriptiei (mail, fisier, etc), parametrii pe care ii are subscriptia.

Solutie

create procedure BD_ReportsSubscriptions_prc

as

WITH

[Sub_Parameters] AS
(
SELECT [SubscriptionID], [Parameters] = CONVERT(XML,a.[Parameters])
FROM [Subscriptions] a
),

[MySubscriptions] AS
(
SELECT DISTINCT
[SubscriptionID],
[ParameterName] = QUOTENAME(p.value(‘(Name)[1]’, ‘nvarchar(max)’)),
[ParameterValue] = p.value(‘(Value)[1]’, ‘nvarchar(max)’)
FROM
[Sub_Parameters] a
CROSS APPLY [Parameters].nodes(‘/ParameterValues/ParameterValue’) t(p)
),

[SubscriptionsAnalysis] AS
(
SELECT
a.[SubscriptionID],
a.[ParameterName],
[ParameterValue] =
(SELECT
STUFF((
SELECT [ParameterValue] + ‘, ‘ as [text()]
FROM [MySubscriptions]
WHERE
[SubscriptionID] = a.[SubscriptionID]
AND [ParameterName] = a.[ParameterName]
FOR XML PATH(”)
),1, 0, ”)
+”)
FROM [MySubscriptions] a
GROUP BY a.[SubscriptionID],a.[ParameterName]
)

SELECT
a.[SubscriptionID],
c.[UserName] AS Owner,
b.Name,
b.Path,
a.[Locale],
a.[InactiveFlags],
d.[UserName] AS Modified_by,
a.[ModifiedDate],
a.[Description],
a.[LastStatus],
a.[EventType],
a.[LastRunTime],
a.[DeliveryExtension],
a.[Version],
e.[ParameterName],
LEFT(e.[ParameterValue],LEN(e.[ParameterValue])-1) as [ParameterValue],
SUBSTRING(b.PATH,2,LEN(b.PATH)-(CHARINDEX(‘/’,REVERSE(b.PATH))+1)) AS ProjectName

FROM [Subscriptions] a
INNER JOIN [Catalog] AS b ON a.[Report_OID] = b.[ItemID]
LEFT OUTER JOIN [Users] AS c ON a.[OwnerID] = c.[UserID]
LEFT OUTER JOIN [Users] AS d ON a.MODIFIEDBYID = d.Userid
LEFT OUTER JOIN [SubscriptionsAnalysis] AS e ON a.SubscriptionID = e.SubscriptionID

Tip solutie

Permanent

Voteaza

(18 din 34 persoane apreciaza acest articol)

Despre Autor

Leave A Comment?