Использование каскадных параметров в отчетах с разбивкой на страницы

Эта статья предназначена для разработки отчетов Power BI с разбивкой на страницы. Он предоставляет сценарии разработки каскадных параметров. Каскадные параметры — это параметры отчета с зависимостями. Когда пользователь отчета выбирает значение параметра (или значения), он используется для задания доступных значений для другого параметра.

Примечание.

Общие сведения о каскадных параметрах и их настройке не рассматриваются в этой статье. Если вы не знакомы с каскадными параметрами, рекомендуем сначала прочитать каскадные параметры в отчет в Power BI построитель отчетов.

Сценарии разработки

Существует два сценария проектирования для использования каскадных параметров. Их можно эффективно использовать для:

• Фильтрация больших наборов элементов
• Представление соответствующих элементов

Пример базы данных

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

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

• ResellerCode (целое число)
• ResellerName
• Country-Region
• State-Province
• Город
• PostalCode

Также есть таблица с именем Sales. Он хранит записи заказов на продажу и имеет отношение внешнего ключа к таблице Торгового посредника в столбце ResellerCode .

Пример требования

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

Фильтрация больших наборов элементов

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

• Фильтрация по связанным столбцам
• Фильтрация по столбцу группировки
• Фильтрация по шаблону поиска

Фильтрация по связанным столбцам

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

Вот как можно разработать каскадные параметры:

1. Создайте пять параметров отчета, упорядоченных в правильной последовательности.

2. Создайте набор данных CountryRegion, который извлекает отдельные значения стран-региона, используя следующую инструкцию запроса:

   SELECT DISTINCT
     [Country-Region]
   FROM
     [Reseller]
   ORDER BY
     [Country-Region]
   

3. Создайте набор данных StateProvince, который извлекает отдельные значения провинции штата для выбранного региона страны, используя следующую инструкцию запроса:

   SELECT DISTINCT
     [State-Province]
   FROM
     [Reseller]
   WHERE
     [Country-Region] = @CountryRegion
   ORDER BY
     [State-Province]
   

4. Создайте набор данных "Город", который извлекает отдельные значения города для выбранной страны-региона и провинции, используя следующую инструкцию запроса:

   SELECT DISTINCT
     [City]
   FROM
     [Reseller]
   WHERE
     [Country-Region] = @CountryRegion
     AND [State-Province] = @StateProvince
   ORDER BY
     [City]
   

5. Перейдите к этому шаблону, чтобы создать набор данных Почтового кода .

6. Создайте набор данных торгового посредника, чтобы получить все торговые посредники для выбранных географических значений, используя следующую инструкцию запроса:

   SELECT
     [ResellerCode],
     [ResellerName]
   FROM
     [Reseller]
   WHERE
     [Country-Region] = @CountryRegion
     AND [State-Province] = @StateProvince
     AND [City] = @City
     AND [PostalCode] = @PostalCode
   ORDER BY
     [ResellerName]
   

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

Примечание.

Все параметры запроса (префикс с символом @), показанные в этих примерах, могут быть внедрены в инструкции SELECT или переданы в хранимые процедуры.

Как правило, хранимые процедуры лучше подходят к проектированию. Это связано с тем, что их планы запросов кэшируются для быстрого выполнения, и они позволяют разрабатывать более сложную логику при необходимости. Однако в настоящее время они не поддерживаются для реляционных источников данных шлюза, что означает SQL Server, Oracle и Teradata.

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

Фильтрация по столбцу группировки

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

Вот как можно разработать каскадные параметры:

1. Создайте параметры отчета ReportGroup и торгового посредника, упорядоченные в правильной последовательности.

2. Создайте набор данных ReportGroup, чтобы получить первые буквы, используемые всеми торговыми посредниками, с помощью следующей инструкции запроса:

   SELECT DISTINCT
     LEFT([ResellerName], 1) AS [ReportGroup]
   FROM
     [Reseller]
   ORDER BY
     [ReportGroup]
   

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

   SELECT
     [ResellerCode],
     [ResellerName]
   FROM
     [Reseller]
   WHERE
     LEFT([ResellerName], 1) = @ReportGroup
   ORDER BY
     [ResellerName]
   

