Lesson 6: Adding Grouping and Totals (Reporting Services)Lesson 6: Adding Grouping and Totals (Reporting Services)

在本教學課程中,您會將群組和總計新增至 Reporting ServicesReporting Services 報表以組織和摘要資料。In this tutorial lesson, you will add grouping and totals to your Reporting ServicesReporting Services report to organize and summarize your data.

將報表中的資料分組To group data in a report

  1. 按一下 [設計] 索引標籤。Click the Design tab.

  2. 如果您看不到 [資料列群組] 窗格,請以滑鼠右鍵按一下設計介面,並按一下 [檢視],然後按一下 [群組]。If you do not see the Row Groups pane , right-click the design surface and click View and then click Grouping.

  3. Date 欄位從 [報表資料] 窗格拖曳到 [資料列群組] 窗格。From the Report Data pane, drag the Date field to the Row Groups pane. 將它放在稱為 [(詳細資料)] 之資料列的上方。Place it above the row called (Details).

    請注意,資料列控制代碼中現在具有一個用來顯示群組的方括號。Note that the row handle now has a bracket in it, to show a group. 資料表現在也具有兩個 [日期] 資料行 – 垂直虛線兩側各有一個。The table now also has two Date columns -- one on either side of a vertical dotted line.

    新增的日期群組date group added

  4. Order 欄位從 [報表資料] 窗格拖曳到 [資料列群組] 窗格。From the Report Data pane, drag the Order field to the Row Groups pane. 將它放在 [日期] 下方和 [(詳細資料)] 上方。Place it below Date and above (Details).

ssrs_ssdt_addorderfield

Note that the row handle now has two brackets in it ![ssrs_ssdt_rowgroupdoublehandles](../reporting-services/media/ssrs-ssdt-rowgroupdoublehandles.png), to show two groups. The table now has two **Order** columns, too.  
  1. 刪除雙線右側的原始 [日期] 和 [訂單] 資料行。Delete the original Date and Order columns to the right of the double line. 這樣會移除這個個別記錄值,所以只有群組值會顯示。This removes this individual record values so that only the group value is displayed. 選取這兩個資料行的資料行控制代碼,並按一下滑鼠右鍵,然後按一下 [刪除資料行]。Select the column handles for the two columns, right-click and click Delete Columns.

    選取要刪除的資料行Select columns to delete

  2. 若要格式化新的日期資料行,請以滑鼠右鍵按一下含有 [Date] 欄位運算式的資料格,然後按一下 [文字方塊屬性]。To format the new date column, Right-click the cell with the [Date] field expression and then click Text Box Properties.

  3. 按一下 [數字],然後按一下 [類別目錄] 欄位中的 [日期]。Click Number, and then in the Category field, click Date.

  4. [類型] 方塊中,選取 [January 31, 2000]In the Type box, select January 31, 2000.

  5. 按一下 [確定] 。Click OK..

  6. 切換到 [預覽] 索引標籤預覽報表。Switch to the Preview tab to preview the report. 報表應看起來類似下圖:It should look similar to the following illustration:
    rs_BasicTableGroupsPreviewrs_BasicTableGroupsPreview

在報表中加入總計To add totals to a report

  1. 切換至 [設計] 檢視。Switch to Design view.

  2. 以滑鼠右鍵按一下含有 [LineTotal] 欄位的資料區資料格,然後按一下 [新增總計]。Right-click the data region cell that contains the field [LineTotal], and click Add Total.

    這樣會加入每筆訂單的總金額資料列。This adds a row with a sum of the dollar amount for each order.

  3. 以滑鼠右鍵按一下含有 [Qty] 欄位的資料格,然後按一下 [新增總計]。Right-click the cell that contains the field [Qty], and click Add Total.

    這樣會在總計資料列中加入每筆訂單的總數量。This adds a sum of the quantity for each order to the totals row.

  4. Sum[Qty]左側的空白資料格中,輸入訂單總額標籤。In the empty cell to the left of Sum[Qty], type the label "Order Total".

  5. 您也可以在總計資料列中加入背景色彩。You can add a background color to the totals row. 選取兩個總和資料格以及標籤資料格。Select the two sum cells and the label cell.

  6. [格式] 功能表上,依序按一下 [背景色彩][淺灰][確定]On the Format menu, click Background Color, click Light Gray, and click OK.

    設計檢視:具有訂單總計的基本資料表Design view: Basic table with order total

在報表中加入每日總計To add a daily total to a report

  1. 以滑鼠右鍵按一下 [訂單] 資料格,並指向 [加入總計],然後按一下 [之後]。Right-click the Order cell, point to Add Total, and click After.

    這樣會新增含有每日數量和金額總和的新資料列,以及 [訂單] 資料行底端的 [總計] 標籤。This adds a new row containing sums of the quantity and dollar amount for each day, and the label "Total" to the bottom of the Order column.

  2. 在同一資料格的 總計 一詞之前,輸入 每日 一詞,使其讀為 [每日總計]Type the word Daily before the word Total in the same cell, so it reads Daily Total.

  3. 選取 [每日總計] 資料格、兩個 [總和] 資料格以及它們之間的空白資料格。Select the Daily Total cell, the two Sum cells and the empty cell between them.

  4. [格式] 功能表上,依序按一下 [背景色彩][橙色][確定]On the Format menu, click Background Color, click Orange, and click OK.

