使用 SQL Server Analysis Services 資料建立 Excel Services 儀表板Create an Excel Services dashboard using SQL Server Analysis Services data

摘要:了解如何建立、 發佈及使用基本銷售儀表板使用 Excel 與 Excel Services in SharePoint Server 2013。Summary: Learn how to create, publish and use a basic sales dashboard by using Excel and Excel Services in SharePoint Server 2013.

本文逐步說明如何使用 Excel 2016 建立基本銷售儀表板包含數個報告及使用外部資料連線的篩選器。本文所述的範例儀表板的格式類似於下列影像:This article describes, step by step, how to use Excel 2016 to create a basic sales dashboard that contains several reports and a filter using an external data connection. The example dashboard described in this article resembles the following image:

圖: 基本銷售儀表板範例Figure: Basic sales dashboard example

使用 Adventure Works 資料的範例儀表板

本文也會說明如何將儀表板發佈至 SharePoint Server 2013 其他人可以檢視和使用它的所在位置。遵循本文中的步驟,您將了解如何建立並排列在工作表中的不同報告,並將篩選連線至這些報告。This article also describes how to publish the dashboard to SharePoint Server 2013 where others can view and use it. By following the steps in this article, you'll learn how to create and arrange different reports in a worksheet and connect a filter to those reports.

開始之前Before you begin

開始這項作業之前,請先檢閱下列先決條件的相關資訊:Before you begin this operation, review the following information about prerequisites:

規劃儀表板Plan the dashboard

開始建立儀表板之前,建議您建立儀表板計劃。此計劃不需要過於廣泛或複雜。但是,應針對儀表板需包含的項目提供建議。為了協助您準備儀表板計劃,請考慮類似如下的問題:Before you begin to create a dashboard, we recommend that you create a dashboard plan. The plan does not have to be extensive or complex. However, it should give you an idea of what you want to include in the dashboard. To help you prepare a dashboard plan, consider questions such as the following:

  • 誰會使用儀表板?Who will use the dashboard?

  • 他們想查看哪類資訊?What kinds of information do they want to see?

  • 是否存在可用來建立儀表板的資料?Does data exist that you can use to create the dashboard?

是您可以使用以了解如何建立及發佈 Excel Services 儀表板原型而設計範例儀表板。若要顯示我們可能會建立類似的儀表板的儀表板計畫的方式,請參閱下表。Our example dashboard is designed to be a prototype that you can use to learn how to create and publish Excel Services dashboards. To show how we might create a dashboard plan for a similar dashboard, see the following table.

表: 範例儀表板的基本計畫Table: Basic plan for our example dashboard

問題Question 回應Response
誰會使用儀表板?Who will use the dashboard?
儀表板的適用對象是對虛構公司 Adventure Works Cycles 之銷售資訊感興趣的業務代表、業務經理、公司主管及其他專案關係人。The dashboard is intended for use by sales representatives, sales managers, corporate executives, and other stakeholders who are interested in sales information for the fictitious company Adventure Works Cycles.
如何使用儀表板?換句話說,儀表板使用者想查看哪類資訊?How will the dashboard be used? That is, what kinds of information do the dashboard consumers want to see?
銷售代表、經理、行政人員及其他儀表板使用者想使用儀表板檢視、探索及分析資料。儀表板使用者至少想查看下列資訊類型:Sales representatives, managers, executives, and other dashboard consumers want to use the dashboard to view, explore, and analyze data. At a minimum, the dashboard consumers want to see the following kinds of information:
不同產品類別的銷售金額Sales amounts for different product categories
不同銷售領域的銷售金額Sales amounts for different sales territories
網際網路和經銷商通路的銷售量Sales for Internet and Reseller channels
儀表板使用者想使用儀表板檢視、探索及分析資料,以取得特定問題的答案。Dashboard consumers want to use the dashboard to view, explore, and analyze data to obtain answers to specific questions.
儀表板使用者也想能夠使用篩選,以專注於更特定的資訊,例如特定期間的銷售量。The dashboard consumers also want to be able to use filters to focus on more specific information, such as sales for a particular period of time.
是否存在可用來建立儀表板的資料?Does data exist that we can use to create the dashboard?
是。AdventureWorks 範例資料庫包含要用於儀表板上的資料。此範例資料由於是多維度資料 Cube,因此可讓我們建立互動式報表,供儀表板使用者檢視不同層級的詳細資料來探索資料。Yes. The Adventure Works sample database contains the data that we want to use for the dashboard. Because this sample data is a multidimensional data cube, it will enable us to create interactive reports that dashboard users can use to explore data by viewing different levels of detail.
儀表板應該包含哪些項目?What items should the dashboard contain?
範例儀表板包含下列項目:Our example dashboard includes the following items:
SQL Server Analysis Services 的資料連線A data connection to SQL Server Analysis Services
顯示不同產品類別之產品銷售資訊的報表A report showing product sales information for different product categories
顯示不同銷售領域之銷售資訊的報表A report showing sales information for different sales territories
顯示不同銷售通路之訂單和銷售資訊的報表A report showing orders and sales information for different sales channels
顯示跨不同產品類別之訂單和銷售量的報表A report showing orders and sales across different product categories
儀表板使用者可用來檢視特定期間或時間範圍之資訊的篩選A filter that dashboard consumers can use to view information for a particular period of time or a range of time

