報表產生器函式 - 彙總函式參考Report Builder Functions - Aggregate Functions Reference

若要在報表中加入彙總值,您可以在運算式中使用內建彙總函式。To include aggregated values in your report, you can use built-in aggregate functions in expressions. 數值欄位的預設彙總函式是 SUM。The default aggregate function for numeric fields is SUM. 您可以編輯運算式,並使用不同的內建彙總函式或指定不同的範圍。You can edit the expression and use a different built-in aggregate function or specify a different scope. 範圍會識別用於計算的資料集。Scope identifies which set of data to use for the calculation.

當報表處理器結合報表資料和報表配置時,將會評估每一個報表項目的運算式。As the report processor combines report data and the report layout, the expressions for each report item are evaluated. 當您檢視報表的每一頁時,您會在轉譯的報表項目中看到每一個運算式的結果。As you view each page of the report, you see the results for each expression in the rendered report items.

下表列出可以包含在運算式中的內建函數類別:The following table lists categories of built-in functions that you can include in an expression:

注意

您可以在報表產生器以及 SQL Server Data Tools 的報表設計師中建立和修改分頁報表定義檔 (.rdl)。You can create and modify paginated report definition (.rdl) files in Report Builder and in Report Designer in SQL Server Data Tools. 每種撰寫環境都會提供不同的方式來建立、開啟和儲存報表與相關的項目。Each authoring environment provides different ways to create, open, and save reports and related items.

內建彙總函式Built-in Aggregate Functions

下列內建函數會針對預設範圍或具名範圍,計算非 Null 數值資料集的摘要值。The following built-in functions calculate summary values for a set of non-null numeric data in the default scope or the named scope.

函數Function 說明Description
AvgAvg 傳回運算式指定的所有非 Null 數值的平均值 (在給定範圍中評估)。Returns the average of all non-null numeric values specified by the expression, evaluated in the given scope.
計數Count 傳回運算式指定的非 Null 值的計數 (在給定範圍的內容中評估)。Returns a count of non-null values specified by the expression, evaluated in the context of the given scope.
CountDistinctCountDistinct 傳回運算式指定的所有非 Null 相異值的計數 (在給定範圍的內容中評估)。Returns a count of all distinct non-null values specified by the expression, evaluated in the context of the given scope.
MaxMax 傳回運算式指定的所有非 Null 數值的最大值 (在給定範圍的內容中)。Returns the maximum value of all non-null numeric values specified by the expression, in the context of the given scope. 這個函數可用來指定圖表軸的最大值以控制刻度。You can use this for specifying a chart axis maximum value to control the scale.
MinMin 傳回運算式指定的所有非 Null 數值的最小值 (在給定範圍的內容中)。Returns the minimum value of all non-null numeric values specified by the expression, in the context of the given scope. 這個函數可用來指定圖表軸的最小值以控制刻度。You can use this for specifying a chart axis minimum value to control the scale.
StDevStDev 傳回運算式指定的所有非 Null 數值的標準差 (在給定範圍中評估)。Returns the standard deviation of all non-null numeric values specified by the expression, evaluated in the given scope.
StDevPStDevP 傳回運算式指定的所有非 Null 數值的母體標準差 (在給定範圍的內容中評估)。Returns the population standard deviation of all non-null numeric values specified by the expression, evaluated in the context of the given scope.
SumSum 傳回運算式指定之所有非 Null 數值的總和 (在給定範圍中評估)。Returns the sum of all the non-null numeric values specified by the expression, evaluated in the given scope.
UnionUnion 傳回運算式所指定之 SqlGeometrySqlGeography 類型的所有非 Null 空間資料值聯集 (在給定的範圍中評估)。Returns the union of all the non-null spatial data values of type SqlGeometry or SqlGeography that are specified by the expression, evaluated in the given scope.
VarVar 傳回運算式指定的所有非 Null 數值的變異數 (在給定範圍中評估)。Returns the variance of all non-null numeric values specified by the expression, evaluated in the given scope.
VarPVarP 傳回運算式指定的所有非 Null 數值的母體擴展變異數 (在給定範圍的內容中評估)。Returns the population variance of all non-null numeric values specified by the expression, evaluated in the context of the given scope.

