Повышение производительности отчета с использованием фильтров

Статья
03/22/2023

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

Помимо фильтрации данных, поддерживаемой службами Reporting Services, Microsoft Dynamics 365 Customer Engagement (on-premises) поддерживает предварительную фильтрацию данных. Предварительную фильтрацию данных можно использовать для следующих целей:

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

Внимание

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

Включение предварительной фильтрации данных в отчете на основе языка Fetch

Отчеты на основе языка Fetch поддерживают только автоматическую предварительную фильтрацию данных. Отчет может иметь несколько наборов данных и несколько запросов на языке FetchXML. Один набор данных поддерживает один запрос на языке FetchXML. Для включения предварительной фильтрации для основной или связанной сущности в отчете на основе языка Fetch необходимо задать для параметра enableprefiltering значение "1", а также определить имя параметра в свойстве prefilterparametername. Имя параметра должно начинаться с "CRM_", чтобы идентифицировать его как скрытый параметр. Как и в случае отчета на основе SQL Server, это параметр, указанный в запросе на языке FetchXML, работает как вложенный запрос внутри запроса языка FetchXML, и этот вложенный запрос создается со значением, определенным пользователем в области Расширенный поиск во время выполнения отчета.

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

<CommandText  
 <fetch distinct="false" mapping="logical">  
   <entity name="account" enableprefiltering="1" prefilterparametername="CRM_FilteredAccount">  
      <attribute name="name" />  
      <attribute name="accountid" />  
   </entity>  
 </fetch>  
</CommandText>  
<DataSourceName>CRM</DataSourceName>

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

В случае изменения вручную определения отчета на основе языка Fetch без помощи мастера отчетов или SQL Server Data Tools, чтобы включить предварительную фильтрацию для основной и связанной сущностей, необходимо следить за следующим:
```
<fetch distinct="false" mapping="logical">  
<entity name="account" enableprefiltering="1" refilterparametername="CRM_FilteredAccount">  
```
Создайте соответствующей параметр запроса с тем же именем, что указано для свойства prefilterparametername. Убедитесь, что имя параметра начинается с CRM_, чтобы идентифицировать его как скрытый параметр.
```
<QueryParameters>  
<QueryParameter Name="CRM_FilteredAccount">  
<Value>=Parameters!CRM_FilteredAccount.Value</Value>  
</QueryParameter>  
```

Создайте соответствующей параметр отчета с тем же именем.

<ReportParameters>  
<ReportParameter Name="CRM_FilteredAccount">  
<DataType>String</DataType>  
<Prompt>CRM Filtered Account</Prompt>        
</ReportParameter>  
</ReportParameters>

Включение предварительной фильтрации данных в отчетах на основе SQL (только Dynamics 365 On-premises)

Есть два способа включить предварительную фильтрацию данных в отчетах Microsoft Dynamics 365 на основе SQL: автоматически и явно.

Автоматическая предварительная фильтрация

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

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

Без предварительной фильтрации
Автоматическая предварительная фильтрация

Запрос без предварительной фильтрации.

   SELECT <column1>, <column2>, <columnN>
   FROM FilteredAccount;

Модифицированный запрос с включенной автоматической предварительной фильтрацией.

   SELECT <column1>, <column2>, <columnN>
   FROM FilteredAccount AS CRMAF_FilteredAccount;

При включении функции автоматической предварительной фильтрации данных с помощью префикса CRMAF_, Microsoft Dynamics 365 изменяет запрос, чтобы включить параметр (например, P1) при его отправке в Dynamics 365, как показано в следующих примерах.

Автоматическая предварительная фильтрация
Dynamics 365

Запрос с автоматической предварительной фильтрацией.

   SELECT <column1>, <column2>, <columnN>
   FROM FilteredAccount AS CRMAF_FilteredAccount;

Запрос изменен Dynamics 365.

   SELECT <column1>, <column2>, <columnN>
   FROM (@P1) AS CRMAF_FilteredAccount;

Dynamics 365 передаст запрос параметру P1 в зависимости от того, как фильтруется отчет. Другими словами, автоматическая предварительная фильтрация данных действует как подзапрос внутри существующего запроса.

В следующих примерах показано, как Dynamics 365 передает запросы параметру (P1) в соответствии с различными требованиями фильтрации. В этих примерах предполагается, что вы запускаете отчет из области Отчеты в Dynamics 365 и используете параметр фильтрации данных.

Пример 1

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

SELECT <column1>, <column2>, <columnN>
FROM (SELECT FilteredAccount.* FROM FilteredAccount WHERE statecode = 0)
AS CRMAF_FilteredAccount

Пример 2

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

SELECT <column1>, <column2>, <columnN>
FROM (SELECT FilteredAccount.* FROM FilteredAccount WHERE AccountId = '<CurrentAccountId>')
AS CRMAF_FilteredAccount

Пример 3

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

