使用篩選改善報表效能

  • 發行項

 

發行︰ 2017年1月

適用於： Dynamics 365 (online)、Dynamics 365 (on-premises)、Dynamics CRM 2016、Dynamics CRM Online

傳回大型資料集的報表可能難以使用，且可能造成效能問題。 若要限制報表中呈現的資料，請使用資料篩選。

除了 Reporting Services 支援的資料篩選以外，Microsoft Dynamics 365 還可支援資料預先篩選。 您可以使用預先篩選來：

  • 製作即時線上報表，方法是縮小報表的範圍以傳回相關性更高的資料。

  • 更快擷取和顯示結果集，因為只會傳回相關性較高的資料。

  • 允許使用 [進階尋找] 功能篩選報表。

重要

含有階層式運算子的報表查詢 (例如 Under 運算子) 目前無法用於報表篩選。 當您嘗試執行報表使用階層式運算子時，報表將不會轉譯。

本主題內容

在 Fetch 報表中啟用資料預先篩選

在 SQL 報表中啟用資料預先篩選 (僅限 Dynamics 365 內部部署)

在篩選摘要中傳遞篩選

預設篩選

在 Fetch 報表中啟用資料預先篩選

Fetch 報表只支援自動資料預先篩選。 報表可能有多個資料集和多個 FetchXML 查詢。 一個資料集支援一個 FetchXML 查詢。 若要在 Fetch 報表中啟用主要或連結實體的預先篩選，您必須將 enableprefiltering 參數的值設為 "1"，並且在 prefilterparametername 屬性中指定參數名稱。 參數名稱應以 "CRM_" 開頭，指定其為隱藏參數。 就像在 Microsoft 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 報表定義，而不使用 Microsoft Dynamics 365 Web 應用程式中的 [報表精靈] 或 SQL Server 資料工具 啟用主要和連結實體的預先篩選，請確定：

  1. 同樣地，您可以啟用連結實體的預先篩選。 您也可以為 FetchXML 查詢中的連結實體指定不同的預先篩選條件，方法是在 prefilterparametername 屬性中指定不同的唯一參數名稱。

    如果您要手動修改 Fetch 報表定義，而不使用 Microsoft Dynamics 365 Web 應用程式中的 [報表精靈] 或 SQL Server 資料工具 啟用主要和連結實體的預先篩選，請確定：

    <fetch distinct="false" mapping="logical">
   <entity name="account" enableprefiltering="1" prefilterparametername="CRM_FilteredAccount">

  2. 使用為 prefilterparametername 屬性指定的相同名稱建立對應的查詢參數。 確定參數名稱開頭為 CRM_，指定其為隱藏參數。

    <QueryParameters>
   <QueryParameter Name="CRM_FilteredAccount">
   <Value>=Parameters!CRM_FilteredAccount.Value</Value>
</QueryParameter>

  3. 建立同名的對應報表參數。

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

在 SQL 報表中啟用資料預先篩選 (僅限 Dynamics 365 內部部署)

有兩種方式可以在 Microsoft Dynamics 365SQL 報表上啟用資料預先篩選：自動和明確。

自動預先篩選

自動資料預先篩選適用於簡單的查詢。 若要啟用預先篩選，您可以在查詢中使用實體表格別名。 您要使用以 CRMAF_ 開頭的別名來執行此作業。

例如，下表顯示經過修改的簡單查詢，會在 Account 實體上啟用預先篩選。

沒有預先篩選的查詢

經過修改且啟用自動預先篩選的查詢
```sql SELECT

您使用 CRMAF_ 首碼啟用自動資料預先篩選功能時，Microsoft Dynamics 365 會在查詢上傳至 Microsoft Dynamics 365 時修改它以包含參數 (例如 P1)，如下表所示。

使用自動預先篩選的查詢

由 Microsoft Dynamics 365 進行修改
```sql SELECT

Microsoft Dynamics 365 會根據篩選報表的方式，將查詢傳遞至 P1 參數。 換句話說，自動資料預先篩選就像是現有查詢內的子查詢。

下列範例說明 Microsoft Dynamics 365 如何依據不同的篩選需求將查詢傳遞至參數 (P1)。 下列範例假設您是從 Microsoft 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

當所有實體表格名稱都採用別名時，[進階尋找] 使用者介面會自動包含在部署的報表中 (如果是從 Microsoft Dynamics 365 執行)。

若要為查詢建立器中的實體表格名稱設定別名，以滑鼠右鍵按一下報表中的每一個表格，選取 [屬性]，然後輸入格式為 CRMAF_FilteredEntity 的別名值，例如 CRMAF_FilteredAccount。

自動預先篩選的限制

當您使用 CRMAF_ 首碼啟用自動預先篩選時，Microsoft Dynamics 365 會在查詢中新增參數。 若是較複雜的查詢，例如使用 UNION 陳述式的查詢，這樣做可能會導致未預期的結果，因為 Microsoft 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'

當您上傳報表時，Microsoft 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'

在上面的範例中，從 Microsoft Dynamics 365 的 [報表] 區域中執行報表並選擇年度營收大於 1,000,000 的篩選時，Microsoft 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'

這表示查詢只會傳回 Florida 中年度營收大於 $1,000,000 的帳戶，以及 California 中的所有帳戶，但這並非您所預期。 您想要檢視 Florida 和 California 中年度營收大於 $1,000,000 的所有帳戶。

如果從 Microsoft Dynamics 365 下載報表並且在 Microsoft Visual Studio 中開啟它，就會看到您上傳至 Microsoft Dynamics 365 的原始版報表。 如果您直接從 Microsoft SQL Server Reporting Services 下載報表，就會發現 Microsoft Dynamics 365 已修改查詢，但沒有將參數放在您要的位置。

對於像這樣的複雜查詢，您必須使用明確的預先篩選。

明確預先篩選

對於像是使用 UNION 陳述式的複雜查詢，您可能需要使用明確預先篩選。 與自動預先篩選不同的是，Microsoft Dynamics 365 不會在將這類報表上傳至 Microsoft Dynamics 365 時，藉由在明確預先篩選期間將值傳遞至參數的方式重寫報表查詢。 您必須加入預先篩選參數至報表，然後參考查詢中的參數，藉此對報表明確進行所需變更。 然後您可以使用動態 SQL 執行查詢。

當您使用動態 SQL 時，透過建立名為 CRM_FilteredEntity 的隱藏參數 (例如 CRM_FilteredAccount)，並且在動態 SQL 查詢運算式中使用此參數，就可以啟用透過 [進階尋找] 進行篩選。 這個參數可在從所指定篩選過的檢視取得的表格資料上進行篩選。

若採用先前討論的同一範例強調自動預先篩選的限制，下表所顯示查詢的自動預先篩選已過修改，會透過動態 SQL 使用明確預先篩選。 另外也會假設從 Microsoft Dynamics 365 中的 [報表] 區域執行報表時，已套用年度營收大於 1,000,000 的篩選。

使用自動預先篩選的查詢

查詢已修改為使用明確預先篩選
```sql SELECT

注意

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

在篩選摘要中傳遞篩選

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

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

  1. 建立稱為 CRM_FilterText 的隱藏字串參數。

  2. 將文字方塊報表項目新增至報表，並將其 Value 屬性設定如下：
    =Parameters!CRM_FilterText.Value.

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

預設篩選

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

另請參閱

Dynamics 365 的報表與分析
Microsoft Dynamics 365 (Online) 報表考量事項

© 2017 Microsoft. 著作權所有，並保留一切權利。 著作權