搭配 [回到頁首] 連結使用的箭號圖示回到頁首Arrow icon used with Back to Top linkBack to Top

內建欄位、集合和彙總函式的限制Restrictions on Built-in Fields, Collections, and Aggregate Functions

下表摘要列出您可以加入運算式之報表位置的限制,該運算式包含全域內建集合的參考。The following table summarizes restrictions in report locations on where you can add expressions that contain references to global built-in collections.

報表中的位置Location in Report 欄位Fields 參數Parameters ReportItemsReportItems PageNumberPageNumber

TotalPagesTotalPages
DataSourceDataSource

DataSetDataSet
變數Variables RenderFormatRenderFormat
頁首Page Header

頁尾Page Footer
Yes Yes 最多一個At most one

附註 1Note 1
Yes Yes Yes Yes
本文Body Yes

附註 2Note 2
Yes 只有目前範圍或包含範圍內的項目Only items in the currnet scope or a containing scope

附註 3Note 3
No Yes Yes Yes
報表參數Report Parameter No 只有清單中稍早的參數Only parameters earlier in the list

附註 4Note 4
No No No No No
欄位Field Yes Yes No No No No No
查詢參數Query Parameter No Yes No No No No No
群組運算式Group Expression Yes Yes No No Yes No No
排序運算式Sort Expression Yes Yes No No Yes Yes

附註 5Note 5
No
篩選運算式Filter Expression Yes Yes No No Yes Yes

附註 6Note 6
No
程式碼Code No Yes

附註 7Note 7
No No No No No
報表語言Report.Language No Yes No No No No No
變數Variables Yes Yes No No Yes 目前範圍或包含的範圍Current or containing scope No
彙總Aggregates Yes Yes 只有在頁首和頁尾Only in page header/page footer 只有在報表項目彙總內Only in report item aggregates Yes No No
查閱函數Lookup functions Yes Yes Yes No Yes No No
  • 附註 1:Note 1. ReportItems 必須存在於轉譯的報表頁面中,否則其值會是 Null。ReportItems must exist in the rendered report page, or their value is Null. 如果報表項目的可見性取決於評估為 False 的運算式,則表示報表項目不存在於頁面上。If the visibility of a report item depends on an expression that evaluates to False, the report item does not exist on the page.

  • 附註 2。Note 2. 如果欄位參考用於群組範圍中,而且欄位參考未包含在群組運算式中,則表示未定義該欄位的值,除非此範圍內只有一個值。If a field reference is used in a group scope, and the field reference is not included in the group expression, then the value for the field is undefined, unless there is only one value in the scope. 若要指定值,請使用 First 或 Last 及群組範圍。To specify a value, use First or Last and the group scope.

  • 附註 3。Note 3. 包含 ReportItems 之參考的運算式可以針對相同群組範圍或包含的群組範圍內的其他 ReportItems 指定值。Expressions that include a reference to ReportItems can specify values for other ReportItems in the same group scope or in a containing group scope.

  • 附註 4。Note 4. 之前參數的屬性值可能為 null。Property values for earlier parameters might be null.

  • 附註 5。Note 5. 僅限成員排序。In Member sorts only. 無法在資料區排序運算式內使用。Cannot use in data region sort expressions.

  • 附註 6。Note 6. 僅限成員篩選。In Member filters only. 無法在資料區或資料集篩選運算式內使用。Cannot use in data region or dataset filter expressions.

  • 附註 7。Note 7. 處理程式碼區塊之前沒有初始化參數集合,所以無法使用方法來控制初始化的參數。The Parameters collection is not initialized until after the Code block is processed, so methods cannot be used to control parameters on initialization.

  • 附註 8。Note 8. 除了 Count 與 CountDistinct 以外的所有彙總之資料類型,所有的值都必須是相同的資料類型或 null。Data type for all aggregates except Count and CountDistinct must be the same data type, or null, for all values.

    搭配 [回到頁首] 連結使用的箭號圖示回到頁首Arrow icon used with Back to Top linkBack to Top

