# 總計、彙總與內建集合的運算式範圍Expression Scope for Totals, Aggregates, and Built-in Collections

• 資料範圍 ：資料範圍是報表處理器所使用的範圍階層，因為它會結合報表資料和報表配置，並建立用來顯示資料的資料區，例如資料表及圖表。Data scope Data scope is a hierarchy of scopes that the report processor uses as it combines report data and report layout, and builds out data regions such as tables and charts on which to display the data. 了解資料範圍可協助您在執行下列動作時得到您希望的結果：Understanding data scope helps you to get the results that you want when you do the following:

• 撰寫使用彙總函式的運算式 ：指定要彙總的資料。Write expressions that use aggregate functions Specify which data to aggregate. 運算式在報表中的位置會影響彙總計算範圍的資料。The location of the expression in the report influences which data is in scope for aggregate calculations.

• 將走勢圖加入至資料表或矩陣 ：指定要對齊資料表或矩陣中之巢狀執行個體的圖表座標軸最小和最大範圍。Add sparklines to a table or matrix Specify a minimum and maximum range for chart axes to align nested instances in a table or matrix.

• 將指標加入至資料表或矩陣 ：指定要對齊資料表或矩陣中之巢狀執行個體的量測計最小和最大刻度。Add indicators to a table or matrix Specify a minimum and maximum scale for the gauge to align nested instances in a table or matrix.

• 撰寫排序運算式 ：指定一個您可以用來同步處理多個相關報表項目之間的排序次序之包含範圍。Write sort expressions Specify a containing scope that you can use to synchronize sort order among multiple related report items.

• 資料格範圍 ：資料格範圍是資料格所屬之 Tablix 資料區中資料列及資料行群組的集合。Cell scope Cell scope is the set of row and column groups in a tablix data region to which a cell belongs. 依預設，每個 Tablix 資料格都包含一個文字方塊。By default, each tablix cell contains a text box. 文字方塊的值為運算式。The value of the text box is the expression. 資料格的位置會間接決定您可以在運算式中針對彙總函式指定的資料範圍。The location of the cell indirectly determines which data scopes you can specify for aggregate calculations in the expression.

• 報表項目範圍 ：報表項目範圍指的是轉譯之報表頁面上的項目集合。Report item scope Report item scope refers to the collection of items on a rendered report page. 報表處理器會結合資料與報表配置元素來產生已編譯的報表定義。The report processor combines data and report layout elements to produce a compiled report definition. 在此程序期間，資料區 (例如資料表和矩陣) 會依需要擴展以顯示所有報表資料。During this process, data regions such as tables and matrices expand as needed to display all of the report data. 接著，報表轉譯器會處理已編譯的報表。The compiled report is then processed by a report renderer. 報表轉譯器會決定每一頁所出現的報表項目。The report renderer determines which report items appear on each page. 在報表伺服器上，當您檢視每個頁面時，系統就會轉譯頁面。On a report server, each page is rendered as you view it. 匯出報表時，所有頁面都會經過轉譯。When you export a report, all pages are rendered. 了解報表項目範圍可協助您在執行下列動作時得到您希望的結果：Understanding report item scope helps you get the results that you want when you do the following:

• 加入切換項目 ：指定文字方塊來加入控制報表項目可見性的切換。Add toggle items Specify a text box to add the toggle that controls the visibility of a report item. 您只能將切換加入至您要切換之報表項目範圍中的文字方塊。You can only add a toggle to text boxes that are in the scope of the report item that you want to toggle.

• 在頁首與頁尾中撰寫運算式 ：在文字方塊或出現在轉譯頁面之其他報表項目中，指定運算式的值。Write expressions in page headers and footers Specify values in expressions in text boxes or other report items that appear on the rendered page.

了解範圍可協助您成功撰寫能夠提供您希望之結果的運算式。Understanding scopes helps you to successfully write expressions that give you the results that you want.

## 了解資料範圍和資料階層Understanding Data Scope and Data Hierarchy

• 套用資料集篩選後的資料集 ：指定連結到報表主體中之資料區或報表項目的報表資料集。Datasets, after dataset filters are applied Specifies the report dataset linked to the data region or to a report item in the report body. 用於彙總的資料是在套用資料集篩選運算式之後取自報表資料集。The data used for aggregation is from the report dataset after dataset filter expressions are applied. 若是共用資料集，這表示共用資料集定義中的篩選與報表之共用資料集執行個體中的篩選。For shared datasets, this means both the filters in the shared dataset definition and the filters in the shared dataset instance in the report.

• 資料區域 ：指定在套用資料區域篩選和排序運算式之後取自資料區域的資料。Data regions Specifies data from the data region after data region filter and sort expressions are applied. 在計算資料區的彙總時，不會使用群組篩選。Group filters are not used when calculating aggregates for data regions.

• 套用群組篩選後的資料區群組 ：指定在套用群組運算式和群組篩選之後父群組和子群組的資料。Data region groups, after group filters are applied Specifies the data after the group expressions and group filters are applied for the parent group and child groups. 若是資料表，這式資料列和資料行群組。For a table, this is the row and column groups. 若是圖表，這是數列和類別目錄群組。For a chart, this is the series and category groups. 為了識別範圍內含項目，每個父群組都會包含其子群組。For the purposes of identifying scope containment, every parent group contains its child groups.

