Занятие 1. Добавление параметров для фильтрации отчетов по дате (службы SSRS)

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

В ходе этого занятия к отчету будут добавлены параметры @StartDate и @EndDate, чтобы ограничить данные, получаемые из источника данных. Параметры отчета StartDate и EndDate будут созданы автоматически и добавлены в панель «Данные отчета». Параметры учитывают регистр символов. Параметры запроса начинаются с символа @, в отличие от параметров отчета.

В качестве типа данных параметров будет установлен DateTime. При этом на панели инструментов средства просмотра отчетов появится элемент управления «календарь» вместе с текстовым полем параметра. Будут заданы значения для параметров по умолчанию, чтобы отчет мог выполняться автоматически. И наконец, будет создан параметр отчета DayofWeek, не привязанный к параметру запроса. Он будет использован для фильтрации данных после их получения из источника данных.

Прежде чем приступать к работе с данным учебником, необходимо сначала пройти предыдущий учебник, Учебник Создание простого табличного отчета.

Открытие существующего проекта сервера отчетов

1. Нажмите кнопку Пуск, выберите пункт Все программы, затем Microsoft SQL Server 2008 R2, а затем выберите пункт Среда Business Intelligence Development Studio.

2. В меню Файл выберите пункт Открыть, а затем Проект или решение.

3. Нажмите Tutorial и выберите Tutorial.sln. Этот проект был создан в разделе Учебник Создание простого табличного отчета.

4. Чтобы открыть проект, нажмите кнопку ОК. В обозревателе решений будет отображен проект Tutorial с одним отчетом, имя которого Sales Orders.rdl.

   Примечание Если окно обозревателя решений не видно, в меню Вид выберите команду Обозреватель решений.

Преобразование внедренного источника данных в общий источник данных

1. В панели «Данные отчета» щелкните правой кнопкой мыши источник данных AdventureWorks и выберите команду Преобразовать в общий источник данных. В Обозреватель решений будет добавлен источник данных с именем AdventureWorks.rds.

2. В панели «Данные отчета» щелкните правой кнопкой мыши источник данных AdventureWorks и выберите Свойства набора данных.

3. В поле Имя введите AdventureWorks_Ref.

4. Нажмите кнопку ОК.

Замена существующего набора данных

1. В панели «Данные отчета» щелкните правой кнопкой мыши набор данных AdventureWorksDataset и выберите пункт Свойства набора данных.

   Примечание
   Если панель «Данные отчета» не видна, в меню Вид выберите пункт Данные отчета.

2. Убедитесь, что в списке Источник данных выбран набор данных AdventureWorks_Ref.

3. Убедитесь, что в списке Тип запроса выбран тип Текст.

4. Нажмите кнопку Конструктор запросов, чтобы открыть конструктор запросов.

5. Текст в текстовом поле необходимо заменить на следующий текст запроса:

   SELECT 
      soh.OrderDate AS [Date], 
      soh.SalesOrderNumber AS [Order], 
      pps.Name AS Subcat, pp.Name as Product,  
      SUM(sd.OrderQty) AS Qty,
      SUM(sd.LineTotal) AS LineTotal
   FROM Sales.SalesPerson sp 
      INNER JOIN Sales.SalesOrderHeader AS soh 
         ON sp.BusinessEntityID = soh.SalesPersonID
      INNER JOIN Sales.SalesOrderDetail AS sd 
         ON sd.SalesOrderID = soh.SalesOrderID
      INNER JOIN Production.Product AS pp 
         ON sd.ProductID = pp.ProductID
      INNER JOIN Production.ProductSubcategory AS pps 
         ON pp.ProductSubcategoryID = pps.ProductSubcategoryID
      INNER JOIN Production.ProductCategory AS ppc 
         ON ppc.ProductCategoryID = pps.ProductCategoryID
   GROUP BY ppc.Name, soh.OrderDate, soh.SalesOrderNumber, 
      pps.Name, pp.Name,    soh.SalesPersonID
   HAVING (ppc.Name = 'Clothing' 
      AND (soh.OrderDate BETWEEN (@StartDate) AND (@EndDate)))
   

   Этот запрос идентичен предыдущему, но к нему было добавлено условие с двумя ограничивающими параметрами:

   AND (soh.OrderDate BETWEEN (@StartDate) AND (@EndDate))

6. На панели инструментов нажмите кнопку Выполнить (!). Откроется диалоговое окно Определение параметров запроса для ввода значений параметров.