巢狀彙總的限制Restrictions on Nested Aggregates

下表摘要列出彙總函式可以將其他彙總函式指定為巢狀彙總的限制。The following table summarizes restrictions on which aggregates functions can specify other aggregate functions as nested aggregates.

內容Context RunningValueRunningValue RowNumberRowNumber 第一個First

最後一個Last
PreviousPrevious Sum 和其他 Presort 函數Sum and Other Presort functions ReportItem 彙總ReportItem aggregates 查閱函數Lookup functions 彙總函式Aggregate Function
執行中的值Running Value No No No No Yes No Yes No
第一個First

最後一個Last
No No No No Yes No No No
PreviousPrevious Yes Yes Yes No Yes No Yes No
Sum 和其他 Presort 函數Sum and other Presort functions No No No No Yes No Yes No
ReportItem 彙總ReportItem aggregates No No No No No No No No
查閱函數Lookup functions Yes Yes

附註 1Note 1
Yes

附註 1Note 1
Yes

附註 1Note 1
Yes

附註 1Note 1
Yes

附註 1Note 1
No No
彙總函式Aggregate Function No No No No No No No No
  • 附註 1:Note 1. 如果查閱函數未包含在彙總內,則只有查閱函數的 Source 運算式中才允許彙總函式。Aggregate functions are only allowed inside the Source expression of a Lookup function if the Lookup function is not contained in an aggregate. 查閱函數的 DestinationResult 運算式內不允許彙總函式。Aggregate functions are not allowed inside the Destination or Result expressions of a Lookup function.

    搭配 [回到頁首] 連結使用的箭號圖示回到頁首Arrow icon used with Back to Top linkBack to Top

計算執行值Calculating Running Values

下列的內建函數會計算資料集的執行值。The following built-in functions calculate running values for a set of data. RowNumberRunningValue 類似,會傳回累加包含範圍內每個資料列的計數執行值。RowNumber is like RunningValue in that it returns the running value of a count that increments for each row within the containing scope. 這些函數的範圍參數必須指定包含範圍,這個範圍控制何時重新開始計數。The scope parameter for these functions must specify a containing scope, which controls when the count restarts.

函數Function 說明Description
RowNumberRowNumber 傳回指定範圍中資料列數的執行計數。Returns a running count of the number of rows for the specified scope. RowNumber 函數從 1 開始重新計數,而不是 0。The RowNumber function restarts counting at 1, not 0.
RunningValueRunningValue 傳回運算式指定的所有非 Null 數值的執行彙總 (在給定範圍中評估)。Returns a running aggregate of all non-null numeric values specified by the expression, evaluated for the given scope.

搭配 [回到頁首] 連結使用的箭號圖示回到頁首Arrow icon used with Back to Top linkBack to Top

擷取資料列計數Retrieving Row Counts

下列的內建函數會計算給定範圍中的資料列數。The following built-in function calculates the number of rows in the given scope. 這個函數可用來計算所有資料列的數目,包括具有 Null 值的資料列。Use this function to count all rows, including rows with null values.

函數Function 說明Description
CountRowsCountRows 傳回指定之範圍中的資料列數目,包括具有 Null 值的資料列。Returns the number of rows in the specified scope, including rows with null values.

搭配 [回到頁首] 連結使用的箭號圖示回到頁首Arrow icon used with Back to Top linkBack to Top

從其他資料集查詢值Looking Up Values from Another Dataset

下列查閱函數會從指定的資料集擷取值。The following lookup functions retrieve values from a specified dataset.

函數Function 說明Description
Lookup 函數Lookup Function 從指定之運算式的資料集傳回值。Returns a value from a dataset for a specified expression.
LookupSet 函數LookupSet Function 從指定之運算式的資料集傳回一組值。Returns a set of values from a dataset for a specified expression.
Multilookup 函數Multilookup Function 從包含名稱/值組的資料集傳回第一組符合某一組名稱的值。Returns the set of first-match values for a set of names from a dataset that contains name/value pairs.

