«Блеск и нищета» сводных таблиц

Часть 9. Создание отчета со сложной структурой

Павел Сухарев

Настоящий материал подводит итог всего цикла статей, посвященного основным функциям КУБ(): КУБЭЛЕМЕНТ(), КУБСВОЙСТВОЭЛЕМЕНТА(), КУБПОРЭЛЕМЕНТ(), КУБМНОЖ(), КУБЧИСЛОЭЛМНОЖ(). Нам останется изучить только последнего представителя этого семейства — КУБЭЛЕМЕНТКИП(), который стоит особняком и предназначен для работы с ключевыми индикаторами производительности (КИПами).

В статье рассказывается о практическом воплощении идей, которые обсуждались в предыдущих частях. В ней описывается, как построить сложный динамический отчет, используя для этого только операторы КУБ() в различных комбинациях. Под «сложной структурой» здесь понимается пользовательский отчет, удовлетворяющий двум условиям:

• на одной оси в нем размещаются сразу два измерения;
• для каждого из этих измерений имеется возможность менять состав, включенных в форму элементов.

Перечисленные требования на первый взгляд не кажутся слишком сложными для реализации. Если бы речь шла о формировании отчета с помощью инструмента сводных таблиц, это бы соответствовало действительности. Чтобы получить желаемый макет сводной таблицы, нужно всего лишь перенести по принципу drag and drop выбранные поля в область строк в форме настройки. Но на самом деле размещение двух или более измерений на одной оси означает формирование набора из многомерных кортежей. В случае сводных таблиц такая работа скрыта от пользователя — ее делает в фоновом режиме сервис Pivot Service. А в случае применения функций КУБ() ее уже придется выполнять вручную. Поэтому создание, казалось бы, простого отчета на базе класса функций КУБ() представляет определенную проблему, которая серьезно усугубляется внутренними ограничениями данного интерфейса. Функции КУБ() ориентированы на отображение одномерных данных. Поэтому полностью показать с их помощью кортежи из нескольких элементов не получится.

Тем не менее данная задача успешно решается. В предыдущей статье мы обсудили идею, которая вкратце сводилась к размещению в одном отчете нескольких экземпляров функции КУБПОРЭЛЕМЕНТ(). При этом используемые в функциях наборы должны отличаться лишь порядком следования измерений в кортеже. В MDX нет оператора, позволяющего напрямую переставлять элементы в кортежах. Однако желаемую операцию все­таки можно выполнить посредством «обходного маневра» — с помощью оператора извлечения множеств EXTRACT(). Покажем, как эти знания можно применить на практике.

Будем считать, что перед нами стоит задача создания отчета, детализирующего доходы компании по месяцам и типам клиентов. Поэтому начать следует с формирования двух базовых наборов, каждый из которых содержит элементы только одного измерения — «Дата» или «Клиенты». Мы хотим, чтобы они составлялись путем выбора пользователем значений из некого базового множества. В предыдущей статье было показано, что такую возможность можно предоставить, включая в набор неопределенные элементы.

На рис. 1 исходные элементы измерений представлены двумя группами пользовательских элементов формы типа «флажок». Каждая из групп замкнута на собственный диапазон ячеек, в котором определяющим является столбец G. В нем в зависимости от выбора пользователя, стоит наименование соответствующего элемента измерения либо заглушка “Null”. Обращаем внимание, что если в группе измерений содержится несколько иерархий, то наименования элементов следует указывать полностью. В противном случае MDX-выражения, создаваемые на следующих этапах, могут отрабатывать некорректно. Поэтому на рис. 1 названия месяцев приводятся с префиксом “[Месяц].”.

Рис. 1. Исходный отчет

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

Выражение 1

=СЦЕПИТЬ(«{«;G6;»,»;G7;»,»;G8;»,»;G9;»,»;G10;»,»;G11;»}»)

Для удобства последующей работы ячейке, содержащей выражение 1, присвоим собственное имя «Месяцы». Назначение имени производится в диалоговом окне Диспетчер имен, которое вызывается одноименной командой на ленте Формулы либо сочетанием клавиш Ctrl+F3. Для случая, представленного на рис. 1, в ячейке «Месяцы» будет создана строка, описывающая набор элементов из измерения «Дата»:

Набор 1

{[Месяц].[Янв],[Месяц].[Фев],Null,Null,Null,[Месяц].[Июн]}

Обратите внимание, что набор, созданный с использованием функции СЦЕПИТЬ(), имеет динамическую природу. Если пользователь решит изменить выделение месяцев на пользовательской форме, это сразу же отразится на итоговом множестве.

Аналогичным образом поступим с набором, в котором перечисляются типы клиентов.

Имея в своем распоряжении два одномерных набора, можно переходить к операции их объединения в одно множество посредством операции CrossJoin. Напишем в MS Excel следующую формулу:

Выражение 2

=СЦЕПИТЬ(Месяцы;»*»;Клиенты)

