Sparkline і користувальницькі агрегати

Все добре, скажете ви, прочитавши попередній пост. Однак описаний сценарій припускає, що ми заносимо в Репортинг розгорнуту з обох вимірів таблицю фактів, яку він сам групує за часом і продуктам. Це логічно, тому інакше як він побудує графік уздовж часу в кожному рядку? У той же час даний підхід припускає (бо скоро таблиця фактів розгорнута), що агрегати вважаються всередині Репортинг. Як бути, якщо в колонці “Гроші” на Рис.12 попереднього посту по бізнес-логіці потрібно не сума, а який-небудь більш хитрий агрегат, реалізувати який засобами Репортинг проблематично?

Припустимо, є деякий датасет, що показує користувальницький агрегат уздовж вимірювання “Продукти”. Наприклад, останні ненульові місячні продажу:

if object_id(“LastNonEmptyMonthBySubcategory”, “V”) is not null drop view LastNonEmptyMonthBySubcategory

go

create view LastNonEmptyMonthBySubcategory as

with cte (Рід, Вид, Рік, Місяць, n, ПользАгрегат) as (

select pc.EnglishProductCategoryName, psc.EnglishProductSubcategoryName, d.CalendarYear, d.MonthNumberOfYear,

row_number() over (partition by pc.EnglishProductCategoryName, psc.EnglishProductSubcategoryName order by d.CalendarYear desc, d.MonthNumberOfYear desc),

sum(s.SalesAmount) from dbo.FactInternetSales s

join dbo.DimProduct p on s.ProductKey = p.ProductKey

join dbo.DimProductSubcategory psc on p.ProductSubcategoryKey = psc.ProductSubcategoryKey

join dbo.DimProductCategory pc on psc.ProductCategoryKey = pc.ProductCategoryKey

join dbo.DimDate d on s.OrderDateKey = d.DateKey

group by pc.EnglishProductCategoryName, psc.EnglishProductSubcategoryName, d.CalendarYear, d.MonthNumberOfYear

)

select Рід, Вид, ПользАгрегат from cte where n = 1

go

select * from LastNonEmptyMonthBySubcategory

Скрипт 1

Напевно, приклад можна було придумати краще, тому що подібний агрегат, як і суму, теж можна реалізувати всередині Репортинг, але не будемо на це відволікатися. Нехай є датасет виду “Категорія продукту”, “Підкатегорія”, “Деяка величина”, який би ми хотіли відобразити в звіті, а як ця величина порахована – справа, за великим рахунком, десяте.

image

Рис.1

У звіті ми б хотіли додати до датасету колонку зі спарклайном, що відображає поведінку деякої (взагалі кажучи, інший) чисельної характеристики для кожної підкатегорії продукту уздовж ортогонального вимірювання. Це означає, що нам потрібно в датасете помножити вимір Продукт на це вимір і додати до твору нову міру. Нехай ортогональним твором, який простягнеться вздовж осі Х спарклайна, як і в попередньому пості, залишається Час, а чисельної характеристикою (вісь Y) буде продаж. Додаємо продажу в розрізі по часу і підкатегоріями в датасет Рис.1, сджойнів його з датасетом Скрипт 1 з попереднього поста:

with ДатасетІзПредидущегоПоста as (

select pc.EnglishProductCategoryName Рід, psc.EnglishProductSubcategoryName Вид, d.CalendarYear Рік, d.MonthNumberOfYear Місяць, sum (s.SalesAmount) Гроші from dbo.FactInternetSales s

join dbo.DimProduct p on s.ProductKey = p.ProductKey

join dbo.DimProductSubcategory psc on p.ProductSubcategoryKey = psc.ProductSubcategoryKey

join dbo.DimProductCategory pc on psc.ProductCategoryKey = pc.ProductCategoryKey

join dbo.DimDate d on s.OrderDateKey = d.DateKey

group by pc.EnglishProductCategoryName, psc.EnglishProductSubcategoryName, d.CalendarYear, d.MonthNumberOfYear

)

select t1.Род, t1.Від, t2.Год, t2.Месяц, t1.ПользАгрегат, t2.Деньгі from LastNonEmptyMonthBySubcategory t1

join ДатасетІзПредидущегоПоста t2 on t1.Род = t2.Род and t1.Від = t2.Від

order by 1, 2, 3, 4

Скрипт 2

image

Рис.2

Користувальницький агрегат всередині кожної підкатегорії продукту є константою уздовж часу, тому, за умовою, він залежить тільки від виміру Продукт. Чудово. Переносимо датасет в звіт. Аналогічно Рис.3-5 попереднього посту, зробіть матрицю, згруповану по категорії продукту (Рід), підкатегорії (Вид) і перенесіть в комірку з сірої написом Data поле ПользАгрегат. За замовчуванням йому буде присвоєна агрегатна функція Sum. Змініть її на

= First (Fields! ПользАгрегат.Value, “Вид”)

Скрипт 3

image

Рис.3

Вираз означає, що замість підсумовування ми будемо брати перше значення всередині кожної групи підкатегорій, яка у нас називається Вид. Як зазначалося вище (Рис.2), всередині підкатегорій (в розрізі по часу) ПользАгрегат не змінюється, так що тут без різниці, перше значення брати в групі або останнім.

Праворуч від колонки Вид додаємо в матрицю нову колонку в межах цієї групи:

image

Рис.4

і кладемо в неї sparkline аналогічно Рис.8-10 попереднього посту.

image

Рис.5

По осі Y пускаємо поле Гроші, по осі Х – згруповані по роках місяці. Дивимося в Preview, що вийшло:

image

Рис.6

