SELECT        pc.EnglishProductCategoryName AS Род, psc.EnglishProductSubcategoryName AS Вид, d.CalendarYear AS Год, d.MonthNumberOfYear AS Месяц, 
                         SUM(s.SalesAmount) AS Деньги 
FROM            FactInternetSales AS s INNER JOIN 
                         DimProduct AS p ON s.ProductKey = p.ProductKey INNER JOIN 
                         DimProductSubcategory AS psc ON p.ProductSubcategoryKey = psc.ProductSubcategoryKey INNER JOIN 
                         DimProductCategory AS pc ON psc.ProductCategoryKey = pc.ProductCategoryKey INNER JOIN 
                         DimDate AS d ON s.OrderDateKey = d.DateKey 
GROUP BY pc.EnglishProductCategoryName, psc.EnglishProductSubcategoryName, d.CalendarYear, d.MonthNumberOfYear 
ORDER BY Род, Вид, Год, Месяц

Скрипт 1

Второй датасет:

Рис. 2

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

Скрипт 2

Сейчас можно не вчитываться в тексты запросов. Лучше посмотреть на результаты. Основным набором данных является Продажи_по_товарам_и_времени (Рис.1). В матрице отчета будет явно фигурировать измерение Товар (уровни Род и Вид). Измерение Время (уровни Год и Месяц) в матрице будет свернуто. Оно получит отражение в графиках sparkline против каждого вида товара. В предыдущем посте речь шла о том, что при свертке мы не хотим использовать стандартные агрегатные функции Reporting Services. Предположим, для каждого вида товара имеется уже готовый посчитаный агрегат - см.набор данных Продажи_за_последний_непустой_месяц_по_товарам. Остается связать между собой эти наборы по Товару и отобразить готовый агрегат из второго набора в каждой строке первого. Вместо джойна в источнике данных будем использовать функцию Lookup в Report Designer. В содержание колонки ПользАгрегат вместо Рис.6 предыдущего поста напишем выражение:

Рис. 3

=Lookup ( 
 Fields!Род.Value.PadRight(100) + Fields!Вид.Value, 
 Fields!Род.Value.PadRight(100) + Fields!Вид.Value, 
 Fields!ПользАгрегат.Value, 
 "Продажи_за_последний_непустой_месяц_по_товарам" 
 )

Скрипт 3

Первый аргумент функции Lookup принимает значение "ключа" из исходного набора данных. Композитный ключ не поддерживается. Поскольку в данном случае ключевыми полями являются Род и Вид, приходится сделать из них скаляр по типу вычисляемой колонки, сопоставив каждой паре значений Род, Вид уникальное значение. Константа 100 в общей длине строки, которую нужно добить пробелами справа, взята от балды. Правильнее было бы написать что-то вроде

=Fields!Род.Value.PadRight(Max(Len(Fields!Род.Value), "Продажи_по_товарам_и_времени"))

Однако агрегаты в таком контексте не поддерживаются. Возникает ошибка

The Value expression for the textrun 'Деньги.Paragraphs[0].TextRuns[0]' contains an aggregate function in an argument to a Lookup or LookupSet function. Aggregate functions cannot be used for the destination or result Expression parameter of a lookup function. (rsAggregateInLookupDestinationOrResult)

Оформить выражение =Fields!Род.Value.PadRight(Max(Len(Fields!Род.Value), "Продажи_по_товарам_и_времени")) + Fields!Вид.Value в виде Calculated Field первого датасета не вылечит ситуацию, т.к. там такой агрегат тоже не воспримется с аналогичным сообщением об ошибке.

Второй аргумент содержит значение ключа в целевом (lookup-) наборе данных, которому должен соответствовать первый. Третий - это значение какого поля из целевого (lookup-) набора данных возвратит функция в случае нахождения соответствия. Наконец, последний, четвертый - это, собственно, имя целевого (lookup-) набора данных, чуждого по отношению к данному табликсу, значение из которого (третий аргумент) тем не менее требуется в него положить. Запускаем отчет и видим, что все работает, как и в предыдущем посте.

Рис. 4

Функция Multilookup отличается тем, что вместо скалярного значения в кач-ве первого аргумента принимает массив значений, для каждого из которых делает ровно то же, что и рассмотренная только что Lookup. Соответственно, на выходе тоже получается не одно соответствие, а массив значений из поля (аргумент 3) датасета (аргумент 4). Функция LookupSet, как гласит документация, обрабатывает связи 1:n, т.е. ищет не первое соответствие, как Lookup, а все с данным значением ключа. В качестве первого аргумента она, как и Lookup принимает скалярное значение из набора данных (датасета) данного региона данных (табликса), а на выходе, как и в Multilookup, получается массив. Из-за того, что Multilookup и LookupSet возвращают массивы, их ценность для меня несколько непонятна. Я не представляю, как по отношению к родительской записи сделать дочернюю группу, источником данных для которой поставить массив. Единственно, что удалось найти в примерах - конкатенация элементов массива через запятую в строку, чтобы можно было отобразить в одной ячейке. Либо использовать в качестве разделителя при конкатенации перевод строки, чтобы изобразить в одной ячейке псевдомногострочность.

Автор: Алексей Шуленин