Операция, заданная в выражении 2, генерирует множество, содержащее все возможные комбинации элементов из исходных множеств. Для случая, представленного на рис. 1, итогом работы выражения 2 будет строка следующего вида:

Выражение 3

{[Месяц].[Янв],[Месяц].[Фев],Null,Null,Null,[Месяц].[Июн]}*{[SME],[SOHO],[LE]}

Выражение 3 определяет набор, состоящий уже из двухэлементных кортежей, в котором на 1-м месте стоит название месяца, а на втором — тип клиента (набор 2):

Рис. 2. Отчет, содержащий правила условного форматирования

Набор 2

{

([Месяц].[Янв], [SME]),

([Месяц].[Янв], [SOHO]),

([Месяц].[Янв], [LE]),

([Месяц].[Фев], [SME]),

([Месяц].[Фев], [SOHO]),

([Месяц].[Фев], [LE]),

([Месяц].[Июн], [SME]),

([Месяц].[Июн], [SOHO]),

([Месяц].[Июн], [LE])

}

Заметим, что строка с выражением 3 подходит для применения в различных функциях КУБ() в роли выражения множества. Ячейке с выражением 3 также присвоим собственное имя, например «Месяцы_Клиенты». Во втором столбце итогового отчета (на рис. 1 это столбец F) напишем формулу, последовательно показывающую элементы из набора 2.

Выражение 4

=КУБПОРЭЛЕМЕНТ(«OLAP_Connection»;Месяцы_Клиенты;D29)

Как видно из рис. 1, на листе MS Excel отображаются только последние элементы из каждого кортежа. Данное обстоятельство является самым существенным ограничением при создании сложных отчетов.

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

Выделим на листе новую ячейку и назовем ее “Клиенты_Месяцы”. В этой ячейке напишем выражение следующего вида:

Выражение 5

=СЦЕПИТЬ(«Extract(«;Месяцы_Клиенты;»,[Клиент],[Дата].[Месяц])»)

В нашем случае выражение 5 выдаст следующую строку:

Выражение 6

Extract({[Месяц].[Янв],[Месяц].[Фев],Null,Null,Null,[Месяц].[Июн]}*{[SME],[SOHO],[LE]},[Клиент],[Дата].[Месяц])

Несмотря на устрашающий вид выражения 6, принцип его работы довольно прост: оно извлекает из ранее созданного набора кортежи, размещая на первом месте элементы из иерархии «Клиент», а затем — «Дата.Месяц». Поэтому выражение 6 переделывает исходный набор 2 в новый формат (набор 3):

Набор 3

{

([SME], [Месяц].[Янв]),

([SOHO], [Месяц].[Янв]),

([LE], [Месяц].[Янв]),

([SME], [Месяц].[Фев]),

([SOHO], [Месяц].[Фев]),

([LE], [Месяц].[Фев]),

([SME], [Месяц].[Июн]),

([SOHO], [Месяц].[Июн]),

([LE], [Месяц].[Июн])

}

Элементы из набора 3 также можно вывести на лист MS Excel. В очередной раз воспользуемся функцией КУБПОРЭЛЕМЕНТ(). В первом столбце итогового отчета (столбец Е) напишем формулу:

Выражение 7

=КУБПОРЭЛЕМЕНТ(«OLAP_Connection»;Клиенты_Месяцы;D29)

Оператор КУБПОРЭЛЕМЕНТ для набора, указанного в качестве его входного аргумента, опять покажет только последние элементы из каждого кортежа. Но в этот раз ими будут названия месяцев.

Совместно столбцы E и F книги показывают на листе MS Excel исходный набор 2 полностью. В общем на этом решение поставленной задачи можно было бы считать законченным. Но получившийся отчет вызывает ряд нареканий с позиций эргономики и удобства практического использования.

Во­первых, таблицу отчета пришлось сделать длиной в 18 строк, чтобы гарантировать отображение в пользовательской форме множества, получаемого при декартовом перемножении полных наборов из месяцев и типов клиентов (6 месяцев на 3 типа клиентов). Но когда пользователь выбирает не все месяцы либо типы клиентов, то мощность получаемого набора оказывается меньше максимального, поэтому функции КУБПОРЭЛЕМЕНТ() и КУБЗНАЧЕНИЕ() будут возвращать значения ошибки «#Н/Д» для строк со старшими индексами.

Во­вторых, более наглядным представляется принцип последовательной развертки измерений, реализованный в обычных сводных таблицах. В этом случае не показываются повторяющиеся названия месяцев в столбце Е, а строки, относящиеся к разным периодам, разделяются линиями.

К счастью, доработать исходный отчет и устранить перечисленные замечания не составляет особого труда. Достаточно добавить несколько правил форматирования, в которых условие форматирования задается при помощи формул.

Для использования в роли параметров в будущих правилах нам понадобятся две новые именованные ячейки. Первую из них назовем «Начало_диапазона» и проставим в нее номер строки, содержащей заголовки таблицы отчета. В нашем случае такая строка имеет номер 28. Второй ячейке присвоим имя «Число_Элементов», а ее значение определим при помощи следующей формулы:

