Привет, Qlik-мир! Сегодня расскажу о «гибком отчёте».
Сразу перейду к сути дела и расскажу, что это за зверь такой, «гибкий отчет», и зачем его использовать. Итак, начнем!
Гибкий отчёт: что это такое и зачем он нужен
Гибкий отчёт – это динамический объект, который позволяет произвести объемный и разносторонний анализ данных в разрезе многих измерений и показателей. Проще говоря – это многомерная таблица, которая имеет показатели и измерения, и мы можем сами выбирать, какие показатели и измерения видеть в конечном отчете.
Нужен гибкий отчет нам для того, чтобы не создавать множество различных таблиц со своими показателями и измерениями на каждый случай, а сделать единую таблицу, предусматривающую различные аналитические сценарии. А в этой объединенной таблице (или гибком отчете) пользователь может сам выбирать нужные данные и способ их представления.
Гибкий отчёт довольно прост в написании и позволяет рассмотреть лишь те данные, которые нам нужны. Такой отчёт следует применять, если есть большой «чарт», в котором много столбцов, которые нужны в разное время разным людям.
Гибкий отчет: инструкция по созданию
ШАГ 1. Конечно, чтобы создать гибкий отчёт, нам нужны данные, по которым мы хотим построить его. На основе этих данных, используя «Chart» составляем полную таблицу со всеми необходимыми измерениями и показателями.
НА ЗАМЕТКУ! Не забываем прописывать показателям в Label правильные названия показателей, а не «SUM(Транзакции)-SUM(Транзакции Отменённые)».
Выписываем все названия измерений в столбик. Затем выписываем все Label, которые мы присвоили показателям.
Теперь нам нужно добавить к приложению 2 таблицы, состоящие из 1 столбца.
- Таблица, содержащая названия показателей.
- Таблица, содержащая названия измерений.
НА ЗАМЕТКУ! Стоит учесть, что для корректной работы нам нужно будет ещё значение, которое отображаться не будет (назову его dummy).
Вносим в скрипт следующие изменения
Dimensions:
1 2 3 4 5 6 7 8 9 10 11 12 13 |
LOAD * INLINE [ Dimension dummy Торговый Объект ID Торговый Объект Город Торговый Объект Регион Торговый Объект Страна Товар Артикул Товар Группа Ассортимента Товар Категория Ассортимента Товар Наименование Товар Пол ]; |
Measures:
1 2 3 4 5 6 7 8 9 10 |
LOAD * INLINE [ Measure dummy Сумма с НДС План Сумма с НДС План Сумма Средний Чек План Средний Чек План Колличество Чеков Колличество чеков ]; |
После внесения в скрипт изменений перезагружаем приложение.
ШАГ 2. Заставляем отчет работать. Итак, мы получили громоздкий отчёт и 2 таблицы с названиями. Теперь нам нужно сделать так, чтобы они друг с другом взаимодействовали.
Для начала создадим 2 объекта «Лист Бокс». По одному на каждую из наших 2-х новых таблиц.
Так как они ни с чем не связаны, то фильтрация по ним не приведёт к результатам.
Для того, чтобы наши фильтры не могли случайно повлиять на работу таблиц, то для них мы вводим альтернативное состояние и называем его так, как мы хотим. Я назову его viz.
Теперь присвоим это альтернативное состояние двум нашим объектам «Лист Бокс».
Хорошо, но ведь у нас всё ещё нет связи между отчётом, и этими таблицами, а введённое нами альтернативное состояние даже увеличило пропасть между ними.
Теперь начинается самая главная часть разработки. Для связи мы будем использовать переменные и макрос.
Создаём сначала 2 переменные. Например:
- vDimensions
- vMeasures
Теперь сделаем 2 макроса, которые будут изменять наши переменные так, чтобы мы могли из них вытащить нужные нам названия. Для этого открываем редактор макросов.
НА ЗАМЕТКУ! Чтобы быстро открыть редактор макросов используйте сочетание клавиш Ctrl+M или через меню программы QlikView Tool à Edit Module…
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 |
sub OnSelectShowDimensions If ActiveDocument.Evaluate("Count({1} [Dimension])-Count({viz} [Dimension])<>0") then ActiveDocument.Variables("vDimensions").SetContent ActiveDocument.Evaluate("Concat({viz} '|'&[Dimension]&'|', '')"),true else ActiveDocument.Variables("vDimensions").SetContent "",true end if end sub sub OnSelectShowMeasures If ActiveDocument.Evaluate("Count({1} [Measure])-Count({viz} [Measure])<>0") then ActiveDocument.Variables("vMeasures").SetContent ActiveDocument.Evaluate("Concat({viz} '|'&[Measure]&'|', '')"),true else ActiveDocument.Variables("vMeasures").SetContent "",true end if end sub |
Первый макрос для измерений OnSelectShowDimensions, а второй OnSelectShowMeasures соответственно для показателей.
После написания макросов жмём клавишу Test, чтобы убедится, что всё написано правильно.
Если всё правильно, то названия наших макросов появятся в левом верхнем окне, а сверху окна макросов будет надпись *** Ready ***.
Гибкий отчет: настраиваем фильтрацию
Теперь нам нужно сделать так, чтобы макросы срабатывали при выборке в лист боксах с названиями.
Для этого открываем Document Properties и переходим на закладку Triggers.
Среди всех полей находим наши поля и для каждого проделываем следующую процедуру:
Жмём на кнопку Add Action, которая находится под On Select
Нажимаем Add и выбираем в левом окне External, затем в правом — Run Macro и кликаем на OK.
Теперь в Macro Name вписываем соответствующий макрос. Для Dimension у меня это OnSelectShowDimensions, а для Measure OnSelectShowMeasures.
Теперь переменные, которые мы ввели, будут изменяться при выборе фильтров на наших двух объектах «Лист Бокс».
От статического отчета к гибкому
Отлично, но отчет пока все еще статический. Для того, чтобы превратить отчет в гибкий, используем нашу переменную.
Заходим в свойства отчёта, затем во вкладку Dimensions. И каждому нашему полю ставим галочку Enable Conditional и прописываем в окне ниже этой галочки такую строчку:
1 |
SubStringCount('$(vDimensions)','|*Название поля*|')>=1 |
где вместо *Название поля* вписываем именно название измерения.
И так проделываем для каждого измерения.
Затем проделываем эту процедуру с показателями на закладке Expressions. Используя вместо *Название поля* тот Label, который мы им присвоили. А vDimensions заменяем на vMeasures (галочка там именуется как Conditional).
Пример:
1 2 3 4 5 |
« SubStringCount('$(vMeasures)','|Сумма с НДС|')>=1 » |
Вот почти и все: у нас появился гибкий отчет в рабочем состоянии, но, как истинные BI-эстеты, мы не можем его оставить в таком виде:
Гибкий отчет: наводим красоту
Нам осталось немного навести красоту, и исключить ошибку, которая возникнет при полной выборке всех полей.
Для начала исключим ошибку. Для этого входим в свойства наших объектов «Лист Бокс» и заменяем Title на название Dimension или Measure. Значение Field устанавливаем <Expression> и вписываем в открывшееся окно следующее:
- для Dimension
-
1234=Aggr(Only({1<[Dimension]-={'dummy'}>}[Dimension]),[Dimension])
-
- для Measure
-
1234=Aggr(Only({1<[Measure]-={'dummy'}>}[Measure]),[Measure])
-
Итак, теперь у нас даже при полной выборке будет всё правильно отображаться. И лишнее значение, которое мы вводили теперь скрыто.
Но в таком виде всё ещё плохо смотрятся текст-боксы. Надо их сделать красивее. На мой взгляд, намного лучше будет, если они будут отображаться как Check Box. Для этого заходим в свойствах объекта на закладку Presentation и выбираем в выпадающем списке Selection Style Override значение LED Check boxes.
После всех этих процедур получаем красивый «гибкий отчёт».
В «гибком отчёте» мы сами выбираем, что мы хотим видеть, и у нас только нужная нам информация. Ничего лишнего.
На этом все, красоту навели, задачу выполнили. Жду ваши комментарии и до новых встреч.
Приветствую, заметка интересная, однако, вызывает пару вопросов — давно не пользовал эту историю…
Нельзя ли раскрыть тему ошибок поподробнее? Что за ошибка возникает при выборки всех значений в списке измерений?
Так же не очень понятно назначение значения dummy. Зачем было его добавлять, чтобы потом исключать из списков?:)
И, кстати, аналогичный пример, насколько я помню, реализован в документе Что нового в 11.0. Там вроде проще было — без макросов и состояний… Хотя там был «косяк» со схожими названиями полей, но он достаточно просто решаем…
1)
Чтобы посмотреть на ошибку можно просто не следовать инструкции и посмотреть что из этого выйдет =) .
Вот что получается:

