#tessa_exchange_format(Version:1, CreatorName:Васильев В.В., CreationTime:2016-12-05T07:59:22)
{
	#exchange_view(RowID:f92bd0f5-77a4-4a4c-bbf5-164c45f7cd3d, Alias:ComplexReportExample, Caption:Сложный отчет, ModifiedById:3db19fa0-228a-497f-873a-0250bf0a4ccb, ModifiedByName:Васильев В.В., ModifiedDateTime:2016-12-05T07:44:54,GroupName:)
	{
#metadata
{
#view( Paging: no, Appearance: FormatCol)
#param(Alias: Group1, Caption: Группировка 1, Hidden: false, Type: nvarchar, Multiple: false, RefSection: complex_report_groups, AllowedOperands: Equality)
{
  #dropdown(View: ComplexReportGroups, RefPrefix: Group, PopupColumns: 1)
}

#param(Alias: Group2, Caption: Группировка 2, Hidden: false, Type: nvarchar, Multiple: false, RefSection: complex_report_groups, AllowedOperands: Equality)
{
  #dropdown(View: ComplexReportGroups, RefPrefix: Group, PopupColumns: 1)
}
    
#param(Alias: Group3, Caption: Группировка 3, Hidden: false, Type: nvarchar, Multiple: false, RefSection: complex_report_groups, AllowedOperands: Equality)
{
  #dropdown(View: ComplexReportGroups, RefPrefix: Group, PopupColumns: 1)
}
    
#column(Alias: FormatCol,  Hidden: true)



}
#description
{

}
#query
{
declare @temp as table \(
bank nvarchar\(50\)\,
reg nvarchar\(50\)\,
manager nvarchar\(50\)\,
step nvarchar\(50\)\,
amount decimal\(10\,2\)
\);

insert into @temp values \('bank1'\, 'region1'\, 'manager1'\, 'step1'\, 1.11\);
insert into @temp values \('bank1'\, 'region1'\, 'manager2'\, 'step1'\, 2.11\);
insert into @temp values \('bank1'\, 'region2'\, 'manager1'\, 'step1'\, 3.11\);
insert into @temp values \('bank1'\, 'region2'\, 'manager3'\, 'step1'\, 4.11\);
insert into @temp values \('bank1'\, 'region3'\, 'manager1'\, 'step1'\, 5.11\);
insert into @temp values \('bank1'\, 'region3'\, 'manager4'\, 'step1'\, 1.11\);
insert into @temp values \('bank1'\, 'region4'\, 'manager1'\, 'step1'\, 1.11\);
insert into @temp values \('bank1'\, 'region4'\, 'manager1'\, 'step1'\, 2.11\);
insert into @temp values \('bank2'\, 'region1'\, 'manager1'\, 'step2'\, 3.11\);
insert into @temp values \('bank2'\, 'region1'\, 'manager2'\, 'step2'\, 4.11\);
insert into @temp values \('bank2'\, 'region2'\, 'manager1'\, 'step2'\, 5.11\);
insert into @temp values \('bank2'\, 'region2'\, 'manager3'\, 'step2'\, 1.11\);
insert into @temp values \('bank2'\, 'region3'\, 'manager1'\, 'step1'\, 1.11\);
insert into @temp values \('bank2'\, 'region3'\, 'manager4'\, 'step1'\, 1.11\);
insert into @temp values \('bank2'\, 'region4'\, 'manager1'\, 'step1'\, 1.11\);
insert into @temp values \('bank2'\, 'region4'\, 'manager1'\, 'step1'\, 1.11\);
insert into @temp values \('bank2'\, 'region1'\, 'manager1'\, 'step1'\, 1.11\);


\#if\(Group3 && \(!Group2 || !Group1 \) || \(Group2 && !Group1\)\)
\{
select 'Пожалуйста\, задайте группировки по порядку\, с первой!' as [Ошибка]\, 
          '\#appearance\(FontSize\: 20\)' as FormatCol
\}
\{  -- верные параметры группировки
declare @group1_appearance as nvarchar\(128\);
declare @group2_appearance as nvarchar\(128\);
declare @group3_appearance as nvarchar\(128\);
declare @total_appearance as nvarchar\(128\);
set @total_appearance = '\#appearance\(Background\: \#a0aaaaaa\, FontSize\: 15\)';
set @group1_appearance = '';
set @group2_appearance = '';
set @group3_appearance = '';

\#if\(Group2 && !Group3\)
\{  -- группировка второго уровня уровня
  set @group1_appearance = '\#appearance\(Background\: \#cfda9694\, FontSize\: 15\)'; --218 150 148
\}
\#if\(Group3\)
\{  -- группировка третьего уровня
  set @group1_appearance = '\#appearance\(Background\: \#cfda9694\, FontSize\: 15\)'; --218 150 148
  set @group2_appearance = '\#appearance\(Background\: \#cfb8cce4\, FontSize\: 15\)'; --184 204 228
\}
  

select
       case when g1 is null and g2 is null and g3 is null then 'ИТОГО'
		 when g3 is not null then '      ' + g3
		 when g2 is not null then '   ' + g2
		 when g1 is not null then g1
	 end as Name\, 
	 
	  cast\( floor\(isnull\(step1_c\, 0\)\) as nvarchar\)  + ' \\ ' + cast\(isnull\(step1_s\, 0\) as nvarchar\)  as step1\,
	  cast\( floor\(isnull\(step2_c\, 0\)\) as nvarchar\)  + ' \\ ' + cast\(isnull\(step2_s\, 0\) as nvarchar\)  as step2\,
       case 
         when g1 is null and g2 is null and g3 is null then @total_appearance
         when g3 is not null then @group3_appearance
	  when g2 is not null then @group2_appearance
	  when g1 is not null then @group1_appearance
	 else 
	   ''
	 end as FormatCol
from
\(
      select step + col as step\,
                g1\,
                g2\,
                g3\,
                val
      from
      \(
		  select
		  	step\, 
		       g1\,
		       g2\,
		       g3\,
			count\(1\) as cnt\,
			sum\(amount\) as suma
		  from
		  \(
			select -- подменяем значения для тех уровней группировок\, которые не используются на какое-то отличное от всех возможных значений\, но не null. Для строк я выбрал пустую строку.
			  step\,
			  amount
		  	  \#if \(Group1\) \{ \,\#eval\(Group1.Value\) \} \{ \,'' \} as g1 -- для ссылок типа guid\, наверное\, надо записывать нулевой гуид вместо пустой строки
		  	  \#if \(Group2\) \{ \,\#eval\(Group2.Value\) \} \{ \,'' \} as g2
		  	  \#if \(Group3\) \{ \,\#eval\(Group3.Value\) \} \{ \,'' \} as g3	  
			from @temp 
		  \) t
		group by grouping sets \(\(step\)\, \(step\, g1\)\, \(step\, g1\, g2\)\, \(step\, g1\, g2\, g3\)\)
	\) tg cross apply
	\(
	    values
	         \(cnt\, '_c'\)\,
	         \(suma\, '_s'\)
	\) x \(val\, col\)
\) t1
pivot
\(
max\(val\)
for step in \([step1_c]\, [step2_c]\, [step1_s]\, [step2_s]\)
\) p
where \(g1 is null or g1 <> ''\)
and \(g2 is null or g2 <> ''\)
and \(g3 is null or g3 <> ''\)
order by
\(case when g1 is null and g2 is null and g3 is null then 1 else 0 end\)\, -- это будет строчка ИТОГО и она будет последней
 g1\, g2\, g3

\} -- верные параметры группировки
}
	}

}
