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

Занятие 2. Добавление каскадных параметров в отчет

  • Статья

Добавления: 17 июля 2006 г.

Каскадные параметры обеспечивают возможность фильтрации доступных значений для каждого параметра в наборе параметров, имеющих естественные связи. Например, в базе данных AdventureWorks у каждого посредника есть адрес в определенном месте города (поле City). Города сгруппированы в штаты и провинции (поле StateProvince). Штаты и провинции сгруппированы в страны и регионы (поле CountryRegion). Страны и регионы сгруппированы в территории продаж (поле TerritoryGroup). Для этих полей, расположенных в зависимом порядке, начиная с наиболее общей категории TerritoryGroup и заканчивая самой точной City, можно создать четыре каскадных параметра.

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

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

На этом занятии в созданный при работе с предыдущим учебником отчет Sales Orders потребуется добавить четыре каскадных параметра (TerritoryGroup, CountryRegion, StateProvince и City). Необходимо будет создать набор данных для каждого параметра, чтобы заполнить список доступных для него значений. При создании запроса с параметром запроса для набора данных допустимых значений параметр отчета создается автоматически. Ввиду того, что наборы данных для списков допустимых значений создаются начиная с наиболее общего (TerritoryGroup) и заканчивая наиболее точным параметром (Cities), параметры отчета должны создаваться в правильном порядке зависимостей. В отношении последнего набора данных допустимых значений (Cities) будет рассмотрено создание параметра отчета вручную и его связывание с соответствующим параметром запроса.

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

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

Процедура

Открытие отчета ResellersWorldwide

  1. В среде SQL Server Business Intelligence Development Studio откройте созданный на предыдущем занятии проект сервера отчетов AdvancedParametersTutorial.

  2. В обозревателе решений дважды щелкните отчет Resellers Worldwide. Отчет откроется в режиме разметки.

  3. Перейдите на вкладку Данные.

Добавление набора данных для доступных значений параметра TerritoryGroup

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

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

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

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

  5. В область запроса вставьте следующий запрос:

    SELECT distinct [Group] as SalesTerritory
   FROM [AdventureWorks].[Sales].[SalesTerritory]

  6. Нажмите кнопку Выполнить (!) для просмотра результирующего набора. Появится столбец SalesTerritory, в котором будет отображено три строки: Europe, North America и Pacific.

Добавление набора данных для доступных значений параметра CountryRegion

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

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

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

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

  5. В область запроса вставьте следующий запрос.

    SELECT Distinct CR.Name AS CountryRegion
FROM Sales.Store AS S
    JOIN Sales.CustomerAddress AS CA ON CA.CustomerID = S.CustomerID
    JOIN Person.Address AS A ON A.AddressID = CA.AddressID
    JOIN Person.StateProvince SP ON 
        SP.StateProvinceID = A.StateProvinceID
    JOIN Person.CountryRegion CR ON 
        CR.CountryRegionCode = SP.CountryRegionCode
    JOIN Sales.Customer C on S.CustomerID = C.CustomerID
    JOIN Sales.SalesTerritory T on C.TerritoryID = T.TerritoryID
WHERE (T.[Group] = (@TerritoryGroup))
Order by CR.Name

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

  7. Введите Pacific.

    Появится результирующий набор со столбцом CountryRegion и одной строкой со значением «Australia».

    При определении параметра запроса с названием @TerritoryGroup будет создан новый параметр отчета под названием TerritoryGroup.

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

Добавление набора данных для доступных значений параметра StateProvince

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

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

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

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

  5. В область запроса вставьте следующий запрос.

    SELECT Distinct SP.Name AS StateProvince
FROM Sales.Store AS S
    JOIN Sales.CustomerAddress AS CA ON CA.CustomerID = S.CustomerID
    JOIN Person.Address AS A ON A.AddressID = CA.AddressID
    JOIN Person.StateProvince SP ON 
        SP.StateProvinceID = A.StateProvinceID
    JOIN Person.CountryRegion CR ON 
        CR.CountryRegionCode = SP.CountryRegionCode
WHERE (CR.Name = @CountryRegion)
Order by SP.Name

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

  7. В текстовом поле Значение параметра введите Australia. Нажмите кнопку ОК.

    Появится столбец StateProvince, в котором будет отображено четыре строки: New South Wales, Queensland, South Australia и Victoria.

Добавление набора данных для доступных значений параметра City

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

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

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

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

  5. В области запроса вставьте следующий запрос.

    SELECT Distinct A.City 
