Поделиться через

Занятие 5. Добавление параметра отчета, передаваемого определяемой пользователем функции

  • Статья

Изменения: 12 декабря 2006 г.

Параметры отчета могут быть привязаны к входным параметрам, переданным определяемой пользователем функции или хранимой процедуре в запросе набора данных. Определяемые пользователем функции могут возвращать табличное или скалярное значение (с одним результатом). Запрос набора данных, по определению, получает от определяемой пользователем функции или хранимой процедуры один результат. Одним из преимуществ определяемой пользователем функции над хранимой процедурой является то, что эту функцию можно включать непосредственно в инструкцию Transact-SQL SELECT, сравнивая, таким образом, результат функции с другими столбцами в результирующим наборе запроса.

Определяемые пользователем функции и хранимые процедуры не принимают напрямую многозначные параметры, которые являются массивами. Чтобы передать хранимой процедуре многозначный массив, используется функция JOIN языка Visual Basic (с помощью которой создается сцепленная строка всех значений) и выполняется запись определяемой пользователем функции для принятия строкового параметра с несколькими выбранными элементами с разделителями по выбору.

Определяемая пользователем функция ufnGetContactInformation принимает идентификатор базы данных для таблицы Person.Contact и получает значения для ContactID, Name, Phone, EmailAddress, JobTitle и ContactType. В этом отчете будет создан параметр, который позволит пользователю выбирать для отображения стандартное подмножество полей.

На этом занятии будет создан новый отчет под названием Contact Information с параметром, который принимает идентификатор базы данных для лица, представленного в списке контактов магазина посредника. Создается запрос, вызывающий определяемую пользователем функцию ufnGetContactInformation из базы данных AdventureWorks, а параметр отчета связывается с входным параметром @ContactID определяемой пользователем функции.

Этот отчет будет подготовлен в качестве вложенного в отчете Resellers Worldwide. При этом будут созданы параметры цвета фона и текста, удалены пробелы вокруг отчета, заданы поля, а также будет задана ширина отчета. Кроме того, для этого отчета будет создан многозначный параметр, который позволит указывать в главном отчете поля, в которых должны отображаться контактные данные.

Открытие проекта сервера отчетов «Advanced Parameters Tutorial»

  1. В среде SQL Server Business Intelligence Development Studio для SQL Server откройте проект сервера отчетов Advanced Parameters Tutorial, если он еще не открыт.

Создание нового отчета из определяемой пользователем функции

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

  2. В области Шаблоны выберите Отчет.

  3. В текстовом поле Имя введите Contact Information.rdl.

  4. Нажмите кнопку Добавить. Отчет откроется в представлении данных.

  5. В раскрывающемся списке Набор данных выберите <Новый набор данных>. Откроется диалоговое окно Набор данных.

  6. На вкладке Запрос в текстовом поле Имя введите ContactInformation.

  7. Убедитесь, что параметр Источник данных имеет значение Resellers.

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

  9. В области «Строка запроса» вставьте следующий запрос:

    SELECT udf.ContactID, udf.FirstName + N' ' + udf.LastName AS Name,
   c.Phone, c.EmailAddress, udf.JobTitle, udf.ContactType
   FROM ufnGetContactInformation(@ContactID) udf
   JOIN Person.Contact c ON udf.ContactID = c.ContactID

    В этом запросе используется параметр под названием @ContactID для передачи идентификатора базы данных определяемой пользователем функции с именем ufnGetContactInformation, которая принимает ContactID в качестве входного параметра.

  10. Нажмите кнопку Запустить (!) на панели инструментов. Откроется диалоговое окно Определение параметров запроса.

  11. В столбце Значение параметра введите 1 в качестве значения параметра @ContactID. В результирующем наборе отобразятся контактные данные для контактного лица магазина, определяемого по значению ContactID. Возвращенные в результирующем наборе столбцы задаются в пользовательской функции.

  12. (Необязательно) Убедитесь, что параметр запроса @ContactID привязан к параметру отчета ContactID. На панели инструментов отчета выберите Изменить выбранный набор данных(…), затем перейдите на вкладку Параметры. Параметр @ContactID имеет значение =Parameters!ContactID.Value.

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

