В качестве предыстории если кто не в теме, общая схема работы SSRS в собственном режиме выглядит так.
Подробное описание режимов работы можно посмотреть тут
Вся необходимая информация для запуска сервера отчетов содержится в файле конфигурации RsReportServer.config
Дополнительную информацию о конфигурации сервера отчетов можно посмотреть процедурой exec dbo.GetAllConfigurationInfo
запустив ее в базе ReportServer
Итак поехали тут самые полезные скрипты
1. Посмотреть папки и доступ
SELECT CASE RSCatalog.TYPE
WHEN 1 THEN 'Folder'
ELSE 'Report'
END AS [Type] ,
RSCatalog.Path ,
RSCatalog.Name AS Report ,
Users.UserName ,
Roles.RoleName
FROM [dbo].[Catalog] RSCatalog
INNER JOIN [dbo].[PolicyUserRole] PolicyUserRole ON RSCatalog.PolicyID = PolicyUserRole.PolicyID
INNER JOIN [dbo].[Roles] Roles ON PolicyUserRole.RoleID = Roles.RoleID
INNER JOIN [dbo].[Users] Users ON PolicyUserRole.UserID = Users.UserID
WHERE RSCatalog.TYPE IN ( 1, 2 )
ORDER BY RSCatalog.Path ,
RSCatalog.Name ,
Users.UserName
2. Посмотреть XML код отчетов и объектов
SELECT ItemID ,
[Path] ,
[Name] ,
CONVERT(VARCHAR(MAX), CONVERT(NVARCHAR(MAX), CONVERT(XML, CONVERT(VARBINARY(MAX), Content)))) AS [ReportDefinition]
FROM [dbo].[Catalog]
WHERE [Type] = 2
3. Посмотреть настроенные рассылки
SELECT
[Locale],
[InactiveFlags],
'Next Run Date' = CASE next_run_date
WHEN 0 THEN null
ELSE
substring(convert(varchar(15),next_run_date),1,4) + '/' +
substring(convert(varchar(15),next_run_date),5,2) + '/' +
substring(convert(varchar(15),next_run_date),7,2)
END,
'Next Run Time' = isnull(CASE len(next_run_time)
WHEN 3 THEN cast('00:0'
+ Left(right(next_run_time,3),1)
+':' + right(next_run_time,2) as char (8))
WHEN 4 THEN cast('00:'
+ Left(right(next_run_time,4),2)
+':' + right(next_run_time,2) as char (8))
WHEN 5 THEN cast('0' + Left(right(next_run_time,5),1)
+':' + Left(right(next_run_time,4),2)
+':' + right(next_run_time,2) as char (8))
WHEN 6 THEN cast(Left(right(next_run_time,6),2)
+':' + Left(right(next_run_time,4),2)
+':' + right(next_run_time,2) as char (8))
END,'NA'),
Convert(XML,[ExtensionSettings]).value('(//ParameterValue/Value[../Name="TO"])[1]','nvarchar(50)') as [To]
,Convert(XML,[ExtensionSettings]).value('(//ParameterValue/Value[../Name="RenderFormat"])[1]','nvarchar(50)') as [Render Format]
,Convert(XML,[ExtensionSettings]).value('(//ParameterValue/Value[../Name="Subject"])[1]','nvarchar(50)') as [Subject]
---Example report parameters: StartDateMacro, EndDateMacro & Currency.
,Convert(XML,[Parameters]).value('(//ParameterValue/Value[../Name="StartDateMacro"])[1]','nvarchar(50)') as [Start Date]
,Convert(XML,[Parameters]).value('(//ParameterValue/Value[../Name="EndDateMacro"])[1]','nvarchar(50)') as [End Date]
,Convert(XML,[Parameters]).value('(//ParameterValue/Value[../Name="Currency"])[1]','nvarchar(50)') as [Currency]
,[ModifiedDate]
,[LastStatus]
,[EventType]
,[LastRunTime]
,[DeliveryExtension]
,[Version]
FROM
ReportServer.dbo.[Subscriptions] S
INNER JOIN ReportServer.dbo.ReportSchedule R ON S.SubscriptionID = R.SubscriptionID
INNER JOIN msdb.dbo.sysjobs J ON Convert(nvarchar(128),R.ScheduleID) = J.name
INNER JOIN msdb.dbo.sysjobschedules JS ON J.job_id = JS.job_id
4. Найти отчет где используется объект, в данном примере находятся все источники где используется слово USER
;WITH XMLNAMESPACES
(DEFAULT 'http://schemas.microsoft.com/sqlserver/reporting/2008/01/reportdefinition',
'http://schemas.microsoft.com/sqlserver/reporting/2008/01/reportdefinition' AS REP
)
SELECT c.Path ,
c.Name ,
DataSetXML.value('@Name', 'varchar(MAX)') DataSourceName ,
DataSetXML.value('REP:Query[1]/REP:CommandText[1]', 'varchar(MAX)') CommandText
FROM ( SELECT ItemID ,
CAST(CAST(Content AS VARBINARY(MAX)) AS XML) ReportXML
FROM [ReportServer].[dbo].[Catalog]
WHERE TYPE = 2
) ReportXML
CROSS APPLY ReportXML.nodes('//REP:DataSet') DataSetXML ( DataSetXML )
INNER JOIN [dbo].[Catalog] c ON ReportXML.ItemID = c.ItemID
-- Search by part of the query text
WHERE ( DataSetXML.value('REP:Query[1]/REP:CommandText[1]', 'varchar(MAX)') ) LIKE N'%USER%'
Есть 3 стандартных представления для просмотра лога выполнения отчетов, отличаются по себе незначительно, в зависимости от вашей версии используйте нужную:
- dbo.ExecutionLog – Available since 2000
- dbo.ExecutionLog2 – Introduced in 2008
- dbo.ExecutionLog3 – Introduced in 2008R2
5. Посмотреть кто запускал отчет последние N дней
SELECT UserName
,COUNT(*) AS TimesExecuted
FROM ReportServer.dbo.ExecutionLog3
WHERE ItemPath LIKE N'%Документы требующие доработки%'
AND TimeStart > GETDATE() - 10
GROUP BY UserName
ORDER BY 2 DESC
6. Посмотреть использование отчета, количество строк в отчете, параметры, время выполнения, формат и пользователя
SELECT TimeStart
,Source
,[RowCount]
,Parameters
,UserName
,RequestType
,Format
,TimeDataRetrieval + TimeProcessing + TimeRendering [TimeInMilliseconds]
FROM dbo.ExecutionLog3
WHERE ItemPath LIKE N'%Документы требующие доработки%'
AND TimeStart BETWEEN '2014-09-09' AND '2014-10-09'
ORDER BY TimeStart
7. Посмотреть размещение отчета с датой создания, последний запуск и количество запусков
SELECT Name
,CreationDate
,Path
,E.TimesExecuted
,E.LastExecuted FROM Catalog
INNER JOIN (SELECT ItemPath
,COUNT(*) AS TimesExecuted
,MAX(TimeStart) AS LastExecuted
FROM ExecutionLog3
GROUP BY ItemPath) E
ON CATALOG.PATH = E.ItemPath --WHERE
WHERE TYPE = 2
--AND Path LIKE '/Reports%'
ORDER BY 2 DESC
Где смотреть логи об ошибках ?
В SSRS2008 и более поздних версия все файлы лога сливаются в один ReportServerService_<timestamp>.log.
Этот файл можно найти в папке \Microsoft SQL Server\<SQL Server Instance>\Reporting Services\LogFiles
7. Как найти проблемный отчет. Смотрим по логам.
SELECT c.Name , l.*
FROM [ReportServer].[dbo].[ExecutionLogStorage] l (NOLOCK)
LEFT JOIN [ReportServer].[dbo].[Catalog] c (NOLOCK) ON c.ItemID = l.ReportID
WHERE TimeStart>= '2016-11-02'
ORDER BY [LogEntryId] desc
SELECT c.Path , c.Name , ItemID ,
CAST(CAST(Content AS VARBINARY(MAX)) AS VARCHAR(MAX)) ReportTEXT
FROM [ReportServer].[dbo].[Catalog] c (NOLOCK)
WHERE TYPE = 2
AND c.[Path] LIKE N'%СЭД%'
AND CAST(CAST(Content AS VARBINARY(MAX)) AS VARCHAR(MAX)) LIKE N'%GetInitiatorTime%'
P/S:Важной частью сервера отчетов SSRS является конфигурационный файл RsReportServer.config , этим скриптом можно отобразить его содержимое напрямую в SQL если лень открывать файл на сервере,
однако необходимы будут права на выполнени xp_cmdshell
DECLARE @FileName VARCHAR(255)
DECLARE @ExecCmd VARCHAR(255)
DECLARE @y INT
DECLARE @x INT
DECLARE @FileContents VARCHAR(MAX)
DECLARE @xmlHandle INT
-- used later on for parsing
CREATE TABLE #configXML
(
PK INT NOT NULL
IDENTITY(1, 1) ,
[XMLValue] VARCHAR(MAX)
)
SET @FileName = 'D:\DATA\MSRS11.OLAP\Reporting Services\ReportServer\rsreportserver.config'
SET @ExecCmd = 'type ' + '"' + @FileName + '"'
SET @FileContents = ''
INSERT INTO #configXML
EXEC MASTER.dbo.xp_cmdshell @ExecCmd
SELECT @y = COUNT(*)
FROM #configXML
SET @x = 0
WHILE @x <> @y - 1
BEGIN
SET @x = @x + 1
SELECT @FileContents = @FileContents + [XMLValue]
FROM #configXML
WHERE PK = @x
END
-- display the file contents
SELECT @FileContents AS FileContents
DROP TABLE #configXML
-- Parsing the config file XML
EXEC sp_xml_preparedocument @xmlHandle OUTPUT, @FileContents
SELECT *
FROM OPENXML (@xmlHandle, '//Service', 2) WITH
(
IsSchedulingService VARCHAR(255) 'IsSchedulingService',
IsNotificationService VARCHAR(255) 'IsNotificationService',
PollingInterval VARCHAR(255) 'PollingInterval',
WindowsServiceUseFileShareStorage VARCHAR(255) 'WindowsServiceUseFileShareStorage'
)
EXEC sp_xml_removedocument @xmlHandle
P\S
Ну и как бонус. Расширение отчетов с помощью javascript.
Добавляем код в раздел Action на любой элемент.
Сообщение
="javascript:( alert('Hi Guys') )"
Пользовательский ввод
Based, on the user input you can open a new web page.
="javascript:var name=prompt('Which page do you want to navigate?'); if(name=='google') {void window.open('http://www.google.com',800,800,'_blank')} else {void window.open('http://www.msn.com',800,800,'_blank')}"
Кнопка Да\Отмена
="javascript:var check=confirm('Do you want to navigate to New report?'); if (check == true) {window.open('http://localhost/Reports/Pages/Report.aspx?ItemPath=%2fReport1','_self')}"
Открыть новое окно в браузере
="javascript:void (window.open('http://localhost/ReportServer/Pages/ReportViewer.aspx?/Folder/Reportname&rs:Command=Render&Param1="& Parameters!Param1.Value & "&Param2=" & Parameters!Param2.Value & "','_blank'))"
Выгрузить отчет из базы
cd "C:\Program Files\Microsoft SQL Server\110\Tools\Binn>"
BCP " SELECT Content FROM [ReportServer].[dbo].[Catalog] c (NOLOCK) WHERE TYPE = 2 AND ItemID = '06987356-5842-4A9E-9728-9F99F59F0F2D'" queryout c:\shared\demo.rdl -S servername -d ReportServer -T -n
Чтобы выгрузить пачкой можно сгенерировать скрипты
SELECT c.Path , c.Name , ItemID , c.creationdate, c.modifieddate ,
--CAST(CAST(Content AS VARBINARY(MAX)) AS VARCHAR(MAX)) ReportTEXT,
'BCP " SELECT Content FROM [ReportServer].[dbo].[Catalog] c (NOLOCK) WHERE TYPE = 2 AND ItemID = '''+cast(ItemID as varchar(max))+'''" queryout "c:\shared\export\'+c.Name+'.rdl" -S servername -d ReportServer -T -n'
FROM [ReportServer].[dbo].[Catalog] c (NOLOCK)
WHERE TYPE = 2 --and hidden=0
AND c.[Path] LIKE N'%/Анализ производительности%'