FROM Sales.Store AS S
    JOIN Sales.CustomerAddress AS CA ON CA.CustomerID = S.CustomerID
    JOIN Person.Address AS A ON A.AddressID = CA.AddressID
    JOIN Person.StateProvince SP ON 
        SP.StateProvinceID = A.StateProvinceID
    JOIN Person.CountryRegion CR ON 
        CR.CountryRegionCode = SP.CountryRegionCode
    JOIN Sales.Customer C on S.CustomerID = C.CustomerID
    JOIN Sales.SalesTerritory T on C.TerritoryID = T.TerritoryID
WHERE (
    T.[Group] = (@TerritoryGroup) AND
    CR.[Name] = (@CountryRegion) AND
    SP.[Name] = (@StateProvince)
    )
Order by A.City

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

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

Имя параметра Значение параметра

@TerritoryGroup

Pacific

@CountryRegion

Australia

@StateProvince

Victoria
  1. Нажмите кнопку ОК.
    Появится столбец City, в котором будет отображено три строки: Melbourne, Seaford и South Melbourne.

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

Установка доступных значений для параметра отчета TerritoryGroup

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

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

  3. В текстовом поле Запрос введите Выберите группу территорий:.

  4. Убедитесь, что все флажки сняты.

  5. В разделе Доступные значения выберите Из запроса.

  6. Из раскрывающегося списка Набор данных выберите параметр ValidValuesforTerritoryGroup.

  7. Из раскрывающегося списка Поле значения выберите SalesTerritory.

  8. Из раскрывающегося списка Поле метки выберите SalesTerritory.

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

  10. В текстовом поле введите North America.

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

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

  12. (Необязательно) Перейдите на вкладку Предварительный просмотр. Появится параметр TerritoryGroup со значением по умолчанию «Europe» и допустимыми значениями из поля SalesTerritory набора данных ValidValuesforTerritoryGroup.

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

Установка доступных значений для параметра отчета CountryRegion

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

  2. В области Параметры выберите параметр CountryRegion.

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

  4. В текстовом поле Запрос введите Выберите Country/Region:.

  5. Убедитесь, что все флажки сняты.

  6. В разделе Доступные значения выберите Из запроса.

  7. Из раскрывающегося списка Набор данных выберите параметр ValidValuesforCountryRegion.

  8. Из раскрывающегося списка Поле значения выберите CountryRegion.

  9. Из раскрывающегося списка Поле метки выберите CountryRegion.

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

  11. Из раскрывающегося списка Набор данных выберите параметр ValidValuesforCountryRegion.

  12. Из раскрывающегося списка Поле значения выберите CountryRegion.

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

  14. (Необязательно) Перейдите на вкладку Предварительный просмотр. Выберите значение для параметра TerritoryGroup. Укажите значение для параметра CountryRegion. Убедитесь, что значения, отображаемые для параметра CountryRegion, допустимы для выбранной группы территорий.

Установка доступных значений для параметра отчета StateProvince

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

  2. В области Параметры выберите параметр StateProvince.

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

  4. В текстовом поле Запрос введите Выберите State/Province:.

  5. Убедитесь, что все флажки сняты.

  6. В разделе Доступные значения выберите Из запроса.

  7. Из раскрывающегося списка Набор данных выберите параметр ValidValuesforStateProvince.

  8. Из раскрывающегося списка Поле значения выберите StateProvince.

  9. Из раскрывающегося списка Поле метки выберите StateProvince.

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

  11. Из раскрывающегося списка Набор данных выберите параметр ValidValuesforStateProvince.

  12. Из раскрывающегося списка Поле значения выберите StateProvince.

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

  14. (Необязательно) Перейдите на вкладку Предварительный просмотр. Выберите значение для параметра TerritoryGroup. Укажите значение для параметра CountryRegion. Укажите значение для параметра StateProvince. Убедитесь, что значения, отображаемые для параметра StateProvince, допустимы для выбранных параметров TerritoryGroup и CountryRegion.

Создание параметра отчета City и установка его свойств

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

  2. Обратите внимание, что в области Параметры отсутствует параметр отчета с названием City.

    Параметры отчета создаются автоматически при определении для набора данных запроса, содержащего их. Никакой из созданных ранее запросов наборов данных не содержит параметра запроса @City. Можно создать параметр отчета под названием City, и когда будет определен отчет, содержащий параметр запроса @City, параметр запроса будет автоматически привязан к параметру отчета, если у него будет соответствующее имя (имя без указателя параметра запроса «@»).

  3. Нажмите кнопку Добавить. Создание параметра отчета со свойствами по умолчанию завершено.

  4. В разделе Свойства в текстовом поле Имя введите City.

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

  6. В текстовом поле Запрос введите Выберите City:.

  7. Убедитесь, что все флажки сняты.

  8. В разделе Доступные значения выберите Из запроса.

  9. Из раскрывающегося списка Набор данных выберите параметр ValidValuesforCity.

  10. Из раскрывающегося списка Поле значения выберите City.

  11. Из раскрывающегося списка Поле метки выберите City.

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

  13. Из раскрывающегося списка Набор данных выберите параметр ValidValuesforCity.

  14. Из раскрывающегося списка Поле значения выберите City.

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

  16. (Необязательно) Перейдите на вкладку Предварительный просмотр. Выберите значения для параметров TerritoryGroup, CountryRegion и StateProvince. Убедитесь, что значения, отображаемые для параметра City, допустимы для выбранных параметров.

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