搭配 [回到頁首] 連結使用的箭號圖示回到頁首Arrow icon used with Back to Top linkBack to Top

擷取與排序相依的值Retrieving Sort-Dependent Values

下列的內建函數會傳回給定範圍內的第一個、最後一個或上一個值。The following built-in functions return the first, last, or previous value within a given scope. 這些函數會視資料值的排序次序而定。These functions depend on the sort order of the data values. 舉例而言,這些函數可用來尋找頁面上的第一個和最後一個值,以建立字典樣式的頁首。Use these functions, for example, to find the first and last values on a page to create a dictionary-style page header. Previous 可用來比較特定範圍內一個資料列的值與上一個資料列的值,以在資料表中找出年的成長百分比。Use Previous to compare a value in one row to the previous row's value within a specific scope, for example, to find percentage year over year values in a table.

函數Function 說明Description
第一個First 傳回所指定運算式給定範圍中的第一個值。Returns the first value in the given scope of the specified expression.
最後一個Last 傳回所指定運算式給定範圍中的最後一個值。Returns the last value in the given scope of the specified expression.
PreviousPrevious 傳回某個項目在指定之範圍內上一個執行個體的值或指定的彙總值。Returns the value or the specified aggregate value for the previous instance of an item within the specified scope.

搭配 [回到頁首] 連結使用的箭號圖示回到頁首Arrow icon used with Back to Top linkBack to Top

擷取伺服器彙總Retrieving Server Aggregates

下列的內建函數將從資料提供者擷取自訂彙總。The following built-in function retrieves custom aggregates from the data provider. 例如,您可以使用 Analysis ServicesAnalysis Services 資料來源類型擷取在資料來源伺服器上計算的彙總,以用於群組頁首。For example, using an Analysis ServicesAnalysis Services data source type, you can retrieve aggregates calculated on the data source server for use in a group header.

函數Function 說明Description
AggregateAggregate 傳回指定之運算式的自訂彙總,由資料提供者定義。Returns a custom aggregate of the specified expression, as defined by the data provider.

搭配 [回到頁首] 連結使用的箭號圖示回到頁首Arrow icon used with Back to Top linkBack to Top

測試範圍Testing for Scope

下列的內建函數會測試報表項目目前的內容,查看是否為特定範圍的成員。The following built-in function tests the current context of a report item to see if it is a member of a specific scope.

函數Function 描述Description
InScopeInScope 指出目前項目的執行個體是否在指定的範圍內。Indicates whether the current instance of an item is within the specified scope.

搭配 [回到頁首] 連結使用的箭號圖示回到頁首Arrow icon used with Back to Top linkBack to Top

擷取遞迴層級Retrieving Recursive Level

下列的內建函數會在系統處理遞迴階層時,擷取目前的層級。The following built-in function retrieves the current level when a recursive hierarchy is processed. 在文字方塊中以 Padding 屬性使用此函數的結果,即可控制遞迴群組視覺階層的縮排層級。Use the result of this function with the Padding property in a text box to control the indent level of a visual hierarchy for a recursive group. 如需詳細資訊,請參閱建立遞迴階層群組 (報表產生器及 SSRS)For more information, see Creating Recursive Hierarchy Groups (Report Builder and SSRS).

函數Function 描述Description
LevelLevel 傳回遞迴階層中之目前所在的層級。Returns the current level of depth in a recursive hierarchy.

搭配 [回到頁首] 連結使用的箭號圖示回到頁首Arrow icon used with Back to Top linkBack to Top

另請參閱See Also

報表中的運算式用法 (報表產生器及 SSRS) Expression Uses in Reports (Report Builder and SSRS)
運算式範例 (報表產生器及 SSRS) Expression Examples (Report Builder and SSRS)
總計、彙總與內建集合的運算式範圍 (報表產生器及 SSRS)Expression Scope for Totals, Aggregates, and Built-in Collections (Report Builder and SSRS)