建立儀表板計劃之後,即可開始建立儀表板。Now that we have created our dashboard plan, we can begin to create the dashboard.

建立儀表板Create the dashboard

若要建立儀表板,請先建立資料連線。然後,我們會使用該資料連線來建立報表與我們想要使用的篩選。之後,我們會將活頁簿發佈至 SharePoint Server 2013。To create the dashboard, we begin by creating a data connection. Then, we use that data connection to create the reports and the filter that we want to use. After that, we publish the workbook to SharePoint Server 2013.

第 1 部分:建立資料連線Part 1: Create a data connection

範例儀表板使用單一資料連線會儲存在 SQL Server 2012 Analysis Services 中的資料。我們將使用此資料連線來建立報表和儀表板篩選。Our example dashboard uses a single data connection to data that is stored in SQL Server 2012 Analysis Services. We'll use this data connection to create the reports and filter for the dashboard.

建立 Analysis Services 資料連線To create a connection to Analysis Services data

  1. 開啟 Microsoft Excel。Open Microsoft Excel.

  2. 選擇 [空白活頁簿]建立活頁簿。Choose Blank workbook to create a workbook.

  3. 在 [資料] 索引標籤上選擇 [取得外部資料] 群組,並選擇 [從其他來源],然後選擇 [從 Analysis ServicesOn the Data tab, choose Get External Data group, choose From Other Sources, and then choose From Analysis Services.

    [資料連線精靈] 隨即開啟。The Data Connection Wizard opens.

  4. 在 [連接至資料庫伺服器] 頁面上 [伺服器名稱] 方塊中,指定您想要使用的 Analysis Services 資料所在的伺服器名稱。On the Connect to Database Server page, in the Server name box, specify the name of the server where the Analysis Services data that you want to use resides.

  5. 在 [登入認證] 區段中,執行下列任一下列步驟:In the Log on credentials section, take one of the following steps:

    • 如果您的組織會使用 Windows 驗證,請選擇 [使用 Windows 驗證],然後選擇 [下一步] 按鈕。If your organization is using Windows Authentication, choose Use Windows Authentication, and then choose the Next button.

    • 如果您的組織使用特定使用者認證,選擇 [使用下列的使用者名稱和密碼、 指定適當的使用者名稱和密碼,,然後選擇 [下一步] 按鈕。If your organization is using specific user credentials, choose Use the following User Name and Password, specify an appropriate user name and password, and then choose the Next button.

      提示

      如果您不知道要選擇哪個選項,請連絡 SharePoint 管理員。If you don't know which option to choose, contact a SharePoint administrator.

  6. 在 [選取資料庫及資料表] 頁面上選擇 [ Adventureworksdw2012multidimensional-ee]資料庫、 選擇 [ Adventure Works ] cube,然後選擇 [下一步] 按鈕。On the Select Database and Table page, choose the AdventureWorksDW2012Multidimensional-EE database, choose the Adventure Works cube, and then choose the Next button.

  7. 在 [儲存資料連線檔案和完成] 頁面上選擇 [完成] 按鈕。On the Save Data Connection File and Finish page, choose the Finish button.

  8. 在 [匯入資料] 頁面上選取 [僅建立連線] 選項,然後選擇[確定]按鈕。On the Import Data page, select the Only Create Connection option, and then choose the OK button.

  9. 保持開啟 Excel。Keep Excel open.

此時我們已 Analysis Services 中建立 [Adventure Works cube 的連線。根據預設,此資料連線的電腦上的文件庫中的「 我的資料來源] 資料夾中儲存及內嵌在活頁簿中。我們將使用儀表板的活頁簿中內嵌的 ODC 連線。At this point, we have created a connection to the Adventure Works cube in Analysis Services. By default, this data connection is saved in a My Data Sources folder in the Documents library on the computer and is embedded in the workbook. We'll use the ODC connection that is embedded in the workbook for the dashboard.

下一步是建立儀表板的報表。The next step is to create reports for the dashboard.

第 2 部分:建立報表Part 2: Create reports

我們將為範例儀表板建立四個報表,如下表所述:For our example dashboard, we'll create four reports, as described in the following table:

表: 儀表板報表Table: Dashboard reports

報告類型Report Type 報表名稱Report Name 描述Description
樞紐分析圖PivotChart report
ProductSalesProductSales
顯示跨不同產品類別之銷售金額的橫條圖報表。Bar chart report that shows sales amounts across different product categories.
樞紐分析圖PivotChart report
GeoSalesGeoSales
顯示跨不同銷售領域之銷售金額的橫條圖報表。Bar chart report that shows sales amounts across different sales territories.
樞紐分析表PivotTable report
ChannelSalesChannelSales
顯示跨網際網路和經銷商通路之訂單數量和銷售金額的表格。Table that shows order quantities and sales amounts across the Internet and Reseller channels.
樞紐分析表PivotTable report
OrderSalesOrderSales
顯示跨不同產品類別之訂單數量和銷售金額的表格。Table that shows order quantities and sales amounts across different product categories.

第一步是建立 ProductSales 報表。We begin by creating the ProductSales report.

若要建立 ProductSales 報表To create the ProductSales report

  1. 在 Excel 中的 [插入] 索引標籤的 [圖表] 區段中,選擇 [樞紐分析圖]。In Excel, on the Insert tab, in the Charts section, choose PivotChart.

    建立樞紐分析圖] 對話方塊隨即出現。The Create PivotChart dialog box appears.

  2. [選擇您想要分析的資料] 區段中選擇 [使用外部資料來源] 選項,然後按 [選擇連線] 按鈕。In the Choose the data that you want to analyze section, choose the Use an external data source option, and then choose the Choose Connection button.

    [現有連線] 對話方塊隨即出現。The Existing Connections dialog box appears.

  3. 在此活頁簿連線] 區段中選取 [ Adventureworksdw2012multidimensional-ee]資料連線,然後按 [開啟] 按鈕。In the Connections in this Workbook section, select the AdventureWorksDW2012Multidimensional-EE data connection, and then choose the Open button.

  4. 在 [建立樞紐分析圖] 對話方塊中,選擇 [現有工作表] 選項,然後選擇[確定]按鈕。In the Create PivotChart dialog box, choose the Existing Worksheet option, and then choose the OK button.

    Chart1開啟供編輯。Chart1 opens for editing.

  5. 在 [樞紐分析圖欄位] 清單中,指定下列選項:In the PivotChart Fields list, specify the following options:

    • 在 [銷售摘要] 區段中,選擇 [銷售金額]。In the Sales Summary section, choose Sales Amount.

      銷售金額會顯示在 [] 區段中,且報表會更新以顯示一個橫條。Sales Amount is displayed in the Values section, and the report updates to display a single bar.

    • 在 [產品] 區段中,選擇 [產品類別]。In the Product section, choose Product Categories.

      產品類別會顯示在 [座標軸] 區段中,且報表會更新以顯示跨不同產品類別的銷售金額。Product Categories is displayed in the Axis section, and the report updates to display sales amounts across different product categories.

  6. 若要以遞減順序排序橫條,請採取下列步驟:To sort the bars in descending order, take the following steps:

  7. 樞紐分析圖欄位] 清單中,按一下 [(或觸碰) [產品類別維度,然後按向下出現的箭號。In the PivotChart Fields list, click (or touch) the Product Categories dimension, and then choose the down arrow that appears.

    選取 [欄位] 對話方塊隨即出現。The Select field dialog box appears.

  8. 選擇 [其他排序選項... ] 以開啟 [排序 (類別) ] 對話方塊。Choose More Sort Options… to open the Sort (Category) dialog box.

  9. [排序選項] 區段中選擇 [依遞減 (Z 到 A) ] 選項,然後使用清單以選取 [銷售金額In the Sort options section, choose the Descending (Z to A) by option, and then use the list to select Sales Amount.

  10. 按一下 [ OK ]。Click OK.

  11. 為了確保有容納更多報表的空間,我們將樞紐分析圖移到更接近工作表左上角的位置。若要執行此動作,請拖曳報表,將左上角對齊工作表儲存格 B10 的左上角。To ensure that there is room for more reports, we'll move the PivotChart report closer to the upper-left corner of the worksheet. To do this, drag the report so that the upper-left corner aligns with the upper-left corner of cell B10 in the worksheet.

  12. 稍後,以避免混淆關於報表名稱我們將指定報表的新名稱。[分析] 索引標籤的 [樞紐分析圖] 群組的 [圖表名稱] 方塊中刪除 [Chart1 的文字、 輸入 ProductSales],並按下 Enter 鍵。To avoid confusion about report names later, we'll specify a new name for the report. On the Analyze tab, in the PivotChart group, in the Chart Name box, delete the text that says Chart1, type ProductSales, and then press the Enter key.

    提示

    確定您指定的名稱僅包含英數字元 (不含空白)。Ensure that the name that you specify contains only alphanumeric characters (no spaces).

  13. 若要確保我們不發生稍後使用報表的大小調整問題,我們將指定報表的大小設定。若要這樣做,請遵循下列步驟:To ensure we don't encounter sizing issues later with the report, we'll specify size settings for the report. To do this, follow these steps:

  14. 在空白] 區段中的報表,例如內報表的右上角,以滑鼠右鍵按一下 (或觸碰並按住第二個),然後選擇 [圖表區格式In an empty section of the report, such as inside the upper-right corner of the report, right-click (or touch and hold for a second), and then choose Format Chart Area.

    [圖表區格式] 清單會隨即開啟。The Format Chart Area list opens.

  15. 圖表選項] 下選擇 [大小及內容] 工具列命令。Below the Chart Options, choose the Size and Properties toolbar command.

  16. 依序展開 [大小] 區段,然後選擇 [鎖定長寬比] 選項。Expand the Size section, and then choose the Lock aspect ratio option.

  17. 依序展開 [屬性] 區段中,並選擇 [不要移動或調整大小儲存格] 選項中,確認已選取 [鎖定] 選項。Expand the Properties section, choose the Don't move or size with cells option, and verify that the Locked option is selected.

  18. 若要選擇性地指定報表的替代文字,請依序展開 [替代文字] 區段中,然後輸入您想要用於報表的文字。To optionally specify alternate text for the report, expand the Alt Text section, and then type the text that you want to use for the report.

  19. 關閉 [圖表區格式] 清單。Close the Format Chart Area list.

  20. 使用 Adventure Works 銷售 」 之類的檔案名稱儲存活頁簿。Save the workbook by using a file name such as Adventure Works Sales.

  21. 保持開啟此活頁簿。Keep the workbook open.

現在我們已經建立樞紐分析圖。下一步是建立另一個樞紐分析圖並命名為 GeoSales,此樞紐分析圖會顯示跨不同地理位置的銷售金額。At this point, we have created a PivotChart report. The next step is to create another PivotChart report and name it GeoSales that shows sales amounts across different geographical locations.

若要建立 GeoSales 報表To create the GeoSales report

  1. 在 Excel 中,用來建立 ProductSales 報表的相同工作表上選擇儲存格 K10。In Excel, on the same worksheet that was used to create the ProductSales report, choose cell K10.

  2. 在 [插入] 索引標籤上選擇 [樞紐分析圖]。On the Insert tab, choose PivotChart.

  3. [選擇您想要分析的資料] 區段中選擇 [使用外部資料來源] 選項,然後按 [選擇連線] 按鈕。In the Choose the data that you want to analyze section, choose the Use an external data source option, and then choose the Choose Connection button.

    [現有連線] 對話方塊隨即出現。The Existing Connections dialog box appears.

  4. 在此活頁簿連線] 區段中選取 [ Adventureworksdw2012multidimensional-ee]資料連線,然後按 [開啟] 按鈕。In the Connections in this Workbook section, select the AdventureWorksDW2012Multidimensional-EE data connection, and then choose the Open button.

  5. 在 [建立樞紐分析圖] 對話方塊中,選擇 [現有工作表] 選項,然後選擇[確定]按鈕。In the Create PivotChart dialog box, choose the Existing Worksheet option, and then choose the OK button.

    [圖表 2隨即開啟供編輯。Chart2 opens for editing.

  6. 移動圖表,將其左上角對齊工作表儲存格 J10 的左上角。Move the chart so that its upper-left corner aligns with the upper-left corner of cell J10 in the worksheet.

  7. 在 [樞紐分析圖欄位] 清單中,指定下列選項:In the PivotChart Fields list, specify the following options:

    • 在 [銷售摘要] 區段中,選擇 [銷售金額]。In the Sales Summary section, choose Sales Amount.

    • [銷售領域] 區段中將 [銷售領域] 拖曳至 [圖例] 區段。In the Sales Territory section, drag Sales Territory to the Legend section.

      報表會隨即更新以顯示指出 [歐洲]、[北美洲] 及 [太平洋地區] 之銷售金額的橫條圖。The report updates to display a bar chart showing sales amounts for Europe, North America, and Pacific.

  8. 請遵循下列步驟,指定報表的大小設定:Specify size settings for the report by following these steps:

  9. 在空白] 區段中的報表、 以滑鼠右鍵按一下 (或觸控和第二個保留),然後選擇 [圖表區格式] 選項。In an empty section of the report, right-click (or touch and hold for a second), and then choose the Format Chart Area option.

    [圖表區格式] 清單會隨即開啟。The Format Chart Area list opens.

  10. 圖表選項] 下選擇 [大小及內容] 工具列命令。Below the Chart Options, choose the Size and Properties toolbar command.

  11. 依序展開 [大小] 區段,然後選擇 [鎖定長寬比] 選項。Expand the Size section, and then choose the Lock aspect ratio option.

  12. 依序展開 [屬性] 區段中,並選擇 [不要移動或調整大小儲存格] 選項中,確認已選取 [鎖定Expand the Properties section, choose the Don't move or size with cells option, and verify that Locked is selected.

  13. 若要選擇性地指定報表的替代文字,請依序展開 [替代文字] 區段中,然後輸入您想要用於報表的文字。To optionally specify alt text for the report, expand the Alt Text section, and then type the text that you want to use for the report.

  14. 關閉 [圖表區格式] 清單。Close the Format Chart Area list.

  15. 指定報表的新名稱。[分析] 索引標籤的 [樞紐分析圖] 群組的 [圖表名稱] 方塊中刪除 [圖表 2] 的文字,輸入 [GeoSales,並按下 Enter 鍵。Specify a new name for the report. On the Analyze tab, in the PivotChart group, in the Chart Name box, delete the text that says Chart2, type GeoSales, and then press the Enter key.

  16. 在 [檔案] 索引標籤上選擇 [儲存] 按鈕。On the File tab, choose the Save button.

  17. 保持開啟此活頁簿。Keep the workbook open.

現在我們已經建立兩個報表。下一步是建立 ChannelSales 報表。At this point, we have created two reports. The next step is to create the ChannelSales report.

若要建立 ChannelSales 報表To create the ChannelSales report

  1. 在 Excel 中,用來建立先前報表的相同工作表上選擇儲存格 B26。In Excel, on the same worksheet that was used to create the previous reports, choose cell B26.

  2. 在 [插入] 索引標籤上選擇 [樞紐分析表]。On the Insert tab, choose PivotTable.

  3. [選擇您想要分析的資料] 區段中選擇 [使用外部資料來源] 選項,然後按 [選擇連線] 按鈕。In the Choose the data that you want to analyze section, choose the Use an external data source option, and then choose the Choose Connection button.

    [現有連線] 對話方塊隨即出現。The Existing Connections dialog box appears.

  4. 在此活頁簿連線] 區段中選取 [ Adventureworksdw2012multidimensional-ee]資料連線,然後按 [開啟] 按鈕。In the Connections in this Workbook section, select the AdventureWorksDW2012Multidimensional-EE data connection, and then choose the Open button.

  5. 選擇 [現有工作表] 選項,然後選擇 [確定] 按鈕。Choose the Existing Worksheet option, and then choose the OK button.

    PivotTable3隨即開啟供編輯。PivotTable3 opens for editing.

  6. 在 [樞紐分析表欄位] 清單中,指定下列選項:In the PivotTable Fields list, specify the following options:

    • 在 [銷售訂單階層] 區段中選擇 [* * 訂單計數 * In the **Sales Orders* section, choose ** Order Count **.

    • 在 [銷售摘要] 區段中,選擇 [銷售金額]。In the Sales Summary section, choose Sales Amount.

    • 在 [銷售通路] 區段中,選擇 [銷售通路]。In the Sales Channel section, choose Sales Channel.

      報表會隨即更新以顯示指出網際網路和經銷商通路之訂單計數和銷售金額的表格。The report updates to display a table showing order counts and sales amounts for the Internet and Reseller channels.

  7. 選擇儲存格 B26,然後在 [公式] 列中刪除 [列標籤的文字和輸入通道銷售。按下 Enter 鍵。Choose cell B26, and then, in the Formula bar, delete the text that says Row Labels, and type Channel Sales. Then press the Enter key.

  8. 指定報表的新名稱。[分析] 索引標籤的 [樞紐分析表] 群組的 [樞紐分析表名稱] 方塊中刪除 [PivotTable3 的文字、 輸入 ChannelSales、,然後按下 Enter 鍵。Specify a new name for the report. On the Analyze tab, in the PivotTable group, in the PivotTable Name box, delete the text that says PivotTable3, type ChannelSales, and then press the Enter key.

  9. 在 [檔案] 索引標籤上選擇 [儲存] 按鈕。On the File tab, choose the Save button.

  10. 保持開啟 Excel 活頁簿。Keep the Excel workbook open.

現在我們已經建立使用相同資料來源的三個報表。下一步是建立 OrderSales 報表。At this point, we have created three reports using the same data source. The next step is to create the OrderSales report.

若要建立 OrderSales 報表To create the OrderSales report

  1. 在 Excel 中,用來建立先前報表的相同工作表上選擇儲存格 H26。In Excel, on the same worksheet that was used to create the previous reports, choose cell H26.

  2. 在 [插入] 索引標籤上選擇 [樞紐分析表]。On the Insert tab, choose PivotTable.

  3. [選擇您想要分析的資料] 區段中選擇 [使用外部資料來源] 選項,然後按 [選擇連線] 按鈕。In the Choose the data that you want to analyze section, choose the Use an external data source option, and then choose the Choose Connection button.

    [現有連線] 對話方塊隨即出現。The Existing Connections dialog box appears.

  4. 在此活頁簿連線] 區段中選取 [ Adventureworksdw2012multidimensional-ee]資料連線,然後按 [開啟] 按鈕。In the Connections in this Workbook section, select the AdventureWorksDW2012Multidimensional-EE data connection, and then choose the Open button.

  5. 選擇 [現有工作表] 選項,然後選擇 [確定] 按鈕。Choose the Existing Worksheet option, and then choose the OK button.

    PivotTable4隨即開啟供編輯。PivotTable4 opens for editing.

  6. 在 [樞紐分析表欄位] 清單中,指定下列選項:In the PivotTable Fields list, specify the following options:

    • 在 [銷售訂單階層] 區段中選擇 [* * 訂單計數 * In the **Sales Orders* section, choose ** Order Count **.

    • 在 [銷售摘要] 區段中,選擇 [銷售金額]。In the Sales Summary section, choose Sales Amount.

    • 在 [產品] 區段中,選擇 [產品類別]。In the Product section, choose Product Categories.

      報表會隨即更新以顯示指出不同產品類別之訂單計數和銷售金額的表格。The report updates to display a table showing order counts and sales amounts for different product categories.

  7. 選擇儲存格 H26,然後在 [公式] 列中刪除 [列標籤] 的預設文字,然後輸入 [產品。按下 Enter 鍵。Choose cell H26, and then, in the Formula bar, delete the default text that says Row Labels, and then type Products. Then press the Enter key.

  8. 指定報表的新名稱。[分析] 索引標籤的 [樞紐分析表] 群組的 [樞紐分析表名稱] 方塊中刪除 [PivotTable4 的文字、 輸入 [OrderSales],然後按下 Enter 鍵。Specify a new name for the report. On the Analyze tab, in the PivotTable group, in the PivotTable Name box, delete the text that says PivotTable4, type OrderSales, and then press the Enter key.

  9. 在 [檔案] 索引標籤上選擇 [儲存] 按鈕。On the File tab, choose the Save button.

  10. 保持開啟此活頁簿。Keep the workbook open.

現在我們已經為基本儀表板建立四個報表。下一步是建立篩選。At this point, we have created our four reports for our basic dashboard. The next step is to create a filter.

第 3 部分:建立篩選Part 3: Create a filter

使用 Excel、 數種不同類型的我們可以建立的篩選。例如,我們可以建立簡單的篩選器將欄位放到 [篩選] 區段中的 [欄位] 清單中。我們可以開始建立交叉分析篩選器,或如果我們使用 Analysis Services 之類的多維度資料來源,我們可以建立時間表控制項。此範例儀表板中,我們將建立時間表控制項。此篩選將會啟用以檢視特定時間資訊的人員。Using Excel, there are several different kinds of filters we can create. For example, we can create a simple filter by putting a field in the Filter section of the Fields list. We can create a slicer, or, if we are using a multidimensional data source such as Analysis Services, we can create a timeline control. For this example dashboard, we'll create a timeline control. This filter will enable people to view information for a particular time.

若要建立時間表控制項To create a timeline control

  1. 在 Excel 中,用來建立報表的相同工作表上選擇儲存格 B1。In Excel, on the same worksheet that was used to create the reports, choose cell B1.

  2. 在 [插入] 索引標籤上的 [篩選] 群組中選擇 [時間表]。On the Insert tab, in the Filter group, choose Timeline.

    [現有連線] 對話方塊隨即顯示。The Existing Connections dialog box appears.

  3. 在此活頁簿連線] 區段中選取 [ Adventureworksdw2012multidimensional-ee]資料連線,然後按 [開啟] 按鈕。In the Connections in this Workbook section, select the AdventureWorksDW2012Multidimensional-EE data connection, and then choose the Open button.

  4. [插入時間表] 對話方塊隨即出現。The Insert Timelines dialog box appears.

  5. 選擇 [日期] 選項,然後選擇 [確定] 按鈕。Choose the Date option, and then choose the OK button.

    時間表控制項會隨即開啟。A timeline control opens.

  6. 移動時間表控制項,將其左上角對齊儲存格 B1 的左上角。Move the timeline control so that its upper-left corner aligns with the upper-left corner of cell B1.

  7. 若要讓時間表控制項變得更寬,請使用調整大小控點,然後將控制項右邊的縮放控點拖曳至欄 M。To make the timeline control wider, use the resizing handles, and drag the sizing handle on the right side of the control to column M.

  8. 選取時間表控制項,然後在 [選項] 索引標籤上 [時間表] 群組中選擇 [報表連線] 工具列命令。Select the timeline control, and then, on the Options tab, in the Timeline group, choose the Report Connections toolbar command.

    [報表連線] 對話方塊隨即出現。The Report Connections dialog box appears.

  9. 選擇 [ ChannelSalesGeoSales[OrderSalesProductSales,,然後選擇 [確定] 按鈕。Choose ChannelSales, GeoSales, OrderSales, and ProductSales, and then choose the OK button.

  10. 在 [檔案] 索引標籤上選擇 [儲存] 按鈕。On the File tab, choose the Save button.

  11. 保持開啟 Excel 活頁簿。Keep the Excel workbook open.

此時我們已經建立儀表板。下一步是將它發佈至 SharePoint Server 2013,其中它可供其他人。At this point, we have created a dashboard. The next step is to publish it to SharePoint Server 2013, where it can be used by others.

發佈儀表板Publish the dashboard

活頁簿發佈至 SharePoint Server 2013,我們將遵循三四個步驟的程序。首先,我們進行活頁簿的顯示方式會影響某些調整。然後,我們指定我們使用外部資料連線的 Excel Services 資料驗證設定。接下來,我們指定發佈活頁簿的選項。最後,我們將該活頁簿發佈至 SharePoint Server 2013。To publish the workbook to SharePoint Server 2013, we'll follow a four-step process. First, we make some adjustments that affect how the workbook is displayed. Then, we specify Excel Services data authentication settings for the external data connection that we use. Next, we specify publish options for the workbook. Finally, we publish the workbook to SharePoint Server 2013.

請先進行調整的活頁簿。根據預設,範例儀表板會包含儀表板的工作表上顯示格線。此外,根據預設,工作表會呼叫 Sheet1。我們可以進行儀表板顯示的方式將可改善一些次要調整。We begin by making adjustments to the workbook. By default, our example dashboard displays gridlines on the worksheet that contains our dashboard. In addition, by default, the worksheet is called Sheet1. We can make some minor adjustments that will improve how the dashboard will be displayed.

若要改善活頁簿的次要刻度的顯示方式To make minor display improvements to the workbook

  1. 在 Excel 中,選擇 [檢視] 索引標籤。In Excel, choose the View tab.

  2. 若要從檢視中,[檢視] 索引標籤的 [顯示] 群組中移除格線清除 [格線] 核取方塊。To remove gridlines from the view, on the View tab, in the Show group, clear the Gridlines check box.

  3. 若要移除列名和欄名,在 [檢視] 索引標籤上的 [顯示] 群組中,清除 [標題] 核取方塊。To remove row and column headings, on the View tab, in the Show group, clear the Headings check box.

  4. 若要重新命名工作表、 其用來指出Sheet1中,其] 索引標籤上按一下滑鼠右鍵,然後選擇重新命名。立即輸入 [SalesDashboard,例如於工作表的新名稱,然後按下 Enter 鍵。To rename the worksheet, right-click its tab where it says Sheet1, and then choose Rename. Immediately type a new name for the worksheet, such as SalesDashboard, and then press the Enter key.

  5. 在 [檔案] 索引標籤上選擇 [儲存]。On the File tab, choose Save.

  6. 保持開啟此活頁簿。Keep the workbook open.

我們建立的活頁簿會使用我們想要我們發佈活頁簿時保持作用的外部資料連線。若要使資料重新整理確保資料連線 live 支援的 Excel services 中,我們必須指定的驗證設定。The workbook we created uses an external data connection that we want to keep active when we publish the workbook. To ensure that the data connection remains live so that data refresh is supported in Excel Services, we must specify authentication settings.

若要指定外部資料連線的驗證設定To specify authentication settings for the external data connection

  1. 在 Excel 中,在 [資料] 索引標籤上選擇 [連線] 工具列命令。In Excel, on the Data tab, choose the Connections toolbar command.

    [活頁簿連線] 對話方塊出現並顯示 [ Adventureworksdw2012multidimensional-ee]資料連線。The Workbook Connections dialog box appears and displays the AdventureWorksDW2012Multidimensional-EE data connection.

  2. 選擇 [內容] 按鈕。Choose the Properties button.

  3. 在 [連線內容] 對話方塊的 [定義] 索引標籤的Excel Services] 旁選擇 [驗證設定... ] 按鈕。In the Connection Properties dialog box, on the Definition tab, next to Excel Services, choose the Authentication Settings… button.

  4. 在 [ Excel Services 驗證設定] 對話方塊中,執行下列任一下列步驟:In the Excel Services Authentication Settings dialog box, take one of the following steps:

    • 如果 Excel Services 設定為使用 Windows 驗證或 EffectiveUserName 功能,選取 [使用驗證的使用者帳戶],然後選擇 [確定] 按鈕。If Excel Services is configured to use Windows Authentication or the EffectiveUserName feature, select Use the authenticated user's account, and then choose the OK button.

    • 如果 Excel Services 設定為使用 Secure Store Service,選取 [使用儲存的帳戶。在 [應用程式識別碼] 方塊中指定安全認證儲存目標應用程式識別碼,然後選擇[確定]按鈕。If Excel Services is configured to use Secure Store Service, select Use a stored account. In the Application ID box, specify the Secure Store target application ID, and then choose the OK button.

    • 若要使用自動的服務帳戶設定 Excel Services、 選取 [],然後選擇 [[確定] 按鈕。If Excel Services is configured to use the unattended service account, select None, and then choose the OK button.

      重要

      如果您不知道要選擇哪個選項,請連絡 SharePoint 管理員。If you do not know which option to choose, contact a SharePoint administrator.

  5. 選擇 [確定] 按鈕關閉 [連線內容] 對話方塊。Choose the OK button to close the Connection Properties dialog box.

  6. 如果您看到訊息指出活頁簿中的連線將不再相同外部檔案中定義的連線,選擇 []。If you see a message that states that the connection in the workbook will no longer be identical to the connection that is defined in the external file, choose Yes.

  7. 選擇 [關閉] 按鈕關閉 [活頁簿連線] 對話方塊。Choose the Close button to close the Workbook Connections dialog box.

我們建立儀表板的報表時,我們已授與之每個唯一的名稱,並定義為命名項目在 Excel 中。除了將活頁簿發佈至 SharePoint Server 2013,我們應該發佈我們所定義的命名項目。這會使您可以稍後顯示其本身的 SharePoint 網頁組件中的命名項目。我們藉由指定的活頁簿發佈選項來進行。When we created the reports for the dashboard, we gave each one a unique name and defined it as a named item in Excel. In addition to publishing the workbook to SharePoint Server 2013, we should publish the named items that we defined. This makes it possible to display a named item in its own SharePoint Web Part later. We do this by specifying publish options for the workbook.

提示

本文不會說明如何在各自的 SharePoint 網頁組件中顯示命名項目。因此,以下是選用程序。不過最佳作法是建議您執行下列程序。如此一來,稍後就不需要重新發佈活頁簿。This article does not describe how to display a named item in its own SharePoint Web Part. Therefore, the following procedure is optional. However, we recommend that, as a best practice, you perform the following procedure. This can save you from having to republish the workbook later.

若要指定活頁簿的發佈選項To specify publish options for the workbook

  1. 在 [檔案] 索引標籤上選擇 [ Info、,,然後選擇 [瀏覽器檢視選項On the File tab, choose Info, and then choose Browser View Options.

  2. 選擇 [瀏覽器檢視選項] 按鈕。Choose the Browser View Options button.

  3. 在 [顯示] 索引標籤上使用清單選擇 [活頁簿中的項目On the Show tab, use the list to choose Items in the Workbook.

  4. 選擇 [所有圖表] 和 [所有樞紐分析表、,然後選擇 [確定] 按鈕。Choose All Charts and All PivotTables, and then choose the OK button.

  5. 在 [檔案] 索引標籤上選擇 [儲存] 按鈕。On the File tab, choose the Save button.

  6. 保持開啟此活頁簿。Keep the workbook open.

下一步是將活頁簿發佈至 SharePoint Server 2013。The next step is to publish the workbook to SharePoint Server 2013.

活頁簿發佈至 SharePoint ServerTo publish the workbook to SharePoint Server

  1. 在 Excel 中,在 [檔案] 索引標籤上選擇 [另存新檔、 [電腦],然後選擇 [瀏覽] 按鈕。In Excel, on the File tab, choose Save As, choose Computer, and then choose the Browse button.

    另存新檔] 對話方塊隨即出現。The Save As dialog box appears.

  2. 在位址行中,輸入 Excel Services 信任的檔案位置之 SharePoint 位址。In the address line, type the SharePoint address to an Excel Services trusted file location.

  3. 選擇 [儲存] 按鈕。Choose the Save button.

    活頁簿會隨即在您指定的 SharePoint 文件庫中發佈。The workbook is published in the SharePoint library that you specified.

  4. 關閉 Excel 活頁簿。Close the Excel workbook.

建立及發佈儀表板之後,即可使用此儀表板探索資料。Now that we have created and published the dashboard, we can use it to explore data.

使用儀表板Use the dashboard

儀表板發佈至 SharePoint Server 2013 之後,它是可供其他人檢視並使用它。After the dashboard was published to SharePoint Server 2013, it is available for people to view and use it.

若要開啟 [儀表板To open the dashboard

  1. 開啟網頁瀏覽器。Open a web browser.

  2. 在網址列中,輸入發佈儀表板之商務智慧中心網站的網址。In the address bar, type the address to the Business Intelligence Center site where the dashboard was published.

  3. 選擇[網站內容],然後再選擇 [文件Choose Site Contents, and then choose Documents.

  4. 選擇 [ Adventure Works 銷售儀表板]。Choose the Adventure Works Sales dashboard.

    儀表板會開啟以供檢視。The dashboard opens for viewing.

此時會開啟儀表板以供檢視,然後即可使用儀表板取得特定問題的答案,如下表所述。Now that the dashboard is open for viewing, we can use it to obtain answers to specific questions, such as those that are described in the following table.

表: 使用儀表板取得特定問題的答案Table: Using the dashboard to obtain answers to specific questions

問題Question 動作Action
哪一個產品類別的訂單數最多?Which product category has the largest number of orders?
使用 OrderSales 報表,檢視不同產品類別的訂單計數] 的欄。您可以看到 [附屬應用程式具有最大的訂單數量。請注意 [附屬應用程式也有所有四種產品類別至少總銷售金額。Using the OrderSales report, view the Order Count column for various product categories. You can see that Accessories has the largest number of orders. Notice also that Accessories also has the least total sales amount of all four product categories.
這間公司透過網際網路通路,還是透過經銷商通路銷售較多商品?Does this company sell more merchandise through the Internet channel, or the Reseller channel?
透過 ChannelSales 報表,您會看到雖然網際網路通路的訂單較多,但是經銷商通路的銷售金額最高。Using the ChannelSales report, you can see that, although the Internet channel has more orders, the Reseller channel has the highest sales amount.
哪一年的總銷售金額最高?Which year resulted in the highest total sales amount?
使用螢幕頂端附近時間表控制項,請選擇月數] 旁的向下箭號並選取。使用控制項選取一年以上一次。請注意年份 2007年具有銷售金額最高。Using the timeline control near the top of the screen, choose the down arrow next to MONTHS and select YEARS. Use the control to select one year at a time. Notice that the year 2007 has the highest sales amount.
這間公司一直以來在哪個銷售領域的銷售金額最高?Which sales territory has the highest sales amount for this company for all time?
時間表控制項的右上角中選取 [清除篩選] 工具列命令,以清除時間表控制項的篩選。請注意 North America 此特定的公司具有銷售金額最高。Clear the filter from the timeline control by selecting the Clear Filter toolbar command in the upper-right corner of the timeline control. Notice that North America has the highest sales amount for this particular company.
哪一個自行車子類別的總銷售量最高?Which subcategory of bikes has the highest overall sales?
使用ProductSales報表,連按兩下 [(或觸碰並按住秒)自行車的列。報表會隨即更新以顯示三個子類別:山區自行車」、 「自行車 」和 「 Gt 自行車。請注意山區自行車子類別具有銷售金額最高。Using the ProductSales report, double-click (or touch and hold for a second) the bar for Bikes. The report updates to display three subcategories: Mountain Bikes, Road Bikes, and Touring Bikes. Notice that the Mountain Bikes subcategory has the highest sales amount.

另請參閱See also

概念Concepts

商務智慧功能的 Excel Service (SharePoint Server 2013)Business intelligence capabilities in Excel Service (SharePoint Server 2013)

建立 Excel Services 儀表板使用資料模型 (SharePoint Server 2013)Create an Excel Services dashboard using a Data Model (SharePoint Server 2013)