7. Введите два значения, чтобы увидеть отфильтрованный результирующий набор.

   1. В столбце Значение параметра введите значение для параметра @StartDate,, например 1/31/2001.

   2. В столбце Значение параметра введите значение для параметра @EndDate, например 1/31/2003.

8. Нажмите кнопку ОК. 

9. Результирующий набор будет содержать отфильтрованный набор данных с заказами, относящимися к 2001 и 2002 годам.

10. Дважды нажмите кнопку ОК. В панели «Данные отчета» отображаются поля набора данных. Следует также заметить, что параметры отчета, StartDate и EndDate, будут созданы автоматически и добавлены в узел «Параметры».

После задания параметров запроса для отчета, нужно изменить тип данных параметров отчета, чтобы они соответствовали типу данных в источнике данных. По умолчанию используется тип Text, который соответствует типу String большинства источников данных. Если данные источника — числовые, логические или типа даты и времени, нужно изменить тип данных параметра отчета.

Изменение типа данных и значений по умолчанию для параметра отчета

1. В панели «Данные отчета» разверните узел «Параметры» и дважды щелкните StartDate. Откроется диалоговое окно Свойства параметра отчета.

2. Убедитесь, что имя параметра — StartDate , а запрос на его введение — Start Date.

3. В списке Тип данных выберите Дата-время.

4. Нажмите кнопку ОК.

5. В панели «Данные отчета» дважды щелкните EndDate. Проверьте значения имени и запроса на введение.

6. В списке Тип данных выберите Дата-время.

7. Нажмите кнопку ОК. 

8. Нажмите кнопку Просмотр. Оба параметра, StartDate и EndDate, появятся на панели инструментов вместе с элементом управления «календарь». Элементы управления «календарь» появляются автоматически, если параметр имеет тип данных Date/Time, а список допустимых значений не определен. Если список допустимых значений задан, вместо календаря появится раскрывающийся список значений.

9. Введите значения параметров для запуска отчета.

   1. В текстовом поле параметра StartDate введите дату 1/31/2001.

   2. В текстовом поле параметра EndDate введите дату 1/31/2003.

10. Нажмите кнопку Просмотреть отчет. Отчет содержит только те записи, даты которых попадают в рамки значений параметров отчета.

После создания параметров отчета можно создать для этих параметров значения по умолчанию. Параметры по умолчанию позволяют выполнять отчет автоматически. В противном случае для выполнения отчета пользователю необходимо ввести значения параметров.

Задание значений параметров по умолчанию

1. В представлении конструктора, в панели «Данные отчета» разверните узел «Параметры» и дважды щелкните StartDate. Откроется диалоговое окно Свойства параметра отчета.

2. Нажмите кнопку Значения по умолчанию.

3. Выберите параметр Задание значений. Появится кнопка Добавить и пустая сетка Значение.

4. Нажмите кнопку Добавить. В сетку параметров добавится пустая строка.

5. Щелкните текстовое поле Значение и удалите находящийся там текст (Null).

6. Введите 1/31/2001. Нажмите кнопку ОК. 

7. На панели конструктора отчета и дважды щелкните EndDate.

8. Нажмите кнопку Значения по умолчанию.

9. Выберите параметр Задание значений.

10. Нажмите кнопку Добавить.

11. Введите 1/31/2003. Нажмите кнопку ОК.

12. Нажмите кнопку Просмотр. Отчет будет выполнен незамедлительно, поскольку для всех параметров были заданы значения по умолчанию.

Добавление в запрос нового поля для фильтрации

1. Переключитесь в режим конструирования.

