Проблема: У вас есть отчет о продажах в SQL Server Reporting Services (SSRS), который принимает параметры для страны и штата. Поступило требование доставить один отчет по электронной почте, который будет включать все комбинации для каждой страны и штата.

Решение: Сначала может показаться невозможным выполнить это без изменения отчета. Однако, используя таблицу подотчетов, мы можем достичь желаемого результата, не изменяя исходный отчет.

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

USE AdventureWorksDW2012
GO

CREATE PROCEDURE usp_DailySales
    @CountryRegionCode NVARCHAR(6),
    @StateProvinceCode NVARCHAR(6)
AS
    SELECT  EnglishCountryRegionName,
            StateProvinceName,
            OrderDate,
            SUM(SalesAmount) AS DaySales
    FROM    dbo.FactInternetSales S
            INNER JOIN DimGeography G ON G.SalesTerritoryKey = S.SalesTerritoryKey
    WHERE   G.CountryRegionCode = @CountryRegionCode
            AND G.StateProvinceCode = @StateProvinceCode
    GROUP BY EnglishCountryRegionName,
            StateProvinceName,
            OrderDate
    ORDER BY EnglishCountryRegionName,
            StateProvinceName,
            OrderDate
GO

Вышеуказанная хранимая процедура принимает два параметра, @CountryRegionCode и @StateProvinceCode. Нам также необходимо создать две дополнительные хранимые процедуры для заполнения списка доступных значений параметров:

USE AdventureWorksDW2012
GO

CREATE PROCEDURE usp_GetCountryRegionCode
AS
    SELECT  CountryRegionCode,
            EnglishCountryRegionName
    FROM    dbo.DimGeography
    GROUP BY CountryRegionCode,
            EnglishCountryRegionName
GO

USE AdventureWorksDW2012
GO

CREATE PROCEDURE usp_GetProvinceCode
    @CountryRegionCode NVARCHAR(6)
AS
    SELECT  StateProvinceCode,
            StateProvinceName
    FROM    dbo.DimGeography
    WHERE   CountryRegionCode = @CountryRegionCode
    GROUP BY StateProvinceCode,
            StateProvinceName
GO

Теперь, в новом проекте отчета, добавьте новый источник данных, указывающий на вашу тестовую базу данных. Добавьте набор данных с именем “ChartData” и настройте его для получения данных из хранимой процедуры “usp_DailySales”. Это добавит два параметра в отчет.

Чтобы получить информацию для входных параметров, настройте еще один набор данных с именем “CountryCodes” для выполнения хранимой процедуры “usp_GetCountryRegionCode”. Используйте возвращаемое значение из этого набора данных в качестве входного параметра для набора данных “ChartData”.

Создайте последний набор данных с именем “StateCodes” для получения значения параметра @StateProvinceCode. Настройте его для выполнения хранимой процедуры “usp_GetProvinceCode”. В представлении параметров сопоставьте параметр хранимой процедуры @CountryRegionCode, чтобы получить его значение из параметра отчета [@CountryRegionCode].

Настройте доступные значения для параметров CountryRegionCode и StateProvinceCode. Для каждого параметра перейдите на страницу “Доступные значения” и выберите радиокнопку “Получить значения из запроса”. Для параметра CountryRegionCode выберите “CountryCodes” в качестве набора данных, “CountryRegionCode” в качестве поля значения и “EnglishCountryRegionName” в качестве его метки. Сделайте то же самое для параметра StateProvinceCode, выбрав “StateCodes” в качестве набора данных, “StateProvinceCode” в качестве поля значения и “EnglishStateProvinceName” в качестве его метки.

Создайте подотчет SSRS, перетащив элемент управления Chart на отчет и настроив его как столбчатую диаграмму. Выберите “DaySales” для значений и “OrderDate” для групп категорий. Для заголовка отчета перетащите текстовое поле в отчет и откройте редактор выражений. Используйте следующий код:

= "Ежедневные продажи на " + First(Fields!StateProvinceName.Value, "ChartData") + ", " + First(Fields!EnglishCountryRegionName.Value, "ChartData")

Так должен выглядеть отчет после выбора значения для параметров.

Теперь давайте создадим мастер-отчет SSRS, который будет использоваться для создания подписки по электронной почте. Начните с добавления общего источника данных в проект и общего набора данных с следующим запросом:

SELECT CountryRegionCode, StateProvinceCode
FROM dbo.DimGeography
GROUP BY CountryRegionCode, StateProvinceCode
ORDER BY CountryRegionCode, StateProvinceCode

Добавьте компонент таблицы в этот мастер-отчет и вставьте компонент подотчета в одну ячейку таблицы. Удалите заголовок таблицы и другие столбцы, оставив только одну ячейку. Измените размер ячейки и привяжите таблицу к общему набору данных.

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

Следуя этим шагам, вы можете создать мастер-отчет SQL Server Reporting Services (SSRS) с подотчетами, который включает все комбинации страны и штата в одном отчете.