Как сделать аналитический отчет или таблицу на сайте

Что должно быть в отчете

В статье покажем как на базе платформы сделать полезный и наглядный отчет по движению некоторых процессов на сайте. 

Что есть показатель, метрика? Что мы хотим измерять? Обычно это деньги, часы, посещения, регистрации и т.д. 

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

Просто в целом понять движение величин во времени - это уже хорошо. Но еще лучше, когда мы можем понять какое распределение есть по категориям, типам и т.д. 

Наши показатели могут быть привязаны к неким различным категориям, стратам. Например, у клиента могут быть следущие измерения - важность, город, тип продукта, год начала работы и т.д.

В итоге получаем таблицу вида: 

За счет фильтров выбираем нужный срез данных, определяем периоды и выводим нужные метрики (в данном случае это множество метрик, а не одна).  

В подтаблицах (там где плюс на скрине) можно указать детальные данные по выбранным объектам (передать все фильтры по сути через data-itemID в подтаблицу).

Причем подтаблица может работать на разные типы данных одинаковым образом. 

Что можно еще улучшить:

• показывать min, max по столбцам, чтобы сразу определять минимумы и максимумы по данным (цветом)
• показывать % изменения некоторых величин
• выделять проблемные точки красным, позитивные - зеленым (особенно когда есть бизнес-логика по точным критериям).
• за счет фильтров можно выдавать разный набор столбцов (на примере последний столбец показывается только если установлена галочка Показывать активность по комментариям).

Как реализовать аналитический отчет

Чтобы все это работало, что необходимо фиксировать различные события через таблицы логов, например, с датой создания строки (created).

По важным событиям в системе должна храниться информация - кто и когда создал событие, связь с объектами (заказ, товар и т.д.), и каков текущий статус. 

Примечание: данные могут лежать в разных таблицах и даже в разных базах данных.

Как реализовать таблицу: 

Создаем столбцы: метрики (col1, col2 и т.д.), date, hide_intervalType(фильтр - radio) и столбцы для выбранных фильтров (мы их передадим в итоге во вложенную таблицу в itemID). 

Для столбца intervalType прописываем процедуру: 

create procedure [dbo].[crud_{tableCode}_intervalType_dict]
	@tableCode nvarchar(32),
	@col nvarchar(32),
	@username nvarchar(32)
as
begin
	-- SELECT 1
	select 'days' Value, 'Дни' Text, 1 ord
	union
	select 'weeks' Value, 'Недели' Text, 2 ord
	union	
	select 'months' Value, 'Месяцы' Text, 3 ord
	union	
	select 'quarters' Value, 'Кварталы' Text, 4 ord
	order by ord
end

Реализуем GetItems: 

CREATE PROCEDURE [dbo].[crud_unit-demo_getItems]
	@filters CRUDFilterParameter READONLY,  
	@sort sql_variant,
	@direction nvarchar(8),
	@page int,
	@pageSize int,
	@username nvarchar(32)