Изменение запроса набора данных по заказам посредников на продажу для включения существующих параметров запроса

  1. В представлении «Данные» в раскрывающемся списке Набор данных выберите Resellers. В области запроса появится исходная строка запроса, созданная на предыдущем занятии.

  2. Замените текст в области запроса следующим запросом:

    SELECT S.CustomerID, SO.SalesOrderNumber, SO.OrderDate, SO.TotalDue,
    S.Name AS Store, A.City, SP.Name AS State, CR.Name
    AS CountryRegion, 
    SC.ContactID As StoreContactID, T.[Group] As TerritoryGroup
FROM Sales.Store AS S
    JOIN Sales.CustomerAddress AS CA ON CA.CustomerID = S.CustomerID
    JOIN Person.Address AS A ON A.AddressID = CA.AddressID
    JOIN Person.StateProvince SP ON 
        SP.StateProvinceID = A.StateProvinceID
    JOIN Person.CountryRegion CR ON 
        CR.CountryRegionCode = SP.CountryRegionCode
    JOIN Sales.SalesOrderHeader AS SO ON 
    S.CustomerID = SO.CustomerID
    JOIN Sales.StoreContact SC ON  S.CustomerID = SC.CustomerID
    JOIN Sales.Customer C on S.CustomerID = C.CustomerID
    JOIN Sales.SalesTerritory T on C.TerritoryID = T.TerritoryID
WHERE(
    (T.[Group] = (@TerritoryGroup))
    AND
    (CR.Name = (@CountryRegion))
     AND
     (SP.Name = (@StateProvince))
    AND
    (A.City = (@City))
   )
ORDER BY S.CustomerID

    Теперь запрос включает параметры запроса, использующие значения параметров отчета.

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

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

Имя параметра Значение параметра

@TerritoryGroup

Pacific

@CountryRegion

Australia

@StateProvince

Victoria

@City

Melbourne
  1. Нажмите кнопку ОК.
    Результирующий набор содержит продажи посредников в городе Мельбурн.
    После добавления параметров запроса в определение запроса для набора данных Resellers они автоматически настраиваются, чтобы получать свои значения из соответствующих значений параметров отчета.
  2. (Не обязательно) Рядом с раскрывающимся списком Набор данных нажмите кнопку Изменить выбранный набор данных (…), затем перейдите на вкладку Параметры. Убедитесь, что параметры запроса @TerritoryGroup, @CountryRegion, @StateProvince и @City привязаны к значениям соответствующих параметров отчета.

Установка значения свойства NoRows

  1. Перейдите на вкладку Макет, чтобы перейти в режим разметки.

  2. Щелкните таблицу, чтобы отобразить маркеры таблицы. Щелкните угловой маркер, чтобы выбрать таблицу. Контур таблицы будет отображен серым цветом.

  3. В окне «Свойства» найдите свойство NoRows. Вставьте в соседнее текстовое поле следующий текст:

    В этой области нет посредников.

Создание заголовка основного отчета и текстовых полей значений параметра

  1. Перейдите на вкладку Макет, чтобы перейти в режим разметки.

  2. Правой кнопкой мыши щелкните текстовое поле, которое содержит временную метку обработки отчета, и выберите Выражение.

  3. Нажмите кнопку выражения (Fx) рядом с текстовым полем Значение. Откроется диалоговое окно Изменение выражения. Замените выражение временной метки следующим выражением:

    ="Report Processed Date: " & 
  Globals!ExecutionTime.ToShortDateString() & " " & 
  Globals!ExecutionTime.ToShortTimeString() & vbCrLf & 
  "Sales Territory for: " 
& Parameters!TerritoryGroup.Value & ", " 
& Parameters!CountryRegion.Value & ", "
& Parameters!StateProvince.Value & ", "
& Parameters!City.Value

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

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

Создание отчета с каскадными параметрами для отображения товарооборота посредников по областям успешно завершено. На следующем занятии рассматривается использование параметров для изменения способа сортировки строк подробностей таблицы и групп таблицы. См. раздел Занятие 3. Изменение исходной сортировки и интерактивная сортировка с помощью параметров.

См. также

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

Работа с параметрами в службах Reporting Services
Использование параметров для управления данными отчета

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

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