Добавление значения по умолчанию для параметра ContactID

  1. В меню Отчет выберите пункт Параметры отчета. Откроется диалоговое окно Параметры отчета с выбранным параметром ContactID в области «Параметры».

  2. В разделе Свойства раздела Значения по умолчанию выберите Без запроса. Введите 1.

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

В этой процедуре создается многозначный строковый параметр, с помощью которого можно будет выбирать контактные данные для отображения. Создается список значений для Phone, Email или None, доступных без запроса.

Создание параметра отчета FieldsToDisplay

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

  2. Нажмите кнопку Добавить. Будет создан новый параметр со значениями по умолчанию.

  3. В разделе Свойства в текстовом поле Имя введите FieldsToDisplay. Убедитесь, что задан тип данных String.

  4. В поле Запрос введите Выберите контактные данные для отображения:.

  5. Выберите параметр Многозначный.

  6. Убедитесь, что выбран параметр Разрешить пустое значение.

  7. В разделе Значения по умолчанию выберите Без запроса. Введите значения с помощью следующей таблицы:

Метка Значение

Phone

Phone

Email

EmailAddress

None

<Пусто>

Если параметр имеет доступные значения, то для выбора параметра Разрешить пустое значение необходимо ввести <Пустое> значение.

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

Создание макета отчета для этих данных

  1. Перейдите на вкладку Макет. Отчет откроется в режиме разметки.

  2. Перетащите элемент табличного отчета из инструментария в область конструктора.

  3. Щелкните правой кнопкой мыши заголовок столбца и выберите Удалить столбцы. Останется два столбца.

  4. В строку детализации первого текстового поля вставьте следующее выражение:

    =Fields!Name.Value & vbCrLf & "[ " & Fields!JobTitle.Value & " ]"

  5. В строку детализации второго текстового поля вставьте следующее выражение:

    =IIF((Parameters!FieldsToDisplay.Count=1) AND 
   (InStr("None",Parameters!FieldsToDisplay.Label(
          Parameters!FieldsToDisplay.Count-1))>0),"",
    IIF(InStr(Join(Parameters!FieldsToDisplay.Value,","),
       "EmailAddress")>0,Fields!EmailAddress.Value,"") + 
vbCrLf + IIF(InStr(Join(Parameters!FieldsToDisplay.Value,","),
       "Phone")>0,Fields!Phone.Value,""))

    Это выражение просто ищет значения, выбранные для многозначного параметра «FieldsToDisplay». Если выбран параметр NONE (только один параметр и метка «None»), результатом выражения является пустое значение. Если введено значение «EmailAddress», то часть выражения определяется как значение «EmailAddress». Если введено значение «Phone», то часть выражения определяется как значение «Phone». Эти два значения сцепляются с помощью символа возврата каретки. Полностью вычисленное выражение отображается во втором поле.

    В этом примере показано следующее.

    • Поиск количества значений, выбранных в многозначном параметре: Parameters!FieldsToDisplay.Count.
    • Для многозначного параметра — проверка последней метки в массиве для определенной строки (в данном случае «None»): InStr("None",Parameters!FieldsToDisplay.Label(Parameters!FieldsToDisplay.Count-1))>0.
    • Условный возврат строки в зависимости от того, найдено ли значение в многозначном параметре (поиск ведется в сцепленной строке всех выбранных значений для многозначного параметра FieldsToDisplay. Если в наборе данных удается найти значение EmailAddress, возвращается определенное значение EmailAddress, если значение не найдено, возвращается пустое значение): IIF(InStr(Join(Parameters!FieldsToDisplay.Value,","),"EmailAddress")>0,Fields!EmailAddress.Value,"").
    Aa337435.note(ru-ru,SQL.90).gifПримечание.
    Функция IIF языка Visual Basic вычисляет все переданные ей значения параметра функции, поэтому данное выражение нельзя использовать с полями набора данных, которые могут иметь значение NULL.

  6. (Необязательно) Нажмите кнопку Предварительный просмотр. Измените значения двух параметров и просмотрите результаты.

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

