Блеск и нищета сводных таблиц. Часть 7

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

В прошлых статьях цикла были рассмотрены две базовые функции семейства КУБ() — КУБЗНАЧЕНИЕ() и КУБЭЛЕМЕНТ(). Потенциала, заложенного в этих функциях, достаточно для построения в среде Microsoft Excel сводных отчетов практически произвольного формата. Более того, логика исполнения функций предполагает использование в качестве аргументов значений из обычных ячеек листа книги, что открывает перед пользователем широкие возможности по созданию отчетов, объединяющих данные из разнородных источников.

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

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

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

{

[1КВ],

[Янв],

[Фев],

[Мар],

[2КВ],

[Апр],

[Май],

[Июн]

}

Набор 1

На листе книги Microsoft Excel подобный набор можно составить путем многократного последовательного вызова функции КУБЭЛЕМЕНТ(): в ячейку A1 ввести формулу КУБЭЛЕМЕНТ(“OLAP_Connection”;”[1КВ]”), в ячейку A2 — формулу КУБЭЛЕМЕНТ(«OLAP_Connection»;»[Янв]») и т.д.

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

Адаптировать отчетную форму II квартала, собранную при помощи операторов КУБЭЛЕМЕНТ(), в пригодный для применения в III квартале вид будет непросто. Для этого придется заново переписать формулы всего диапазона. В ячейке A1 существующую формулу заменить на КУБЭЛЕМЕНТ(“OLAP_Connection”;”[2КВ]”), в ячейке A2 — на выражение КУБЭЛЕМЕНТ(«OLAP_Connection»;»[Апр]») и т.д. Понятно, что при переходе из III в IV квартал трудоемкую операцию по переделыванию макета отчетной формы придется повторить еще раз.

Каким образом можно выйти из сложившейся тупиковой ситуации? Для начала вспомним о богатых возможностях языка MDX. Набор 1 составляется двумя способами. Первый из них — прямое перечисление входящих в него элементов; на этом методе базируется работа функции КУБЭЛЕМЕНТ(). Альтернативный подход можно условно назвать «аналитическим». Набор 1 описывается посредством простого MDX-выражения (Выражения 1):

DrilldownLevel({[1КВ]:[2КВ]})

Выражение 1 определяет множество элементов многомерного пространства. В предыдущей статье мы кратко рассказали, как формировать такие множества на стороне клиентского приложения. Для этих целей в состав семейства КУБ() введена мощная функция КУБМНОЖ(). Вызов КУБМНОЖ(«OLAP_Connection»; «DrilldownLevel({[1КВ]:[2КВ]})») создает в книге Microsoft Excel массив элементов, соответствующий Набору 1. Для полноценной работы с ним требуется разместить его отдельные члены в ячейках электронной таблицы. Данная операция выполняется при помощи функции КУБПОРЭЛЕМЕНТ(), имеющей простой синтаксис:

КУБПОРЭЛЕМЕНТ(подключение, выражение_множества, номер, подпись).

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

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

Аргумент функции «Номер», согласно справке Excel, «является целочисленным значением, определяющим наивысшее значение, которое будет возвращено…». За этим расплывчатым определением на самом деле скрывается понятие ранга элемента в наборе. Формальное определение ранга будет дано несколько позже. А сейчас, обсуждая одномерные наборы, под «Номером» мы будем понимать порядковый номер элемента в исходном наборе.

Так, «Номер» элемента [1КВ] в Наборе 1 равен «1», а элемента [Апр] — «6».

Последний аргумент «Подпись» является необязательным. В нем указывается строка, которая показывается на листе книги Microsoft Excel вместо возвращаемого значения. Такая опция бывает полезной, если пользователю нужно вывести в отчете что­нибудь отличное от ID или внутреннего имени элемента.

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

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

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

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

Функция КУБПОРЭЛЕМЕНТ() не будет работать для следующих наборов:

{

[Янв],

[Фев],

[Дир_1]

}

Поиск по сайту: