Вопрос
MSSQL
View
7.14

Передать параметры из фильтра в SQL view

Сергей Шк
18 сентября 2020 09:30

Здравствуйте!

Нужно сделать некий отчет, который будет содержать информацию по активностям, в том числе вычисляемые поля по количеству активностей, их длительности и тп. На вход отчету нужно задавать как минимум диапазон дат. Стандартными итогами такой отчет получить не могу, т.к. конструктор не позволяет выполнять вычисления. Планирую сделать sql view, но в нее не знаю как передавать параметры, взятые из фильтра. Помогите, пожалуйста, с передачей параметров.

Нравится

6 комментариев
Трефилов Павел Сергеевич
18 сентября 2020 11:48

Отчет где будет строиться?

1) Был опыт вывода view в итоги

2) В excel отчеты

3) подключение вычисляемых полей из view к реестру раздела

Nigreskul Aleksey
18 сентября 2020 14:33

Добрый день! Вы строите View c набором итоговых столбцов по которым уже и будите фильтровать из интерфейса.

Вот пример моего представления:

CREATE VIEW dbo.[VwDynamicOsago] ([Id],[CreatedAt],[WeekId],[MonthId],[Name], [InsuranceCount], 
[AvgPolicyCost], [PolicyIncome], [Cpa], [ProfitByOnePolicy], [Turnover], [Revenue], [AdsCost], [Profit], 
[MultiplexCoeff], [SingleTax], [BankExpenses], [MotivationCC],[BankRatio]) AS
SELECT 
	NEWID() [Id],	
	CONVERT(date, DATEADD(HOUR, dbo.fn_AbGetAddedHoursToSqlQuery(), [I].[CreatedAt])) [CreatedAt],
	[I].[WeekId] [WeekId],
	[I].[MonthId] [MonthId],
	'Динамика '+'('+CONVERT(varchar(10), DATEADD(HOUR, dbo.fn_AbGetAddedHoursToSqlQuery(), [I].[CreatedAt]), 120)+')' [Name],
	COUNT([I].[Id]) [InsuranceCount],
	AVG([I].BrokerDiscountedPayment) [AvgPolicyCost],
	AVG([I].Revenue) [PolicyIncome],
	dbo.fn_AbCalculateCpa([CpcCost].[AdsCost], COUNT([I].[Id])) [Cpa],
	dbo.fn_AbCalculateProfitByOnePolicy(SUM([I].Revenue), 
										dbo.fn_AbCalculateSingleTax(SUM([I].[Commission]), 5), 
										dbo.fn_AbCalculateBankExpenses(AVG([I].Revenue), dbo.fn_AbGetBankRatio(CONVERT(date, DATEADD(HOUR, dbo.fn_AbGetAddedHoursToSqlQuery(), [I].[CreatedAt])))), 
										dbo.fn_AbCalculateMotivationCC(SUM(CC_MOTIVATION.[Value]), COUNT([I].[Id])), 
										[CpcCost].[AdsCost], 
										COUNT([I].[Id])
										) [ProfitByOnePolicy],	
	SUM([I].BrokerDiscountedPayment) [Turnover],
	SUM([I].Revenue) [Revenue],
	[CpcCost].[AdsCost] [AdsCost],
	dbo.fn_AbCalculateProfit(SUM([I].Revenue), 
							 dbo.fn_AbCalculateSingleTax(SUM([I].[Commission]), 5), 
							 dbo.fn_AbCalculateBankExpenses(AVG([I].BrokerDiscountedPayment), dbo.fn_AbGetBankRatio(CONVERT(date, DATEADD(HOUR, dbo.fn_AbGetAddedHoursToSqlQuery(), [I].[CreatedAt])))), 
							 dbo.fn_AbCalculateMotivationCC(SUM(CC_MOTIVATION.[Value]),	COUNT([I].[Id])), 
							 [CpcCost].[AdsCost]
							 ) [Profit],
	IIF([CpcCost].[AdsCost] IS NOT NULL AND [CpcCost].[AdsCost] > 0, SUM([I].Revenue)/[CpcCost].[AdsCost],0) [MultiplexCoeff],
 
	dbo.fn_AbCalculateSingleTax(SUM([I].[Commission]), 5) [SingleTax],
	dbo.fn_AbCalculateBankExpenses(AVG([I].BrokerDiscountedPayment), dbo.fn_AbGetBankRatio(CONVERT(date, DATEADD(HOUR, dbo.fn_AbGetAddedHoursToSqlQuery(), [I].[CreatedAt])))) [BankExpenses],
	dbo.fn_AbCalculateMotivationCC(SUM(CC_MOTIVATION.[Value]), COUNT([I].[Id])) [MotivationCC],
	dbo.fn_AbGetBankRatio(CONVERT(date, DATEADD(HOUR, dbo.fn_AbGetAddedHoursToSqlQuery(), [I].[CreatedAt]))) [BankRatio]
 
FROM [AbInsurance] [I] WITH(NOLOCK)
LEFT OUTER JOIN [CpcCost] [CpcCost] WITH(NOLOCK) ON [CpcCost].[Date] = CONVERT(date, DATEADD(HOUR, dbo.fn_AbGetAddedHoursToSqlQuery(), [I].[CreatedAt]))
OUTER APPLY (SELECT SUM(IIF( DATEPART(HOUR, DATEADD(HOUR,dbo.fn_AbGetAddedHoursToSqlQuery(),CreatedAt)) > 18, 50, 40)) [Value]
			 FROM [AbInsurance] [I1] WITH(NOLOCK)
			 WHERE [I1].ProcessingReasonCCId IS NOT NULL AND [I1].Id = [I].Id
			 GROUP BY CONVERT(date,DATEADD(HOUR, dbo.fn_AbGetAddedHoursToSqlQuery(), [I1].[CreatedAt]))
			 ) AS CC_MOTIVATION
WHERE [I].StatusId = '195CD1AD-37B3-4F0A-B4FC-70E2BF0B3F06'
		AND [I].[CreatedAt] IS NOT NULL
GROUP BY CONVERT(date, DATEADD(HOUR, dbo.fn_AbGetAddedHoursToSqlQuery(), [I].[CreatedAt])), 'Динамика  '+'('+CONVERT(varchar(10), DATEADD(HOUR, dbo.fn_AbGetAddedHoursToSqlQuery(), [I].[CreatedAt]), 120)+')', [CpcCost].[AdsCost], [I].[MonthId], [I].WeekId

 

Зверев Александр
18 сентября 2020 16:51

Может, лучше не создавать динамически view под каждый запуск отчёта, а при его запуске формировать программно в ESQ или Select выборку с нужными условиями и группировками и записывать её  результаты в некоторую (одну и ту же) служебную таблицу, добавив в отдельную колонку Id запуска. А при построении отчёта фильтровать её по этому Id. А когда после построения отчёта эти записи станут ненужными, чистить.

Сергей Шк
21 сентября 2020 09:46

Александр, Ваша идея очень интересна. Спасибо! Объясните, пожалуйста, вкратце как можно на вкладке с итогами добавить контролы дат, для запроса диапазона дат у пользователя и кнопки Сформировать для инициирования формирования ESQ.

Зверев Александр
21 сентября 2020 10:47

Сергей, обычно для отчёта, где нужно указывать параметры, делают отдельную страницу параметров отчёта, которая открывается по действию.

 

Вам необходимо реализовать страницу настроек отчёта (пример схемы в конфигурации — ContactAnniversariesReportFilter) и зарегистрировать её в БД, в таблице SysModuleAnalyticsReport, в колонке SysOptionsPageSchemaUId записи Вашего отчёта.

 

Для конкретной реализации с вычислениями, видимо, нужно будет сделать логику записи результатов в таблицу, а затем передавать в отчёт один параметр — Id для фильтрации.

Сергей Шк
23 сентября 2020 10:11

Нигрескул Алексей пишет:

Добрый день! Вы строите View c набором итоговых столбцов по которым уже и будите фильтровать из интерфейса.

Алексей, ведь в стандартных итогах нет штатного фильтра. Можно будет только выгрузить всю таблицу в эксель и там уже средствами экселя фильтровать. А это очень неудобно. Или можно как-то добавить стандартный фильтр к произвольной вьюхе?

Показать все комментарии