Как создать сводную таблицу HTML в NPrinting
Темы сводных таблиц в NPrinting часто вызывают вопросы. И в том, как их использовать, что поддерживается/не поддерживается, как они работают, как их можно использовать в шаблонах NPrinitng и, в конечном итоге, какие обходные пути существуют для создания сводных таблиц в различных шаблонах есть много путаницы.
Некоторое время назад я наткнулся на этот пост в сообществе и подумал, что возьму на себя задачу найти решение, как создать сводную таблицу в шаблоне HTML и встроить ее в тело письма. То, что кажется легкой задачей во вселенной HTML, уже не такое простое при использовании при почтовой рассылке. Почтовый клиент не поддерживает теги <script> по очевидным причинам безопасности. Большинство существующих решений для построения сводных таблиц в HTML основаны на некоторых скриптах (независимо от того, что они на самом деле делают). Мое предположение было очень простым: я не могу использовать теги <script>, а сводная таблица должна отображаться в почтовом клиенте (или, по крайней мере, в большинстве популярных почтовых клиентов). Чтобы добиться этого, я решил построить свое решение на теге html <table> и встроенном форматировании css.
Проблемы:
- нет возможности использовать тег <script>
- необходимо работать в почтовом клиенте
- метки динамических столбцов
- динамическое количество столбцов
- объединить ячейки таблицы, чтобы создать сводную таблицу
- формат ячеек и форматирование значений (цвета фона, числовые форматы)
- итоговая строка/столбец
Самый простой способ извлечь данные из сводной таблицы QlikView/Qlik Sense в шаблон HTML NPrinting – это перетащить весь тег объекта в шаблон. Это дает нам правильные данные, но не дает возможностей оформить все как следует, только очень простое форматирование, и не сохраняет размерные группировки, которые больше всего необходимы при просмотре сводных таблиц.
Второй подход, который вы, возможно, захотите рассмотреть – это использование изображения и его вывод. Проблемой стало отсутствие контроля над фактическими компонентами диаграммы. Возможности управления размером диаграммы и разрешением визуализации ограничены, что позволяет изменять размер пространства, в котором создается диаграмма, и это в свою очередь позволяет отображать на ней больше информации. Это важно для всех диаграмм Qlik Sense, так как адаптивный дизайн будет случайным образом скрывать метки, значения в точках данных, заголовки, легенды и т. д. Поэтому в целом возможности с изображениями очень ограничены, и они просто не выглядят хорошо в конечном счете.
Возможно, есть другие способы создания сводной таблицы в HTML, но я не смог найти такого, поэтому я подумал о том, как можно имитировать сводную таблицу с помощью обычной таблицы. Я немного покопался в HTML, узнал об атрибутах col-span и row-span и о том, как их использовать. Я немного подумал и решил, что этого можно добиться, немного поработав в Qlik Sense или QlikView. Итак, что нужно сделать?
Вам необходимо учитывать размерность вашей таблицы. Я подготовил пример с 3-мя измерениями, в котором одно измерение используется для создания столбцов. Важно учитывать количество измерений, поскольку оно будет использоваться для правильного объединения ячеек. Так какой же методологии я решил следовать?
В этом примере я использовал Qlik Sense, но решение QlikView будет работать так же.
Сначала я построил сводную таблицу, чтобы увидеть, чего я хочу добиться. В моем примере есть измерения Year, Dim1, Dim2 и Month. У него также есть 1 показатель: сумма (Выражение1).
Сводная таблица, которую я воспроизведу в отчете NPrinting HTML
Я предполагал, что в моей сводной таблице будет ограниченное количество столбцов. Я не ограничиваю ее каким-либо конкретным числом, но, учитывая, что следующий шаг требует от меня создания меры в X столбцах, мне было необходимо это предположение. Итак, следующий шаг – построить прямую таблицу. Я использую те же измерения: Year, Dim1, Dim2, но вместо того, чтобы использовать Month в качестве измерения, я должен создать отдельные выражения для каждого месяца. Для этого мне нужно изменить исходное выражение:
- sum(Expression1) – оригинал
-
sum({<Month*={'$(=FieldValue('Month',1))'}>}Expression1) – за первый месяц
- Label: =FieldValue('Month',1)
-
sum({<Month*={'$(=FieldValue('Month',2))'}>}Expression1) – за второй месяц
- Label: =FieldValue('Month',2)
-
sum({<Month*={'$(=FieldValue('Month',3))'}>}Expression1) – за третий месяц
- Label: =FieldValue('Month',3)
-
sum({<Month*={'$(=FieldValue('Month',4))'}>}Expression1) – за четвертый месяц
- Label: =FieldValue('Month',4)
-
sum({<Month*={'$(=FieldValue('Month',5))'}>}Expression1) – за пятый месяц
- Label: =FieldValue('Month',5)
- и т. д. до 12, так как мое измерение будет иметь только 12 значений. Если вам нужны более или менее столбчатые значения, вам нужно создать больше или меньше таких показателей/столбцов.
-
sum({<Month*={"*"}>}Expression1) – это для столбца «Итого»
- Label: TotalMonth
В результате я получаю таблицу, которая уже будет выглядеть примерно так, как моя сводная таблица, но будет вести себя немного иначе.
Сводная таблица (вверху) в сравнении с обычной таблицей (внизу)
Следующим шагом является создание флагов, которые помогут определить, сколько строк мы должны объединить в одну ячейку таблицы, чтобы создать вид, похожий на сводную таблицу. Мне нужно было сделать это для первого и второго измерения, так как у меня всего 3 измерения, создающих строки. Для этого я использую следующие выражения:
-
Count(DISTINCT Total <Year> Dim2)
- Label: RS_1
-
Count(DISTINCT Total <Dim1> Dim2)
- Label: RS_2
Приведенные выше выражения возвращают количество строк, которые мне нужно сгруппировать для каждого измерения.
6 строк необходимо объединить для столбца Year и 2 строки должны быть объединены для столбца Dim1 – это количество может меняться в зависимости от выбора
На следующем этапе я создаю еще 2 выражения. Первое – создать конкатенированное выражение, которое будет возвращать теги html <td> на основе существующих значений в измерениях таблицы. Оно также будет учитывать любой выбор в вашей модели данных. Для этого я использую функцию IF, которая предполагает, что я знаю, какими будут имена столбцов, и использую ссылку на имя столбца, чтобы упростить его. В то же время я мог бы использовать вместо этого функцию Column().
If(sum(Total {<Month*={'$(=FieldValue('Month',1))'}>}Expression1)<>0,'<td align="right">'&Jan&'</td>')
&If(sum(Total {<Month*={'$(=FieldValue('Month',2))'}>}Expression1)<>0,'<td align="right">'&Feb&'</td>')
&If(sum(Total {<Month*={'$(=FieldValue('Month',3))'}>}Expression1)<>0,'<td align="right">'&Mar&'</td>')
&If(sum(Total {<Month*={'$(=FieldValue('Month',4))'}>}Expression1)<>0,'<td align="right">'&Apr&'</td>')
&If(sum(Total {<Month*={'$(=FieldValue('Month',5))'}>}Expression1)<>0,'<td align="right">'&May&'</td>')
&If(sum(Total {<Month*={'$(=FieldValue('Month',6))'}>}Expression1)<>0,'<td align="right">'&Jun&'</td>')
&If(sum(Total {<Month*={'$(=FieldValue('Month',7))'}>}Expression1)<>0,'<td align="right">'&Jul&'</td>')
&If(sum(Total {<Month*={'$(=FieldValue('Month',8))'}>}Expression1)<>0,'<td align="right">'&Aug&'</td>')
&If(sum(Total {<Month*={'$(=FieldValue('Month',9))'}>}Expression1)<>0,'<td align="right">'&Sep&'</td>')
&If(sum(Total {<Month*={'$(=FieldValue('Month',10))'}>}Expression1)<>0,'<td align="right">'&Oct&'</td>')
&If(sum(Total {<Month*={'$(=FieldValue('Month',11))'}>}Expression1)<>0,'<td align="right">'&Nov&'</td>')
&If(sum(Total {<Month*={'$(=FieldValue('Month',12))'}>}Expression1)<>0,'<td align="right">'&Dec&'</td>')
&If(sum(Expression1)<>0,'<td>'&TotalMonth&'</td>')
Второе и в то же время самое важное выражение – это выражение, которое будет создавать комбинированный HTML-код для измерений и показателей. Здесь используется row-span для объединения ячеек для Year и Dim 1 на основе вычисленных значений RS_1 и RS_2.
If(Year<>Above(Total Year),
'<td align="left" rowspan="'&RS_1&'">'&Concat(DISTINCT Year)&'</td>'
&If(Dim1<>Above(Total Dim1),'<td align="left" rowspan="'&RS_2&'">'&Concat(DISTINCT Dim1)&'</td>'
&'<td align="left">'&Dim2&'</td>'
&Column(1),
'<td align="left">'&Dim2&'</td>'
&Column(1)),
If(Dim1<>Above(Total Dim1),'<td align="left" rowspan="'&RS_2&'">'&Concat(DISTINCT Dim1)&'</td>'
&'<td align="left">'&Dim2&'</td>'
&Column(1),
'<td align="left">'&Dim2&'</td>'
&Column(1)))
Для этого я также должен создать правильный заголовок, чтобы заголовки сводной таблицы создавались правильно. Статические заголовки: Год Year, Dimension 1, Dimension 2 и Total. Между Dimension 2 и Total есть заголовки переменных, которые будут создавать метки для месяцев на основе выбора.
'<th align="left">Year</th><th align="left">Dimension 1</th><th align="left">Dimension 2</th>'&Concat(DISTINCT '<th align="right">'&Month&'</th>','',num(Month))&'<th align="right">Total</th>'
Последние два выражения можно записать как одно выражение. Я специально использовал два выражения, так как намного легче увидеть, как все это работает, когда все это разбито на отдельные части.
Теперь, когда у нас есть последнее выражение, все, что нам нужно сделать, это использовать его в шаблоне NPrinting. В NPrinting создайте отчет HTML и отредактируйте его в NPrinting Designer. Внесите в таблицу объект, над которым мы работали. Вот ключевые шаги, которые вам необходимо сделать:
- Отключить «Сохранять исходные форматы».
- Отключить «HTML-кодирование»
- Показать заголовок, поскольку он содержит строку заголовка нашей сводной таблицы.
- Перетащить тот столбец из таблицы, который содержит всю логику для заполнения данных в нашей сводной таблице HTML.
Не стесняйтесь копировать и вставлять приведенный ниже код, чтобы получить простую версию таблицы. Вы можете захотеть отформатировать свой стиль CSS по собственному усмотрению – я оставляю вам такую возможность. Я не обращал внимания на ширину столбца или цвет фона. Я также не проверял совместимость CSS со всеми браузерами и почтовыми клиентами. Ниже вы заметите, что общий столбец отображается по-разному в веб-браузере и в MsOutlook. Я просто хотел показать вам саму концепцию. Также посмотрите на скриншот ниже, на котором показано, как это будет выглядеть после того, как вы начнете фильтровать свои данные.
<html>
<body>
<style>
th {
display: table-cell;
padding: 5px;
background-color: #EEEEE2;
}
/* make the last cell of every row italic */
tr td:LAST-CHILD{
font-weight:bold;
background-color: #EEEEE2;
text-align: right;
}
td {
display: table-cell;
padding: 5px;
}
table { margin-left:auto; margin-right:auto;}
table caption {font-weight:bold;}
</style>
<table border ='2' cellspacing=0 style="border-collapse:collapse;" >
<tr>
%%=$(vDetail)%%
</tr>
</table>
</body>
</html>Это конечный результат. Я знаю, что создание простой сводной таблицы – большая проблема. Но я также убежден, что на это нужно будет потратить усилия, особенно когда вам нужно встроить его в тело письма. Такая таблица будет хорошо смотреться в различных почтовых клиентах и будет в некоторой степени более читаемой.
Предварительный просмотр в веб-браузере
Предварительный просмотр в Outlook
Отфильтрованная сводная таблица HTML, содержащая только данные за 2020 год
Надеюсь, вы сочтете этот пост полезным. Прикрепленное приложение Qlik Sense здесь
Удачи вам!