在報表中加入總計To add a grand total to a report

  1. 以滑鼠右鍵按一下 [日期] 資料格,並指向 [加入總計],然後按一下 [之後]。Right-click the Date cell, point to Add Total, and click After.

    這樣會加入含有整個報表每日數量和金額總和的新資料列,以及 [日期] 資料行中的 [總計] 標籤。This adds a new row containing sums of the quantity and dollar amount for the entire report, and the Total label in the Date column.

  2. 在同一資料格的 總計 一詞之前,輸入 全部 一詞,使其讀為 [全部總計]Type the word Grand before the word Total in the same cell, so it reads Grand Total.

  3. 選取 [全部總計] 資料格、兩個 [總和] 資料格以及它們之間的空白資料格。Select the Grand Total cell, the two Sum cells and the empty cells between them.

  4. [格式] 功能表上,依序按一下 [背景色彩][淺藍][確定]On the Format menu, click Background Color, click Light Blue, and click OK.

    設計檢視:基本資料表中的總計Design view: Grand total in basic table

  5. 按一下 [預覽]Click Preview.

    最後一頁應該與下列影像類似。The last page should look similar to the following image. 在工具列中,按一下 [最後一頁]In the toolbar, click the Last Page ssrs_ssdt_viewertoolbar_lastpage按鈕,可選取色彩。button.

    預覽:具有總計的基本資料表Preview: Basic table with grand total

若要將報表發行至報表伺服器 (選擇性)To Publish the Report to the Report Server (Optional)

  1. 選擇性步驟是將已完成的報表發行至原生模式報表伺服器,讓您能夠在入口網站中檢視報表。An optional step is to publish the completed report to the native mode report server so you can view the report in the web portal.

  2. 按一下 [專案] 功能表,然後按一下 [Tutorial 屬性...]Click the Project menu and then click tutorial Properties...

  3. TargetServerURL 中,輸入報表伺服器的名稱,例如In the TargetServerURL type the name of your report server, for example

  4. http:/<servername>/reportserver
  • http://localhost/reportserver 適用於在報表伺服器上設計報表時。http://localhost/reportserver works if your designing the report on the report server.
  1. 請記住,TargetReportFolder 是 tutorial (即專案名稱)。Note the TargetReportFolder is tutorial, the name of the project. 這是下一步中將在其中部署報表的資料夾名稱。This is the name of the folder that the report will deploy to in the next steps.
  2. 按一下 [確定]Click OK

  3. 在 [建立] 功能表上,按一下 [部署教學課程]。On click the Build menu and then click Deploy tutorial.

    如果您在輸出視窗中看見類似下面的訊息,就表示部署成功。If you see a message similar to the following in the output window, it indicates a successful deployment.

    ------ 已經開始建立: 專案: tutorial,組態: 偵錯 ------------ Build started: Project: tutorial, Configuration: Debug ------
    正在略過 'Sales Orders.rdl'。Skipping 'Sales Orders.rdl'. 項目已是最新版本。Item is up to date.
    建立已完成 -- 0 個錯誤,0 個警告Build complete -- 0 errors, 0 warnings
    ------ 已經開始部署: 專案: tutorial,組態: 偵錯 ------------ Deploy started: Project: tutorial, Configuration: Debug ------
    正在部署至 http://[伺服器名稱]/reportserverDeploying to http://[server name]/reportserver
    正在部署報表 '/tutorial/Sales Orders'。Deploying report '/tutorial/Sales Orders'.
    部署已完成 -- 0 個錯誤,0 個警告Deploy complete -- 0 errors, 0 warnings
    ========== 建置: 1 個成功或最新狀態,0 個失敗,0 個略過 ==================== Build: 1 succeeded or up-to-date, 0 failed, 0 skipped ==========
    ========== 部署: 1 個成功,0 個失敗,0 個略過 ==================== Deploy: 1 succeeded, 0 failed, 0 skipped ==========

    如果您看見類似下面的錯誤訊息,請確認自己擁有報表伺服器的權限,而且已經以系統管理員權限啟動 SQL Server Data ToolsSQL Server Data ToolsIf you see an error message similar to the following, verify you have permissions on the report server and you have started SQL Server Data ToolsSQL Server Data Tools with administrator privileges.

    「授與使用者 'XXXXXXXX\[您的使用者名稱]' 的權限不足,無法執行此作業」"The permissions granted to user 'XXXXXXXX\[your user name]' are insufficient for performing this operation"

  4. 以系統管理員權限瀏覽至入口網站,例如以滑鼠右鍵按一下 Internet Explorer 的圖示,然後按一下 [以系統管理員​​身分執行​​]。Browse to the web portal with administrator privileges, for example, right-click the icon for Internet Explorer and click Run as administrator.

    瀏覽至 Reporting ServicesReporting Services Web入口網站 URL。Browse to Reporting ServicesReporting Services web portal URL.
    注意︰入口網站 URL 是 "Reports",而非 "Reportserver" 的報表伺服器 URL。Note: The portal URL is "Reports", not the Report Server URL of "Reportserver". 例如:For example:

    • http://<server name>/reportshttp://<server name>/reports.
      • http://localhost/reports 適用於在報表伺服器上設計報表時。http://localhost/reports works if your designing the report on the report server.
  5. 瀏覽至包含報表的資料夾。Browse to the folder that contains the report. 預設名稱是 tutorial,即專案名稱或輸入專案屬性中 TargetReportFolder 欄位的名稱。The default name is tutorial, the name of the project or the name you typed into the TargetReportFolder fiedl in the project properties.
    按一下 Sales Orders 報表的名稱,以在瀏覽器中檢視轉譯的報表。Click the name of the report Sales Orders to view the rendered report in the browser.

    ssrs_tutorial_tutorialfolder

** 您已成功完成「建立基本資料表報表」教學課程。 You have successfully completed the Creating a Basic Table Report tutorial.**

另請參閱See Also

篩選、分組和排序資料 (報表產生器及 SSRS)Filter, Group, and Sort Data (Report Builder and SSRS)