SELECT <column1>, <column2>, <columnN>
FROM  (SELECT FilteredAccount.* FROM FilteredAccount WHERE AccountId in ('<1stAccountId>', '<2ndAccountId>', '<3rdAccountId>') 
AS CRMAF_FilteredAccount

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

Чтобы создать псевдоним для таблицы сущностей в конструкторе запросов, щелкните правой кнопкой мыши каждую таблицу в своем отчете, выберите Свойства, затем введите значение псевдонима в форму CRMAF_FilteredEntity, например, CRMAF_FilteredAccount.

Ограничение автоматической предварительной фильтрации

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

Например, рассмотрим следующий запрос, содержащий операторы UNION:

SELECT <column1>, <column2>, <columnN>
FROM FilteredAccount AS CRMAF_FilteredAccount
WHERE address1_stateorprovince = ‘FL'
UNION
SELECT <column1>, <column2>, <columnN>
FROM FilteredAccount AS CRMAF_FilteredAccount
WHERE address1_stateorprovince = 'CA'

Когда вы отправляете отчет, Dynamics 365 может фильтровать только первый запрос с использованием параметра. Это приводит к тому, что фильтрация не применяется ко второму запросу:

SELECT <column1>, <column2>, <columnN>
FROM  (@P1) AS CRMAF_FilteredAccount WHERE address1_stateorprovince = 'FL'
UNION
SELECT <column1>, <column2>, <columnN>
FROM FilteredAccount AS CRMAF_FilteredAccount
WHERE address1_stateorprovince = 'CA'

В предыдущем примере при запуске отчета из области Отчеты в Dynamics 365 и выборе фильтра в качестве годового дохода, превышающего 1 000 000, Dynamics 365 передаст запрос в параметр P1 следующим образом:

SELECT <column1>, <column2>, <columnN>
FROM  (SELECT FilteredAccount.* from FilteredAccount where AnnualRevenue > 1000000) AS CRMAF_FilteredAccount
WHERE address1_stateorprovince = 'FL'
UNION
SELECT <column1>, <column2>, <columnN>
FROM FilteredAccount AS CRMAF_FilteredAccount
WHERE address1_stateorprovince = 'CA'

Это означает, что запрос вернет только организации во Флориде с годовым доходом, превышающим 1 000 000 долларов США, и все организации в Калифорнии, чего вы не хотели. Вы хотели просмотреть все организации во Флориде и Калифорнии с годовым доходом больше 1 000 000 долларов США.

Если вы загружаете отчет из Dynamics 365 и открываете его в Microsoft Visual Studio, вы увидите исходную версию отчета, которую вы отправили в Dynamics 365. Если вы загрузите отчет непосредственно из службы отчетов Microsoft SQL Server, вы заметите, что Dynamics 365 изменил запрос, но не разместил параметр там, где вы хотели, чтобы он существовал.

Для таких сложных запросов необходимо использовать явную предварительную фильтрацию.

Явная предварительная фильтрация

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

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

Взяв тот же пример, который обсуждался ранее, чтобы подчеркнуть ограничения автоматической предварительной фильтрации, в следующей таблице показан запрос с автоматической предварительной фильтрацией, модифицированной для использования явной предварительной фильтрации с использованием динамического SQL. Также предполагается, что при запуске отчета из области Отчеты в Dynamics 365 фильтр был применен как годовой доход, превышающий 1 000 000.

Автоматическая предварительная фильтрация
Явная предварительная фильтрация

Запрос с автоматической предварительной фильтрацией.

   SELECT <column1>, <column2>, <columnN>
   FROM FilteredAccount AS CRMAF_FilteredAccount
   WHERE address1_stateorprovince = ‘FL'
   UNION
   SELECT <column1>, <column2>, <columnN>
   FROM FilteredAccount AS CRMAF_FilteredAccount
   WHERE address1_stateorprovince = 'CA'

Запрос изменен для использования явной предварительной фильтрации.

   DECLARE @SQL nvarchar(4000)
   DECLARE @CRM_FilteredAccount nvarchar(2000)
   Set @CRM_FilteredAccount = 'Select FilteredAccount.* FROM FilteredAccount where AnnualRevenue > 1000000'
   SET @SQL = 'SELECT <column1>, <column2>, <columnN>
   FROM ('+@CRM_FilteredAccount+') AS FA where address1_stateorprovince = ''FL''
   UNION
   SELECT <column1>, <column2>, <columnN> 
   FROM ('+@CRM_FilteredAccount+') as CA where address1_stateorprovince  = ''CA'' '
   EXEC (@SQL)

Заметка

В большинстве стандартных отчетов Dynamics 365 на основе SQL используется параметр явной предварительной фильтрации.

Передача фильтров в сводку фильтров

Сводка фильтров отображает значение фильтра, которое используется при выполнении отчета. В отчетах она отображается как элемент отчета в виде текстового поля в заголовке отчета, содержащего текстовое значение фильтра. Когда пользователь выполняет отчет, в средстве просмотра отчетов отображается кнопка Изменить фильтр. При нажатии этой кнопки пользователь может определить фильтр данных. Пример сводки фильтра можно найти в отчете "Сводка пользователей", который входит в состав Customer Engagement (on-premises).

Чтобы добавить сводку фильтров в отчет, выполните следующие действия.

Создайте скрытый строковый параметр с именем CRM_FilterText.
Добавьте элемент отчета "текстовое поле" в отчет и установите для него свойство Value следующим образом:
=Parameters!CRM_FilterText.Value.

При выполнении этого отчета для значения параметра CRM_FilterText система задает текст текущего фильтра.

Фильтры по умолчанию

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

См. также

Руководство по отчетам и аналитике
Рекомендации по отчетам Dynamics 365 for Customer Engagement (on-premises)

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