Как подключить список Sharepoint к отчету SSRS?
Вариант 1. Создать dataset c подключением к sharepoint
<RSSharePointList xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
<ListName>График отпусков</ListName>
<Query>
<Where>
</Where>
</Query>
</RSSharePointList>
Привязать использую функцию lookup и lookupset
Вариант 2.
Подключить на уровне процедуры через базу sharepoint
ALTER PROCEDURE [dbo].[GetVacationsSharepoint] (@DATEFROM DATETIME = NULL,@DATEEND DATETIME =NULL )
AS
BEGIN
SET NOCOUNT OFF
SET LANGUAGE Russian;
--DECLARE @DATEFROM DATETIME
--DECLARE @DATEEND DATETIME
SET @DATEFROM = ISNULL(@DATEFROM,DATEADD(month, DATEDIFF(month, 0, GETDATE()), 0) )
SET @DATEEND = ISNULL(@DATEEND,DATEADD(d, -1, DATEADD(m, DATEDIFF(m, 0, GETDATE()) + 1, 0)))
SET @DATEEND = DATEADD(d, -1, DATEADD(m, DATEDIFF(m, 0,@DATEEND) + 1, 0))
SELECT D.DATA , t.[Login] , t.[Имя пользователя] COLLATE Cyrillic_General_CI_AS as Username , DATENAME(dw,D.DATA) WeekDay, DATEPART(weekday,D.DATA) WeekDayNumber, DATEPART(wk,D.DATA) WeekNumber, DATENAME(month,D.DATA) Monthname ,DATEPART(month,D.DATA) MonthNumber , DATEName(yyyy,D.DATA) YEARNAME ,
DATEPART(dayofyear,D.DATA) DayOfyearNumber,
DATEPART (day, D.DATA) DayOfMnth ,
(CASE WHEN DATEPART(weekday,D.DATA) = 1 THEN 'Пн'
WHEN DATEPART(weekday,D.DATA) = 2 THEN 'Вт'
WHEN DATEPART(weekday,D.DATA) = 3 THEN 'Ср'
WHEN DATEPART(weekday,D.DATA) = 4 THEN 'Чт'
WHEN DATEPART(weekday,D.DATA) = 5 THEN 'Пт'
WHEN DATEPART(weekday,D.DATA) = 6 THEN 'Сб'
WHEN DATEPART(weekday,D.DATA) = 7 THEN 'Вс' ELSE DATENAME(dw,D.DATA) END) WeekDayShort,
(CASE WHEN D.DATA >=t.DateFrom AND D.DATA <= t.DateTo THEN 1 ELSE NULL END) IsVacation,
(CASE WHEN D.DATA >=t.DateFrom AND D.DATA <= t.DateTo THEN t.VacationType ELSE NULL END) VacationType,
(CASE WHEN D.DATA >=t.DateFrom AND D.DATA <= t.DateTo THEN t.id ELSE NULL END) id,
(CASE WHEN D.DATA >=t.DateFrom AND D.DATA <= t.DateTo THEN t.Comment ELSE NULL END) Comment
FROM (SELECT DATA
FROM dbo.DatesBetween(@DATEFROM, @DATEEND)) D ,
(
SELECT dat.nvarchar3 VacationType, dat.datetime1 DateFrom, dat.[datetime2] DateTo, dat.int1 USERID, u.tp_Login [Login], u.tp_Title [Имя пользователя], dat.tp_ID ID, dat.nvarchar1 Comment
FROM [Wss_content_80_portal2010].[dbo].[AllUserData] DAT (NOLOCK)
LEFT JOIN [Wss_content_80_portal2010].[dbo].[UserInfo] u (NOLOCK) ON u.tp_ID = dat.int1
WHERE dat.tp_ListId = '055C8B06-6C99-4F4F-BAF2-ED809CA2CD42'
--(SELECT TOP 1 LIST.tp_ID FROM [Wss_content_80_portal2010].dbo.alllists LIST(NOLOCK)
--WHERE LOWER(LIST.tp_Title) = 'график отпусков' AND list.tp_DeleteTransactionId = 0x)
AND (dat.[tp_RowOrdinal] = 0)
AND (dat.tp_Level = 1)
AND (dat.[tp_IsCurrentVersion] = CONVERT(BIT, 1))
AND (dat.[tp_CalculatedVersion] = 0)
AND (dat.[tp_DeleteTransactionId] = 0x)
AND (dat.tp_SiteId = '5929DACB-CEDE-4564-9EDF-80B572A032AE')
AND dat.tp_ContentTypeId = 0x0100A3B1A57EC7FEE84593355BC92E45F9D5
) T
END