2. Щелкните правой кнопкой мыши набор данных AdventureWorksDataset и выберите пункт Свойства набора данных. В конструкторе запросов замените существующий запрос следующим:

   SELECT 
      soh.OrderDate AS [Date], DATENAME(weekday, soh.OrderDate) as Weekday,
      soh.SalesOrderNumber AS [Order], 
      pps.Name AS Subcat, pp.Name as Product,  
      SUM(sd.OrderQty) AS Qty,
      SUM(sd.LineTotal) AS LineTotal
   FROM Sales.SalesPerson sp 
      INNER JOIN Sales.SalesOrderHeader AS soh 
         ON sp.BusinessEntityID = soh.SalesPersonID
      INNER JOIN Sales.SalesOrderDetail AS sd 
         ON sd.SalesOrderID = soh.SalesOrderID
      INNER JOIN Production.Product AS pp 
         ON sd.ProductID = pp.ProductID
      INNER JOIN Production.ProductSubcategory AS pps 
         ON pp.ProductSubcategoryID = pps.ProductSubcategoryID
      INNER JOIN Production.ProductCategory AS ppc 
         ON ppc.ProductCategoryID = pps.ProductCategoryID
   GROUP BY ppc.Name, soh.OrderDate, soh.SalesOrderNumber, 
      pps.Name, pp.Name,    soh.SalesPersonID
   HAVING (ppc.Name = 'Clothing' AND (soh.OrderDate BETWEEN (@StartDate) AND (@EndDate)))
   

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

   DATENAME(weekday, soh.OrderDate) as Weekday.

3. Нажмите кнопку «Выполнить» (!). Откроется диалоговое окно Определение параметров запроса.

4. В столбце Значение параметра введите значение для параметра @StartDate,, например 1/31/2001.

5. В столбце Значение параметра введите значение для параметра @EndDate, например 31.01.2003.

6. Нажмите кнопку ОК. В результирующем наборе должен появиться новый столбец с именем Weekday.

7. Дважды нажмите кнопку ОК. В области данных отчета убедитесь, что Weekday является полем.

(Необязательно) Форматирование даты в табличных данных, которые будут отфильтрованы

1. Перейдите на вкладку Конструктор.

2. Щелкните правой кнопкой ячейку с выражением для поля [Date] и выберите параметр Свойства текстового поля.

3. Щелкните Число, а затем в поле Категория выберите Дата.

4. В поле Тип введите «31 января 2000 года, понедельник».

5. Нажмите кнопку ОК.

Добавление нового параметра отчета

1. В представлении конструктора, в панели «Данные отчета» нажмите кнопку Создать и выберите Параметр. Откроется диалоговое окно Свойства параметра отчета.

2. В поле «Имя» введите DayoftheWeek.

3. В текстовом поле Запрос введите День недели для фильтрации:

4. Убедитесь, что поле типа данных содержит значение Text.

5. Нажмите кнопку Значения по умолчанию.

6. Выберите параметр Задание значений. Появится кнопка Добавить и пустая сетка Значение.

7. Нажмите кнопку Добавить.

8. Введите пятница.

9. Нажмите кнопку ОК.  

Задание фильтра таблицы с выражением параметра

1. В представлении конструктора щелкните правой кнопкой мыши маркер строки или столбца таблицы и выберите Свойства табликса.

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

2. Перейдите на вкладку Фильтры. Появится пустая сетка фильтра.

3. Нажмите кнопку «Добавить». В сетку параметров добавится пустая строка.

4. Из раскрывающегося списка в поле Выражение выберите [Weekday].

5. Убедитесь, что в качестве параметра Оператор установлен знак равенства (=).

6. Нажмите кнопку выражения (fx) рядом с текстовым полем Значение. Откроется диалоговое окно Выражение.

7. В поле «Категория» нажмите Параметры. В панели «Значения» появится текущий список параметров. Дважды щелкните DayoftheWeek. Выражение параметра успешно добавлено в текстовое поле выражения. Теперь в текстовом поле «Выражение» появится следующее выражение: =Parameters!DayoftheWeek.Value.

8. Нажмите кнопку ОК. Снова нажмите кнопку OK для выхода из диалогового окна Свойства табликса.

   Теперь фильтр для таблицы настроен на сравнение значения в поле Weekday со значением параметра DayoftheWeek. Например, при вводе на панели инструментов отчета значения Пятница для параметра DayoftheWeek обработчик отчетов обрабатывает только те строки таблицы, в поле Weekday которых содержится значение Пятница.

   Нажмите кнопку Просмотр. Отчет запускается автоматически, поскольку все параметры имеют значения по умолчанию. Таблица отображает только значения, попавшие во временной интервал с StartDate по EndDate и выпавшие на пятницу.

Следующие шаги

Были определены параметры запроса и отчета, установлены значения параметров по умолчанию и задан фильтр для таблицы. Следующее занятие посвящено созданию списка доступных, или допустимых, значений параметра. См. раздел Занятие 2. Добавление параметров для создания списка доступных значений (службы SSRS).

См. также

Основные понятия

Использование данных даты и времени

Параметры отчета (построитель отчетов версии 3.0 и службы SSRS)