Удаление пробелов и настройка размеров отчета

  1. Для отображения маркеров щелкните внутри таблицы.

  2. Щелкните правой кнопкой мыши маркер строки детализации. В контекстном меню выберите Верхний колонтитул таблицы и Нижний колонтитул таблицы и установите для них значение OFF. В таблице будет отображаться только строка детализации.

    В следующих трех шагах необходимо настроить ширину отчета в соответствии с определенным размером. Этот размер понадобится на следующем занятии для добавления элемента вложенного отчета в главный отчет.

  3. Выберите таблицу. Убедитесь, что в окне «Свойства» задана ширина таблицы, равная 3. Установите минимальную ширину таблицы, достаточную для просмотра контактных данных.

    Установленная для вложенного отчета ширина таблицы определяет внешний вид вложенного отчет в главном отчете. Чтобы установить фиксированный размер для вложенного отчета, следует установить нужные значения высоты и ширины. Кроме того, можно установить свойства текстового поля CanGrow и CanShrink, чтобы разрешить растягивание текстового поля по вертикали по размерам текста. Свойства CanGrow и CanShrink неприменимы к ширине. В настоящее время автоматическое растягивание текстового поля по ширине текста недоступно.

  4. Выберите первое текстовое поле заголовка столбца. В окне «Свойства» перейдите к свойству Ширина и введите значение 1,5 или (с помощью измерения по умолчанию) любое значение, необходимое для установки ширины, равной половине таблицы.

  5. Выберите второе текстовое поле заголовка столбца. В окне «Свойства» перейдите к свойству Ширина и введите значение 1,5 или (с помощью измерения по умолчанию) любое значение, необходимое для установки ширины, равной половине таблицы.

  6. Выберите таблицу. С помощью клавиш со стрелками переместите таблицу в верхнюю часть страницы отчета и выровняйте границы таблицы по краям отчета.

  7. С помощью мыши удерживайте указатель над краем области отчета (пунктирный белый фон). Когда указатель мыши изменится на двойную стрелку, захватите край области отчета и перетащите его к границе таблицы. В результате при просмотре отчета пробелы будут удалены.

  8. Аналогичным образом удалите пробелы в нижней части страницы отчета путем перемещения области отчета к нижней границе таблицы.

Добавление параметров для установки цветов

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

  2. Нажмите кнопку Добавить. Будет создан новый параметр со значениями по умолчанию.

  3. В разделе Свойства в текстовом поле Имя введите BackgroundColor. Убедитесь, что задан тип данных String.

  4. Выберите параметр Скрытый. Текстовое поле Запрос отключено.

  5. Очистите флажок для параметра Разрешить пустые значения.

  6. Убедитесь, что в разделе Доступные значения выбрано значение Без запроса. Оставьте таблицу значений пустой.

  7. В разделе Значения по умолчанию выберите Без запроса и введите Azure.

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

  8. Создайте новый параметр FontColor. Для этого выполните шаги 1 — 6.

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

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

  11. В режиме разметки щелкните правой кнопкой мыши строку детализации таблицы. В окне «Свойства» перейдите к свойству BackgroundColor. В качестве значения задайте следующее выражение:

    =Parameters!BackgroundColor.Value

  12. В окне «Свойства» перейдите к свойству Color. В качестве значения задайте следующее выражение:

    =Parameters!FontColor.Value

  13. Нажмите кнопку Предварительный просмотр. Убедитесь, что с помощью значений параметров BackgroundColor и FontColor задан фон строк таблицы и цвет текста.

Добавление описания отчета

  1. Перейдите на вкладку Макет.

  2. В меню Отчет выберите пункт Свойства отчета. Откроется диалоговое окно Свойства отчета.

  3. В текстовом поле Описание введите следующий текст: «Вложенный отчет для отображения контактных данных».

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

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

Отчет для отображения контактных данных для магазина посредника успешно создан. Отчет был отформатирован как вложенный. Для этого была задана ширина, удалены лишние пробелы и созданы параметры для управления внешним видом и содержимым отчета. На следующем занятии этот отчет добавляется в качестве вложенного в отчет Resellers Worldwide. См. раздел Занятие 6. Добавление вложенного отчета с параметрами.

Журнал изменений

Версия Журнал

12 декабря 2006 г.
Измененное содержимое
  • Исправлен пример кода для udf.ContactID = c.ContactID.

См. также

Другие ресурсы

Работа с параметрами в службах Reporting Services
Основы определяемых пользователем функций

Справка и поддержка

Получение помощи по SQL Server 2005