• 巢狀資料區域 —針對要加入資料的資料格內容，指定在套用巢狀資料區域篩選和排序運算式之後的巢狀資料區域的資料。Nested data regions Specifies the data for the nested data region in the context of the cell to which it has been added, and after the nested data region filter and sort expressions have been applied.

• 巢狀資料區域的資料列和資料行群組 ：指定在套用巢狀資料區域的群組運算式和群組篩選之後的資料。Row and column groups for the nested data regions Specifies the data after the nested data region group expressions and group filters have been applied.

撰寫包含彙總函式的運算式時，了解包含和被包含的範圍相當重要。Understanding containing and contained scopes is important when you write expressions that include aggregate functions.

## 資料格範圍和運算式Cell Scope and Expressions

• 預設範圍 ：報表處理器評估運算式時，位於計算範圍中的資料。Default scope The data that is in scope for calculations when the report processor evaluates an expression. 預設範圍資料格或資料點所屬的最內部一組群組。The default scope is the innermost set of groups to which the cell or data point belongs. 若是 Tablix 資料區，此集合可以包含資料列和資料行群組。For a tablix data region, the set can include row and column groups. 若是圖表資料區，此集合可以包含類別目錄和數列群組。For a chart data region, the set can include category and series groups.

• 具名範圍 ：位於運算式範圍中之資料集、資料區或資料區群組的名稱。Named scope The name of a dataset, a data region, or a data region group that is in scope for the expression. 若是彙總計算，您可以指定一個包含的範圍。For aggregate calculations, you can specify a containing scope. 您無法在單一運算式中同時指定資料列群組和資料行群組的具名範圍。You cannot specify a named scope for both a row group and a column group in a single expression. 除非運算式是供彙總使用，否則您無法指定被包含的範圍。You cannot specify a contained scope unless the expression is for an aggregate of an aggregate.

下列運算式會產生 SellStartDate 與 LastReceiptDate 之間的間隔年數。The following expression generates the interval years between SellStartDate and LastReceiptDate. 這些欄位位於兩個不同的資料集：DataSet1 和 DataSet2 中。These fields are in two different datasets, DataSet1 and DataSet2. First 函式 (報表產生器及 SSRS) 為彙總函式，它會傳回 DataSet1 中 SellStartDate 的第一個值，以及 DataSet2 中 LastReceiptDate 的第一個值。The First Function (Report Builder and SSRS), which is an aggregate function, returns the first value of SellStartDate in DataSet1 and the first value of LastReceiptDate in DataSet2.

``````=DATEDIFF(“yyyy”, First(Fields!SellStartDate.Value, "DataSet1"), First(Fields!LastReceiptDate.Value, "DataSet2"))
``````
• 網域範圍 ：也稱為同步處理範圍。Domain scope Also called synchronization scope. 一種資料範圍的類型，適用於巢狀資料區的運算式評估。A type of data scope that applies to expression evaluation for nested data regions. 網域範圍用來跨所有群組執行個體指定彙總，讓系統可以對齊巢狀執行個體並輕鬆進行比較。Domain scope is used to specify aggregates across all instances of a group so that nested instances can be aligned and easily compared. 例如，您可以對齊資料表中內嵌之走勢圖的範圍和高度，讓這些值可以對齊。For example, you can align the range and height for sparklines embedded in a table so that the values line up.

在某些報表位置，您必須指定一個範圍。In some locations of a report, you must specify a scope. 例如，若是設計介面上的文字方塊，您必須指定要使用的資料集名稱： `=Max(Fields!Sales.Value,"Dataset1")`For example, for a text box on the design surface, you must specify the name of the dataset to use: `=Max(Fields!Sales.Value,"Dataset1")`. 在某些位置，則有一個隱含的預設範圍。In other locations, there is an implicit default scope. 例如，如果您沒有在群組範圍中指定文字方塊的彙總，則會先使用預設彙總。For example, if you do not specify an aggregate for a text box in a group scope, the default aggregate First is used.

每個彙總函式主題都會列出適用於其用途的範圍。Each aggregate function topic lists the scopes that are valid for its use. 如需詳細資訊，請參閱 彙總函式參考 (報表產生器和 SSRS)For more information, see Aggregate Functions Reference (Report Builder and SSRS).

## 資料表資料區的彙總運算式範例Example Aggregate Expressions for a Table Data Region

• 包含邊角資料格與三個資料列 (包括資料行群組標頭) 的資料表標頭。A table header that contains the corner cell and three rows that include the column group headers.

• 以名稱為 Cat 之類別目錄和名稱為 SubCat 之子類別目錄為基礎的兩個巢狀資料列群組。Two nested row groups based on category named Cat and subcategory named SubCat.

• 以名稱為 Year 之年度與名稱為 Qtr 之季度為基礎的兩個巢狀資料行群組。Two nested column groups based on year named Year and quarter named Qtr.

