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

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

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

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

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

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

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

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

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

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

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

 

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

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

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

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

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

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

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

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

Реализуем 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 @filterCommentCount bit = (select try_cast(Value as bit) from @filters where [Key] = 'commentCount')


	declare @filterIntervalType nvarchar(128) = (select Value from @filters where [Key] = 'hide_intervalType')
    declare @filterProduct nvarchar(128) = isnull((select Value from @filters where [Key] = 'product'), '')
     -- определяем даты  
  	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,  */
                 
                                         where com.type='client' and  com.created > @date and com.created < @date2), 0), NULL) commentCount,
            
         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' +'">' 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, '<a href="/client/'+cast(ei.id as nvarchar)+'">'+ ctr.shortname + '</a>' 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

Falcon Space - функциальная веб-платформа разработки на узком стеке MS SQL/Bootstrap. Вводная по Falcon Space
Насколько полезной была статья?

Google поиск по нашей документации

Falcon Space

Это снижение стоимости владения

за счет меньшего количества людей для поддержки и узкого стека разработки. Про снижение стоимости владения продуктом

Это быстрое внесение изменений

по ходу эксплуатации программы. Как создается функционал на платформе

Это простой удобный интерфейс

адаптация под мобильные устройства. Про юзабилити платформы