Как мы видим, наша переменная стала ровна ‘’ (то есть ничему).
Причина тому кроется в строчке макроса:
Она принимает 0, когда ничего не выбрано или выбрано всё. «Count({1} [Measure])» берётся всегда 7 (так как в том состоянии мы не фильтруем поле).
«Count({viz} [Measure])» меняется каждый раз при фильтрации, но оно ровно 7, когда мы выбираем все или не выбираем ничего. Поэтому нам нужно чтобы при выборке всех наших значений не было ровно такому же, как без выборки, иначе мы получим 0, и наш макрос присвоит значение “” переменной.
2)
Есть разные варианты решения одной и той же задачи. Если перед вами стоит речка вы можете, переплыть, найти перешеек и перейти, ну или перепрыгнуть, если расстояние позволяет =),
Тут точно также. Есть несколько вариантов решения этой задачи, правда они различаются в основном только в том, как собирать переменные: использовать макрос или вычисляемые переменные сами по себе. Кстати, если вы используете самовычисляемые переменные, у вас они будут рассчитываться каждый раз при переключении любого фильтра, что может привести к потере производительности при работе с диаграммами не связанными с гибкой диаграммой.
И все таки, соглашусь с Анатолием, макросы и триггеры для решения такой задачи — скорее ненужное усложение решения, усложняющее портабельность.
Я в своей практике использую такой подход:
1) две рассчитываемых параметрических переменных:
2) две таблицы со списками измерений и показателей гибкого отчета (с поддержкой иерархичности):
3) и собственно чарт с гибким отчетом, в котором в свойство Conditional для dimensions и expressions записываются выражения вида:
Собственно все, ни макросов, ни триггеров, проверено на модели с количеством строк несколько сот миллионов — работает штатно.
Но вот с Alternate State для таблиц Data Island — это действительно ценный совет, недавно аналогичный прочитал у Rob Wunderlich, http://qlikviewcookbook.com/2015/06/the-impact-of-data-islands-on-cache-and-cpu/
Но вот с Alternate State для таблиц Data Island – это действительно ценный совет, недавно аналогичный прочитал у Rob Wunderlich, http://qlikviewcookbook.com/2015/06/the-impact-of-data-islands-on-cache-and-cpu/
Не совсем аналогичный. Да и вообще лучше вообще не делать островков данных, они очень сильно садят производительность.
Как аналог структуры данных для выпадающего списка можно использовать импутбокс с преднастроенным списком значений. Для остальных случаев проще хранить строки в переменных и парсить их по разделителям при надобности.