Связывание двух датасетов в Reporting Services 2008 R2

Среди пожеланий трудящихся к функциональности Reporting Services было иметь возможность джойнить наборы данных не внутри источника, откуда они приходят, а в Report Builder / Report Designer непосредственно в ходе подготовки отчета и вне зависимости от источников. Другим, не менее страстным пожеланием была возможность иметь в одном табликсе данные из более, чем одного датасета, чтобы не возникала ошибка Only fields from the current dataset can be added. Строго говоря, ни та, ни другая возможности не были реализованы. Тем не менее в регион данных теперь можно заносить связанную информацию из другого датасета. Это достигается при помощи трех новых функций - Lookup, Multilookup и LookupSet. Первая определена в документации так: функция Lookup позволяет извлечь значение из указанного набора данных, состоящего из пар «имя-значение» с отношением один к одному. берет значение колонки из первого. На самом деле, отношение может быть 1:n или n:1, без разницы. Далее говорится: при первом совпадении исходного и целевого выражений вычисляет результирующее выражение для этой строки в наборе данных. Т.е. она берет значение из заданной колонки первого датасета и ищет его внутри заданной колонки второго. Как только такое встретилось, поиск прекращается. Функция возвращает значение из колонки второго датасета, заданной в ее третьем параметре. Рассмотрим на примере.

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

Первый датасет:

Рис.1

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возвращают массивы, их ценность для меня несколько непонятна. Я не представляю, как по отношению к родительской записи сделать дочернюю группу, источником данных для которой поставить массив. Единственно, что удалось найти в примерах - конкатенация элементов массива через запятую в строку, чтобы можно было отобразить в одной ячейке. Либо использовать в качестве разделителя при конкатенации перевод строки, чтобы изобразить в одной ячейке псевдомногострочность.

 

Алексей Шуленин