В недавнем форуме кто-то спросил, как отфильтровать результат на отчете на основе параметра хранимой процедуры. В этой статье мы рассмотрим, как это сделать с помощью SQL Server.

Хранимая процедура

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

USE AdventureWorks2008
GO

IF(OBJECT_ID('dbo.GetStoreSales')) IS NOT NULL
    DROP PROC dbo.GetStoreSales
GO

CREATE PROC dbo.GetStoreSales 
    @StoreID int 
AS 
    SELECT s.Name StoreName, soh.OrderDate, soh.TotalDue, soh.PurchaseOrderNumber 
    FROM Sales.SalesOrderHeader soh 
    INNER JOIN Sales.Customer c ON soh.CustomerID = c.CustomerID 
    INNER JOIN Sales.Store s ON c.StoreID = s.BusinessEntityID 
    WHERE c.StoreID = @StoreID

Вышеуказанная хранимая процедура извлекает данные о продажах магазина на основе предоставленного параметра StoreID.

Отчет

Теперь, когда хранимые процедуры созданы, давайте создадим отчет с помощью Business Intelligence Development Studio (BIDS). Начните с создания нового проекта отчета и добавления общего источника данных в проект. В этом примере источник данных будет подключаться к базе данных AdventureWorks2008.

Затем добавьте новый отчет в проект. Обозреватель решений должен отображать только что добавленный отчет.

Чтобы создать наборы данных, перейдите на вкладку “Данные отчета” и добавьте новый источник данных, используя созданный ранее общий источник данных. Щелкните правой кнопкой мыши на новом источнике данных и выберите “Добавить набор данных”. В окне “Свойства набора данных” назовите набор данных “Магазины” и выберите хранимую процедуру “GetStores” из выпадающего списка.

Повторите вышеуказанные шаги для создания второго набора данных, но на этот раз выберите хранимую процедуру “GetStoreSales” и назовите набор данных “GetStoreSales”. Поскольку хранимая процедура содержит параметр, параметр автоматически добавляется в отчет. Разверните папку “Параметры”, чтобы увидеть параметр “StoreID”.

Дважды щелкните параметр, чтобы открыть окно “Свойства параметра отчета”. Измените значение приглашения на “Выберите магазин”. В левой навигационной панели выберите “Получить значения из запроса” и выберите “Магазины” из выпадающего списка наборов данных. Выберите “BusinessEntityID” из выпадающего списка поля “Значение” и “Name” из выпадающего списка полей “Метка”. Щелкните “ОК”, чтобы сохранить изменения.

Теперь перейдите на вкладку “Инструменты” и перетащите таблицу на поверхность дизайна отчета. Вернитесь на вкладку “Данные отчета” и перетащите столбцы из набора данных “GetStoreSales” на таблицу, чтобы включить их в отчет. После добавления всех столбцов просмотрите отчет.

Вы заметите выпадающий список в верхней части отчета с надписью “Выберите магазин”. Выберите магазин, по которому вы хотите отфильтровать отчет, и нажмите кнопку “Просмотр отчета”. Таблица теперь будет отображать данные только для выбранного магазина.

Этот параметризованный отчет позволяет пользователям фильтровать данные на основе их выбора, обеспечивая более интерактивный и настраиваемый опыт.

Надеюсь, эта статья поможет вам понять, как создать параметризованный отчет в SQL Server. Если у вас есть вопросы или комментарии, пожалуйста, не стесняйтесь писать мне на pleblanc@pragmaticworks.com.

Спасибо за чтение!

Patrick LeBlanc, MCTS

Основатель www.TSQLScripts.com и www.SQLLunch.com

Посетите www.BIDN.com, чтобы внедрить бизнес-аналитику в вашу компанию.