使用篩選改善報表效能

發行項
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 會修改查詢，使其在上傳至 Dynamics 365 時包含一個參數 (例如，P1)，如下列範例所示。

自動預先篩選
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 Reporting Services 下載報表，則會發現 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)

Note

大部分的標準 Dynamics 365 SQL 報表都會使用明確預先篩選選項。

在篩選摘要中傳遞篩選

篩選摘要會顯示報表執行時使用的篩選值。在報表中，它會顯示為報表標題中包含篩選文字值的文字方塊報表項目。當使用者執行報表時，報表檢視器會顯示編輯篩選按鈕。按一下按鈕時，可讓使用者定義資料篩選。篩選摘要的範例可在 Customer Engagement (on-premises) 隨附的「使用者摘要」報表中找到。

若要將篩選摘要新增至報表，請依照下列步驟執行：

建立稱為 CRM_FilterText 的隱藏字串參數。
將文字方塊報表項目新增至報表，並將其 Value 屬性設定如下：
=Parameters!CRM_FilterText.Value.

報表執行時，CRM_FilterText 參數的值將由系統設定為目前篩選的文字。

預設篩選

當您發行報表時，可以設定預設篩選。針對所有使用報表精靈建立的報表，如果您未設定預設篩選，則篩選會自動設定為過去 30 天內修改過之實體的所有記錄。如需定義預設報表篩選的程序，請參閱發行報表。

請參閱

報表與分析指南
 Dynamics 365 for Customer Engagement (on-premises) 報表考量事項

共用方式為