Выражение 8

=КУБЧИСЛОЭЛМНОЖ(КУБМНОЖ(«OLAP_Connection»;Месяцы_Клиенты))

Назначение выражения 8 заключается в подсчете количества элементов для текущего ракурса набора 2. В частности, для набора, представленного на рис. 2, число элементов будет равно 12.

Зная номер строки на листе Excel, с которого начинается вывод множества, а также общее число входящих в него элементов, можно составить правило условного форматирования, скрывающее текст во всех ячейках, находящихся за пределами рабочего диапазона. Для этого выделим всю внутреннюю область значений аналитического отчета. На рис. 2 данная область представлена ячейками E29:H46. Затем на ленте выберем вкладку Главная, а в ней, в группе команд Стили, — команду Условное форматирование. В раскрывающемся списке нас интересует команда Создать правило, а в появившемся меню — тип правила Использовать формулу для определения форматируемых ячеек (рис. 3).

Рис. 3. Настройка правила форматирования, использующего формулу

Для создания нового правила форматирования требуется определить всего два параметра. Первый из них — логическая формула. В том случае, когда она принимает значение «Истина», выполняется форматирование ячеек, вид которого задается во втором параметре.

Отбор ячеек будем выполнять при помощи выражения 9.

Выражение 9

=СТРОКА()-Начало_Диапазона>Число_Элементов

Формула для каждой ячейки из диапазона E29:H46 рассчитывает соответствующий ей абсолютный порядковый номер строки на листе MS Excel. Затем при помощи корректирующего параметра “Начало_Диапазона” полученные номера строк нормируются и приводятся к простому списку от 1 до 18, что соответствует номерам строк в отчете. Понятно, что правило форматирования должно срабатывать только для тех строк из отчета, порядковый номер которых превышает общее число элементов в наборе. Поэтому в выражении 9 условие проверки задается оператором «Больше».

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

Затем сделаем так, чтобы строки отчета, относящиеся к разным месяцам, разделялись пунктирными линиями. Повторно воспользуемся условным форматированием и создадим еще одно правило для диапазона E29:H46 (рис. 4).

Рис. 4. Добавление в таблицу отчета разделительных линий

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

Выражение 10

=$E29<>$E28

Выражение 10 описывает следующее правило: «Если название месяца в текущей строке не совпадает с названием месяца в строке, находящейся выше на одну позицию, верхнюю границу текущей ячейки следует выделить пунктирной линией. В отношении записи выражения 10 есть один нюанс, на который необходимо обратить внимание читателей. В формуле применялись адреса конкретных ячеек, но из этого не следует, что форматирование всего диапазона определяется значениями в ячейках E29 и E28. Дело в том, что правило форматирования для всего диапазона можно задать при помощи формулы, в которой участвует любая ячейка из этого множества. В нашем примере правило было сформулировано для крайней левой ячейки, но на эту роль подходит и любая другая ячейка из столбца E.

В качестве завершающего штриха настроим в нашем отчете развертку измерений по образу и подобию сводных таблиц. Когда в области строк или столбцов сводной таблицы присутствует сразу несколько измерений, они раскрываются следующим образом: для каждого элемента «старшего» (расположенного на макете отчета левее либо выше) измерения показываются все элементы «младшего» (расположенного на макете отчета правее либо ниже). Нужно понимать, что речь здесь идет всего лишь о способе представления данных. Исходный набор, который в итоге будет отправлен на сервер, остается неизменным, но пользователю он показывается в удобном для восприятия виде, например в виде иерархического списка. Двумерный набор переделывается в раскрывающийся список посредством простого правила форматирования: «Если текущий элемент старшего измерения совпадает с тем, что расположен на одну строку выше, его следует скрыть в пользовательской форме». Аналитически условие такой проверки описывается формулой = ($E29=$E28) — рис. 5. Новое правило форматирования должно применяться не ко всему диапазону, а только к столбцу с элементами старшего измерения. В нашем случае это столбец E, в котором показываются месяцы.

Рис. 5. Просмотр всех правил условного форматирования

Совместное действие трех правил форматирования обеспечивает представление исходного отчета в виде, который максимально приближен к привычным сводным таблицам. Возникает вопрос — стоила ли игра свеч? Ведь такой же отчет при помощи сервиса «Сводные таблицы» создается гораздо быстрее и проще. Мы уверены, что в ряде случаев подход, основанный на применении функций КУБ(), является оправданным и полезным. Операторы КУБ() позволяют работать с наборами произвольной структуры, чего так не хватает в сводных таблицах. Платой за универсальность служат серьезные ограничения в части настройки форм. Но, как видите, при наличии знаний и определенной настойчивости все они успешно преодолеваются, что открывает перед пользователями широкие возможности для решения практических задач.

КомпьютерПресс 07'2012