Создание пользовательского виджета таблицы.

Добрый вечер, Алексей.

Результат хранимой процедуры возвращайте в представление, например:

CREATE FUNCTION [dbo].[tsf_PathReportData]()
RETURNS @ExaminationBlocksByObject TABLE (
		[Number] nvarchar(50),
		[ExaminationId] uniqueidentifier,
		[ObjectId] uniqueidentifier,
		[MacroscopicDescription] nvarchar(max),
		[BlocksDescription] nvarchar(max))
AS
BEGIN

	DECLARE
		@Number nvarchar(50),
		@ExaminationId uniqueidentifier,
		@ObjectId uniqueidentifier,
		@MacroscopicDescription nvarchar(max),
		@BlocksDescription nvarchar(max)

	DECLARE ExaminationDetailCursor CURSOR STATIC LOCAL FOR
	SELECT
			O.Number,
			O.MacroscopicDescription,
			O.ExaminationId,
			O.Id as ObjectId
	FROM ABExaminationObject O

	OPEN ExaminationDetailCursor

	FETCH NEXT FROM ExaminationDetailCursor INTO @Number, @MacroscopicDescription, @ExaminationId, @ObjectId

	WHILE @@FETCH_STATUS = 0 BEGIN

		SET @BlocksDescription = ''

		SELECT @BlocksDescription = @BlocksDescription + B.Number + ' ' + B.[Description] + CHAR(13) + CHAR(10)
		FROM ABExaminationBlock B
		WHERE B.ExaminationObjectId = @ObjectId

		INSERT INTO @ExaminationBlocksByObject (
			Number,
			ExaminationId,
			ObjectId,
			MacroscopicDescription,
			BlocksDescription
		)
		VALUES (
			@Number,
			@ExaminationId,
			@ObjectId,
			@MacroscopicDescription,
			@BlocksDescription)

		FETCH NEXT FROM ExaminationDetailCursor INTO @Number, @MacroscopicDescription, @ExaminationId, @ObjectId
	
	END 

	CLOSE ExaminationDetailCursor 
	DEALLOCATE ExaminationDetailCursor

	RETURN

END


create view VwExaminationReport
as
select
	newid() as Id,
	Number as Number,
	MacroscopicDescription as MacroscopicDescription,
	ExaminationId as Examination,
	BlocksDescription as BlocksDescription
from [tsf_PathReportData]()

На основании этого представления создайте схему таблицы-представления в конфигурации и используйте этот объект в стандартном элементе итогов 'Список'.

Алла Савельева,

добрый! Мне в ХП нужно передавать параметры даты из фильтров, на сколько я знаю, то представление нельзя сделать с принимаемыми параметрами.

CREATE PROCEDURE [dbo].[absp_GetProductAnalyticByZone] @startDate varchar(10), @stopDate varchar(10)
AS
SELECT 
	T.Segment [Segment], 
	ROUND(SUM(T.[AvgRevenue]), 2, 1) [AvgRevenue],
	SUM(T.[SalesCount]) [SalesCount],	
	ROUND(SUM(T.[CPA]), 2, 1) [CPA],	
	SUM(T.[SumCtr]) [SumCtr],	
	ROUND(SUM(T.[ROAS]), 2, 1) [ROAS]
FROM
	(SELECT 
		[IABZ].[Date],
		[IABZ].ZoneId,
		[IZ].Segment,
		SUM([IABZ].Ctr) [SumCtr],
		SALES_QUERY.SalesCount [SalesCount],
		SALES_QUERY.AvgRevenue [AvgRevenue],
		IIF(SALES_QUERY.SalesCount = 0 , 0,([CpcCost].[AdsCost]/SALES_QUERY.SalesCount))[CPA],
		(SALES_QUERY.SalesCount * SALES_QUERY.AvgRevenue / [CpcCost].[AdsCost]) * 100 [ROAS],
		[CpcCost].[AdsCost]
	
	FROM [InsurerAnalyticByZone] [IABZ] WITH(NOLOCK)
	LEFT OUTER JOIN [InsurerZone] [IZ] WITH(NOLOCK) ON [IABZ].ZoneId = [IZ].Id
	INNER JOIN [CpcCost] [CpcCost] WITH(NOLOCK) ON [IABZ].[Date] = [CpcCost].[Date]
	OUTER APPLY (SELECT COUNT([I].[Id]) [SalesCount],
						AVG([I].[Revenue]) [AvgRevenue]
					FROM [AbInsurance] [I] WITH(NOLOCK) 
					INNER JOIN [CityByEwa] [CBE] WITH(NOLOCK) ON [I].EwaCityId = [CBE].Id
					WHERE [I].StatusId = '195CD1AD-37B3-4F0A-B4FC-70E2BF0B3F06' 
					AND [CBE].ZoneCode = [IZ].Code 
					AND CONVERT(varchar(10), [I].CreatedAt, 120) = [IABZ].[Date]
				) SALES_QUERY
	GROUP BY [IABZ].ZoneId, [IZ].Segment, [IABZ].[Date],  [IZ].Code, [CpcCost].AdsCost, SALES_QUERY.SalesCount, SALES_QUERY.AvgRevenue	
	) AS T
WHERE T.[Date] BETWEEN @startDate AND @stopDate
GROUP BY T.ZoneId, T.Segment

UNION ALL 

SELECT 
[CBE].[Name] [Segment],
[R].[AvgRevenue] [AvgRevenue],
COUNT([I].[Id]) [SalesCount],
SUM(IIF(R.[Count] = 0, 0, [CpcCost].[AdsCost]/R.[Count])) [CPA],
 0 [SumCtr],
ROUND(SUM((R.[Count] * R.[AvgRevenue]) / [CpcCost].[AdsCost]) * 100, 2, 1) [ROAS]
FROM [AbInsurance] [I] WITH(NOLOCK) 
INNER JOIN [CityByEwa] [CBE] WITH(NOLOCK) ON [I].EwaCityId = [CBE].Id AND [CBE].Id = '9a008f3a-f865-4e74-b1f2-530f54711f2b'
INNER JOIN [CpcCost] [CpcCost] WITH(NOLOCK) ON CONVERT(varchar(10), [I].CreatedAt, 120) = [CpcCost].[Date]
OUTER APPLY ( SELECT COUNT([I].Id) [Count],
						AVG([I].[Revenue]) [AvgRevenue]
				FROM [AbInsurance] [I] WITH(NOLOCK) 
				INNER JOIN [CityByEwa] [CBE] WITH(NOLOCK) ON [I].EwaCityId = [CBE].Id AND [CBE].Id = '9a008f3a-f865-4e74-b1f2-530f54711f2b'
				INNER JOIN [CpcCost] [CpcCost] WITH(NOLOCK) ON CONVERT(varchar(10), [I].CreatedAt, 120) = [CpcCost].[Date]
				WHERE [I].StatusId = '195CD1AD-37B3-4F0A-B4FC-70E2BF0B3F06' AND CONVERT(varchar(10), [I].CreatedAt, 120) BETWEEN @startDate AND @stopDate
) R
WHERE [I].StatusId = '195CD1AD-37B3-4F0A-B4FC-70E2BF0B3F06' AND CONVERT(varchar(10), [I].CreatedAt, 120) BETWEEN @startDate AND @stopDate
GROUP BY [CBE].[Name], [R].[AvgRevenue]

вот моя ХП, возвращает мне нужную информацию

Нигрескул Алексей,

А если попробовать реализовать Ваш виджет, как наследник от базового списка итогов?