• 標示為 Totals 的一個靜態總計資料行。One static totals column labeled Totals.

• 以名稱為 Territory 之銷售領域為基礎的一個相鄰資料行群組。One adjacent column group based on sales territory named Territory.

領域群組的資料行標頭已針對顯示用途，分成兩個資料格。The column header for the territory group has been split into two cells for display purposes. 第一個資料格會顯示領域名稱與總計，而第二個資料格中的預留位置文字會針對每個領域計算所有銷售量所佔的百分比。The first cell displays the territory name and totals, and the second cell has placeholder text that calculated the percentage contribution for each territory to all sales.

假設資料集的名稱為 DataSet1，而資料表的名稱為 Tablix1。Assume the dataset is named DataSet1 and the table is named Tablix1. 下表列出資料格標籤、預設範圍和範例。The following table lists the cell label, the default scope, and examples. 預留位置文字的值會以運算式語法顯示。The values for placeholder text are shown by in expression syntax.

C01C01 Tablix1Tablix1 [Sum(Qty)][Sum(Qty)] 彙總和範圍Aggregates and Scope

`=Sum(Fields!Qty.Value)`
C02C02 外部資料行群組 "Year"Outer column group "Year" [Year][Year]

([YearQty])([YearQty])
`=Fields!Year.Value`

`=Sum(Fields!Qty.Value)`
C03C03 Tablix1Tablix1 [Sum(Qty)][Sum(Qty)] 總計Totals

`=Sum(Fields!Qty.Value)`
C04C04 對等資料行群組 "Territory"Peer column group "Territory" ([Total])([Total]) TerritoryTerritory

`=Sum(Fields!Qty.Value)`
C05C05 內部群組 "Qtr"Inner group "Qtr" [Qtr][Qtr]

([QtrQty])([QtrQty])
QQ

`=Fields!Qtr.Value`

`=Sum(Fields!Qty.Value)`
C06C06 對等資料行群組 "Territory"Peer column group "Territory" [Territory][Territory]

([Tty])([Tty])

[Pct][Pct]
`=Fields!Territory.Value`

`=Sum(Fields!Qty.Value)`

`=FormatPercent(Sum(Fields!Qty.Value,"Territory")/Sum(Fields!Qty.Value,"Tablix1"),0) & " of " & Sum(Fields!Qty.Value,"Tablix1")`
C07C07 外部資料列群組 "Cat"Outer row group "Cat" [Cat][Cat]

[Sum(Qty)][Sum(Qty)]
`=Fields!Cat.Value`

`=Sum(Fields!Qty.Value)`
C08C08 與 C07 相同Same as C07
C09C09 外部資料列群組 "Cat" 和內部資料行群組 "Qtr"Outer row group "Cat" and inner column group "Qtr" [Sum(Qty)][Sum(Qty)] `=Sum(Fields!Qty.Value)`
C10C10 與 C07 相同Same as C07 <<Expr>><<Expr>> `=Sum(Fields!Qty.Value) & ": " & FormatPercent(Sum(Fields!Qty.Value)/Sum(Fields!Qty.Value,"Tablix1"),0) & " of " & Sum(Fields!Qty.Value,"Tablix1")`
C11C11 外部資料列群組 "Cat" 和資料行群組 "Territory"Outer row group "Cat" and column group "Territory" <<Expr>><<Expr>> `=Sum(Fields!Qty.Value) & ": " & FormatPercent(Sum(Fields!Qty.Value)/Sum(Fields!Qty.Value,"Territory"),0) & " of " & Sum(Fields!Qty.Value,"Territory")`
C12C12 內部資料列群組 "Subcat"Inner row group "Subcat" [Subcat][Subcat]

[Sum(Qty)][Sum(Qty)]
`=Fields!SubCat.Value`

`=Sum(Fields!Qty.Value)`
C13C13 內部資料列群組 "Subcat" 和內部資料行群組 "Qtr"Inner row group "Subcat" and inner column group "Qtr" [Sum(Qty)][Sum(Qty)] `=Sum(Fields!Qty.Value)`
C14C14 內部資料列群組 "Subcat"Inner row group "Subcat" <<Expr>><<Expr>> `=Sum(Fields!Qty.Value) & ": " & FormatPercent(Sum(Fields!Qty.Value)/Sum(Fields!Qty.Value,"Cat"),0) & " of " & Sum(Fields!Qty.Value,"Cat")`
C15C15 內部資料列群組 "Subcat" 和資料行群組 "Territory"Inner row group "Subcat" and column group "Territory" <<Expr>><<Expr>> `=Sum(Fields!Qty.Value) & ": " & FormatPercent(Code.CalcPercentage(Sum(Fields!Qty.Value),Sum(Fields!Qty.Value,"Cat")),0) & " of " & Sum(Fields!Qty.Value,"Cat")`

## 從頁首或頁尾指定範圍Specifying Scopes from the Page Header or Page Footer

For more information, see Page Headers and Footers (Report Builder and SSRS) and ReportItems Collection References (Report Builder and SSRS).