По-моєму, в акурат те, що замовляли.

Розглянемо другий спосіб, який з точки зору проектування звіту практично нічим не буде відрізнятися від того, що ми тільки що проробили, тому полягає в тому, щоб вважати користувальницькі агрегати не в реляційній базі, а в кубику. На основі реляційної бази AdventureWorksDW2008R2 мається багатовимірна база, яку можна взяти всі там же. Напишемо MDX-запит, який робить ідейно рівно те ж, що і Скрипт 1 + Скрипт 2: він видає підкатегорії продуктів з деяким призначеним для користувача агрегатом LastNonEmptyMonth (це основа майбутньої матриці в Репортинг) і всередині кожної підкатегорії розгортає ще динаміку продажів по місяцях року (це по чому буде будуватися спарклайн в окремій клітинці навпроти кожної підкатегорії):

with member Measures.LastNonEmptyMonth as Tail(nonempty([Date].[Calendar].[Month].Members * [Measures].[Internet Sales Amount]), 1).Item(0)

select {[Measures].[Internet Sales Amount], Measures.LastNonEmptyMonth} on 0,

nonempty ([Product].[Product Categories].[Subcategory].Members * [Date].[Calendar].[Month].Members, [Measures].[Internet Sales Amount]) on 1

from [Adventure Works]

Скрипт 4

image

Рис.7

Аналогічно, перетягуємо цей датасет в звіт, попередньо завівши у звіті нове джерело даних для Analysis Services:

image

Рис.8

Розробники Reporting Services постаралися максимально полегшити введення MDX-запитів і створили спеціальний дизайнер для їх побудови, тому просто так текст запиту ввести не можна. Можна натиснути на значок функції, але тоді текст запиту буде вважатися виразом, і список полів недоступний.

image

Рис.9

Доводиться все-таки зайти в дизайнер запитів (кнопка Query Designer), віджати в рядку меню значок Design Mode, після чого стає можливо ввести текст довільного MDX-запиту та його виконати:

image

Рис.10

Зверніть увагу, що датасет виходить повніше, ніж ми бачили на Рис.7. У ньому присутні не тільки члени замовлених в запиті рівнів вимірів, наприклад, Subcategory, але й автоматично підтягуються колонки MEMBER_NAME з батьківських рівнів, наприклад, Category.

Цілком аналогічно кидаємо на звіт матрицю, натаскувати в неї порядкову групу з поля Category і дочірню по відношенню до неї з поля Subcategory, переносимо в область Data поле LastNonEmptyMonth, що символізує собою користувальницький агрегат по підкатегоріями, заходимо у нього в Expression і прибираємо функцію Sum, яку автоматично норовить підставити Report Designer.

image

Рис.11

Додаємо праворуч ще одну колонку в межах поточної групи:

image

Рис.12

в яку переносимо Sparkline, пускаючи аналогічно Рис.5 поле Internet Sales Amount уздовж вертикальної осі графіка, а Calendar_Year і Month – уздовж горизонтальної.

image

Рис.13

Рассплітім аналогічно Рис.11 попереднього посту заголовну осередок Last Non Empty Month і вобьем над колонкою графіків заголовок “Динаміка продажів”. Отцентріруем і виділимо жирним кольором заголовки, отформатіруем числову осередок = Fields! LastNonEmptyMonth.Value:

image

Рис.14

Дивимося в Preview, що вийшло:

image

Рис.15

Операція пройшла добре, шкода тільки, що хворий про це не дізнається. Мені здається, зовнішній вигляд графіків не дуже збігається з тим, що ми бачили на Рис.6. Наприклад, там навпроти Bike Racks тренд в кінці йшов на спад, а тут, навпаки, радісно росте. По цифрах (див. Рис.2, колонки Рік, Місяць, Гроші або Рис.7, Internet Sales Amount) виходить, що в першому випадку графіки більше були схожі на правду, а тут sparkline показує якусь лажу. Щоб з’ясувати, в чому справа, можна проконвертувати sparkline в повноцінний chart з підписами уздовж осей, як робилося в попередньому пості, Рис.14. На цей раз ми поступимо по-іншому. Додамо в матрицю поля Calendar_Year і Month у вигляді колонок після Subcategory:

image

image

Рис.16

Всі подивилися на колонку Month і всі зрозуміли. З якоїсь причини вона вирішила місяці впорядкувати за алфавітом, хоча в датасете (Рис.10) все нормально.

Звично вирушаємо в властивості sparkline і вибираємо властивості Category Group, відповідної Month. Встаємо на закладку Sorting, в ній – на сходинку Sort by [Month] і тиснемо кнопку Delete.

image

image

Рис.17

Повертаємося до матриці і видаляємо з неї за непотрібністю колонки Calendar Year і Month разом з асоційованими з ними групами, т.к. ми вже розібралися, де тут собака порилася.

image

image

Рис.18

Дивимося в черговий раз Preview:

image

Рис.19

По-моєму на цей раз все нормально.

Примітка. У моєму випадку після видалення сортування спарклайни залишалися виглядати по-старому, і ніякої refresh не допомагав. Я списую це на глюк Стр3. Довелося викинути sparkline з осередку, занести знову, повторити прив’язку полів Рис.13, після цього відразу видалити в ньому сортування для групи Month Рис.17 і тільки після цього сказати Preview.

Схожі статті:


Сподобалася стаття? Ви можете залишити відгук або підписатися на RSS , щоб автоматично отримувати інформацію про нові статтях.

Коментарів поки що немає.

Ваш отзыв

Поділ на параграфи відбувається автоматично, адреса електронної пошти ніколи не буде опублікований, допустимий HTML: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>

*

*