AS
BEGIN
	
	declare @result TABLE( 
		hide_intervalType nvarchar(max),
      	interval nvarchar(max),
      	dt date,
      	date nvarchar(max),		
	col1 int,
	col2 int
	)	

	declare @filterIntervalType nvarchar(128) = (select Value from @filters where [Key] = 'hide_intervalType')

     -- определяем даты  
  	set datefirst 1;
    declare @date date = dateadd(month, -1, getdate())
    if(@filterIntervalType='weeks') begin
    	set @date = dateadd(week, -12, getdate())
       set @date =  DATEADD(DAY, 1-DATEPART(WEEKDAY, @date), @date);
    end
    if(@filterIntervalType='months') begin
    	set @date = dateadd(month, -12, getdate())
        set @date =  DATEADD(month, DATEDIFF(month, 0, @date), 0)
    end
    if(@filterIntervalType='quarters') begin
    	set @date = dateadd(month, -12, getdate())
        set @date =  DATEADD(quarter, DATEDIFF(quarter, 0, @date), 0)
    end
    
    declare @date2 date, @name nvarchar(128)
    
   while (@date< getdate() ) begin
        set @date2= dateadd(day, 1, @date) 
        set @name = convert(nvarchar, @date, 104)
        
    	if(@filterIntervalType='weeks') begin 
        	set @date2= dateadd(week, 1, @date)
            set @name = 'Week '+  cast(datepart(week, @date) as nvarchar);        	    
        end    
    	if(@filterIntervalType='months') begin
        	set @date2= dateadd(month, 1, @date)
            set @name =datename(month, @date) + ' ' + cast(datepart(year, @date) as nvarchar);        	    
        end    
    	if(@filterIntervalType='quarters') begin
        	set @date2= dateadd(quarter, 1, @date)
            set @name =datename(quarter, @date) + ' ' + cast(datepart(year, @date) as nvarchar);        	    
        end
    
        insert into @result 
        select
            NULL  hide_intervalType,
            @filterIntervalType interval,
        	@date dt,
        	@name date, 
                0 col1,
                0 col2,
        /* пример подзапроса   isnull(  (select  cast(sum(valueFloat) as nvarchar) from [as_mt_outerValues] where code='wa-visitors'  and itemID='d' and date >= @date and date < @date2), 0)  wa_visits,  */                 
                                    
            
         set @date= @date2   
	end
	
	-- 1 SELECT - сами данные		
	select 
    	hide_intervalType, 	interval, dt, date,
        isnull(col1, 0),
        '<'+'div class="as-table" data-code="subreport" data-itemID="'+interval+'_'+convert(nvarchar(10), dt, 105)+'_reg' +'"></'+div+'>' sub_col1,
    from @result 
    order by dt desc
	
	-- 2 SELECT - кол-во в таблице
	select count(*) from @result	

	-- 3 SELECT Дополнительные настройки таблицы
    select 1 Compact, '16px' FontSize, 1 InstantFilter, 1 HideTitleCount, 'h2' headerTag, 
        '{ 
        "colorMinMaxCols": [
            	{ "code": "col1",  "minClass": "text-danger font-weight-bold", "maxClass": "text-success font-weight-bold" },
                { "code": "col2",  "minClass": "text-danger font-weight-bold", "maxClass": "text-success  font-weight-bold" }
            ]        
        }' ProcessOptions	
	
END

Также создаем вложенную таблицу(в примере код subreport), которая будет отображать данные в деталях. GetItems: 

CREATE PROCEDURE [dbo].[crud_subReport_getItems]
	@filters CRUDFilterParameter READONLY,  
	@sort sql_variant,
	@direction nvarchar(8),
	@page int,
	@pageSize int,
	@username nvarchar(32)
AS
BEGIN
	
	-- filters...
	declare @itemID nvarchar(128)
	select @itemID = Value from @filters where [Key] = 'itemID'
	
    declare @interval nvarchar(128) = dbo.str_splitPart(@itemID, '_', 1)
    
    declare @temp nvarchar(128) = dbo.str_splitPart(@itemID, '_', 2)
    declare @date date = try_convert(date, dbo.str_splitPart(@temp, '_', 1) , 105)
    declare @type nvarchar(128) = dbo.str_splitPart(@temp, '_', 2)
    
  
    declare @date2 date = iif(@interval='days',  dateadd(day, 1, @date), 
                             iif(@interval='weeks',  dateadd(week, 1, @date), dateadd(month, 1, @date))
                             )
    
    if(@type = 'reg') begin 
    	-- SELECT 1
		select  0, isnull(itemID, '') name, isnull(count(*), 0) [info]  
        from ... where code='demo-reg' and date > @date and date < @date2  
        group by itemID
        order by count(*) desc
    end
    
    if(@type = 'leads') begin 
    	-- SELECT 1
		 select client.id, ''+ ctr.shortname + '' name, 
         	st.name info, st.color marker_info 
         from ...
          where ei.created > @date and ei.created < @date2
    end 
    --- ... по аналогии обрабатываем и другие типы (по сути это поля родительской таблицы)  
	
	-- SELECT 2
	select 1

	-- SELECT 3
    select 1 HideTitleCount, 'h3' headerTag, 1 Compact	

END