4. Сопоставите параметр запроса набора данных торгового посредника с соответствующим параметром отчета.

Более эффективно добавить столбец группировки в таблицу Торгового посредника. При сохранении и индексировании он обеспечивает наилучший результат. Дополнительные сведения см. в статье Specify Computed Columns in a Table.

ALTER TABLE [Reseller]
ADD [ReportGroup] AS LEFT([ResellerName], 1) PERSISTED

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

ALTER TABLE [Reseller]
ADD [ReportGroup2] AS CASE
  WHEN [ResellerName] LIKE '[A-C]%' THEN 'A-C'
  WHEN [ResellerName] LIKE '[D-H]%' THEN 'D-H'
  WHEN [ResellerName] LIKE '[I-M]%' THEN 'I-M'
  WHEN [ResellerName] LIKE '[N-S]%' THEN 'N-S'
  WHEN [ResellerName] LIKE '[T-Z]%' THEN 'T-Z'
  ELSE '[Other]'
END PERSISTED
GO

CREATE NONCLUSTERED INDEX [Reseller_ReportGroup2]
ON [Reseller] ([ReportGroup2]) INCLUDE ([ResellerCode], [ResellerName])
GO

Фильтрация по шаблону поиска

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

Вот как можно разработать каскадные параметры:

1. Создайте параметры отчета поиска и торгового посредника, упорядоченные в правильной последовательности.

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

   SELECT
     [ResellerCode],
     [ResellerName]
   FROM
     [Reseller]
   WHERE
     [ResellerName] LIKE '%' + @Search + '%'
   ORDER BY
     [ResellerName]
   

3. Сопоставите параметр запроса набора данных торгового посредника с соответствующим параметром отчета.

Совет

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

Дополнительные сведения см. в статье LIKE (Transact-SQL).

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

WHERE
  [ResellerName] LIKE @Search

Однако многие профессионалы, не являющиеся базами данных, не знают о проценте (%) дикий карта символ. Вместо этого они знакомы со звездочкой (*). Изменив предложение WHERE, вы можете разрешить им использовать этот символ.

WHERE
  [ResellerName] LIKE SUBSTITUTE(@Search, '%', '*')

Представление соответствующих элементов

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

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

Вот как можно разработать каскадные параметры:

1. Создайте параметры отчета OrderDateStart, OrderDateEnd и торговых посредников, упорядоченные в правильной последовательности.

2. Создайте набор данных торгового посредника, чтобы получить все торговые посредники, создающие заказы в период даты, с помощью следующей инструкции запроса:

   SELECT DISTINCT
     [r].[ResellerCode],
     [r].[ResellerName]
   FROM
     [Reseller] AS [r]
   INNER JOIN [Sales] AS [s]
     ON [s].[ResellerCode] = [r].[ResellerCode]
   WHERE
     [s].[OrderDate] >= @OrderDateStart
     AND [s].[OrderDate] < DATEADD(DAY, 1, @OrderDateEnd)
   ORDER BY
     [r].[ResellerName]
   

Рекомендации

По возможности рекомендуется создавать отчеты с каскадными параметрами. Это потому, что они:

• Интуитивно понятные и полезные возможности для пользователей отчета
• Эффективны, так как они извлекают меньшие наборы доступных значений

Обязательно оптимизируйте источники данных следующими способами:

• Использование хранимых процедур по возможности
• Добавление соответствующих индексов для эффективного извлечения данных
• Материализация значений столбцов (и даже строк), чтобы избежать дорогостоящих вычислений во время запроса

Связанный контент

Дополнительные сведения, связанные с этой статьей, проверка следующие ресурсы:

• Параметры отчета в Power BI построитель отчетов
• Добавление каскадных параметров в отчет (построитель отчетов)
• Вопросы? Задайте их в сообществе Power BI.
• Есть предложения? Участие в разработке идей по улучшению Power BI