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

摘要:了解如何建立、 發佈及使用基本 Excel Services 儀表板使用 Excel 中建立資料模型。Summary: Learn how to create, publish and use a basic Excel Services dashboard using a Data Model that is created in Excel.

本文說明、 逐步、 如何建立資料模型、 如何建立某些報表及交叉分析篩選器,及如何將儀表板發佈至 SharePoint Server 2013。本文所述的範例儀表板的格式類似於下列影像:This article describes, step by step, how to create a Data Model, how to create some reports and a slicer, and then how to publish the dashboard to SharePoint Server 2013. The example dashboard described in this article resembles the following image:

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

使用資料模型建立的範例儀表板

本文內容也涵蓋您可在發佈活頁簿時可以使用的不同顯示選項。依照本文中的步驟進行,您將了解如何以工作表建立和設定報表,然後將交叉分析篩選器連線至那些報表。This article also covers different display options that you can use when you publish a workbook. By following the steps in this article, you'll learn how to create and configure reports in a worksheet and connect a slicer 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 across different sales territories at the country level
網際網路和轉售商銷售通路的產品訂單數量Order quantities for products in the Internet and reseller sales channels
公司所舉辦各種促銷的訂單數量和銷售金額Order quantities and sales amounts for various promotions the company held
儀表板使用者想使用儀表板檢視、探索及分析資料,以取得特定問題的答案。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 a filter to focus on more specific information, such as how many orders and how much sales were generated from different promotions.
是否存在可用來建立儀表板的資料?Does data exist that we can use to create the dashboard?
Adventure Works 範例資料庫包含我們想要用於儀表板的資料。範例資料庫包含大量的資料表。我們可以輕鬆讓我們做為單一資料來源中使用多個資料表建立報告的 Excel 中建立資料模型。這可讓我們建立儀表板使用者可用來探索資料檢視的詳細資料的不同層級的互動式報表。The Adventure Works sample database contains the data that we want to use for the dashboard. The sample database contains lots of tables. We can easily create a Data Model in Excel that will enable us to create reports using multiple tables as a single data source. This 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 中的數個資料表的資料模型。A Data Model that includes several tables that are stored in SQL Server.
顯示各國產品銷售資訊的報表A report showing product sales information across different countries
顯示不同產品訂單與銷售資訊的報表A report showing orders and sales information for different products
儀表板使用者可用以檢視特定促銷資訊的篩選A filter that dashboard consumers can use to view information for a particular promotion

建立儀表板計畫之後,即可開始建立儀表板。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 the Data Model. Then, we use that Data Model 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 Model

範例儀表板使用資料模型包含五個儲存在 SQL Server 中的表格。Our example dashboard uses a Data Model that consists of five tables that are stored in SQL Server.

若要建立資料模型To create a Data Model

  1. 開啟 Excel。Open Excel.

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

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

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

  4. 資料連線精靈] 的 [伺服器名稱] 方塊中,指定儲存 Adventure Works 範例資料集的名稱。In the Data Connection Wizard, in the Server name box, specify the name of the server where the Adventure Works sample dataset is stored.

  5. 在 [選取資料庫及資料表] 對話方塊中選取包含您想要將資料的資料庫] 清單中,選擇 [ AdventureWorksDW2012]。In the Select Database and Table dialog box, in the Select the database that contains the data that you want list, choose AdventureWorksDW2012.

  6. 選取連接至特定的表格啟用的多個資料表] 區段,然後再選取 [下列表格:Select both Connect to a specific table and Enable section of multiple tables, and then select the following tables:

    • DimProductDimProduct

    • DimPromotionDimPromotion

    • DimSalesTerritoryDimSalesTerritory

    • FactInternetSalesFactInternetSales

    • FactResellerSalesFactResellerSales

  7. 確認該已選取 [匯入資料表之間的關係,然後按一下 [下一步Verify that Import relationships between tables is selected, and then click Next.

  8. 儲存連線和完成] 對話方塊的 [ Excel Services] 旁按一下 [驗證設定]。In the Save Connection and Finish dialog box, next to Excel Services, click Authentication Settings.

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

    • 如果 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.

  10. 請不要按一下完成尚未。在 [儲存資料連線檔案和完成] 對話方塊中,按一下 [瀏覽]。Do not click Finish yet. In the Save Data Connection File and Finish dialog box, click Browse.

  11. 輸入您要使用的商務智慧中心網站的位址。位址通常採用表單http://servername/sites/bicenter。Type the address of the Business Intelligence Center site that you are using. The address typically takes the form http://servername/sites/bicenter.

  12. 按兩下 [資料連線] 和 [儲存Double-click Data Connections, and then click Save.

  13. 按一下 [完成]。資料連線] 對話方塊隨即出現。Click Finish. A Data Connections dialog box appears.

  14. 在 [資料連線] 對話方塊中,執行下列步驟:In the Data Connections dialog box, take the following steps:

  15. 在 [標題] 方塊中指定的標題,例如 Adventure Works 資料模型。In the Title box, specify a title, such as Adventure Works Data Model.

  16. 在 [描述] 方塊中指定之資料模型的描述。In the Description box, specify a description for the Data Model.

  17. 在 [關鍵字] 方塊中,指定一些單字和片語,例如 [ Adventure Works ] 及 [資料模型In the Keywords box, specify some words and phrases, such as Adventure Works and Data Model.

  18. 按一下[確定]以關閉 [資料連線] 對話方塊。Click OK to close the Data Connections dialog box.

  19. 在 [匯入資料] 對話方塊中,選擇 [只建立連線。請確定已選取新增此資料至資料模型,並再按一下 [確定]。在資料模型建立及儲存資料連線程式庫中的商務智慧中心網站中。On the Import Data dialog box, choose Only Create Connection. Make sure the Add this data to the Data Model is selected, and then click OK. The Data Model is created and saved in the Data Connections library in the Business Intelligence Center site.

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

此時我們已使用 Excel 建立和上傳 Adventure Works 資料模型使用五儲存在 SQL Server 中的資料表。At this point, we have used Excel to create and upload an Adventure Works Data Model that uses five tables that are stored in SQL Server.

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

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

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

表: 儀表板報表Table: Dashboard reports

報告類型Report Type 報表名稱Report Name 描述Description
樞紐分析圖PivotChart report
營業區銷售狀況Territory Sales
顯示各國銷售金額的長條圖報表。Bar chart report that shows sales amounts across different countries.
樞紐分析表PivotTable report
產品訂單和銷售Product Orders and Sales
顯示各網際網路和轉售商通路之訂單數量和產品銷售金額的資料表。Table that shows order quantities and sales amounts for products across the Internet and Reseller channels.

第一步是建立營業區銷售報表。We begin by creating the Territory Sales report.

若要建立營業區銷售報表To create the Territory Sales report

  1. 在 Excel 中,在 [插入] 索引標籤按一下 [樞紐分析圖In Excel, on the Insert tab, click PivotChart.

  2. 在 [建立樞紐分析圖] 對話方塊中,選取 [使用外部資料來源] 和 [選擇連線In the Create PivotChart dialog box, select Use an external data source, and then click Choose Connection.

  3. 在 [現有連線] 對話方塊的 [表格] 索引標籤中選取 [活頁簿資料模型中的表格] 和 [開啟In the Existing Connections dialog box, on the Tables tab, select Tables in Workbook Data Model, and then click Open.

  4. 建立樞紐分析圖] 對話方塊中,選擇 [現有工作表中,並再按一下 [確定]圖表 1隨即開啟供編輯。在 [樞紐分析圖欄位] 區段中,會發現您選取的資料模型的五個表格所列。In the Create PivotChart dialog box, choose Existing Worksheet, and then click OK. Chart 1 opens for editing. In the PivotChart Fields section, notice the five tables that you selected for the Data Model are listed.

  5. 依序展開 [ DimSalesTerritory,並再選取 [ salesterritorycountry]Expand DimSalesTerritory, and then select SalesTerritoryCountry.

  6. 依序展開 [ FactInternetSales,並再選取 [ salesamount]。圖表更新,以顯示各國銷售金額。Expand FactInternetSales, and then select SalesAmount. The chart updates to show sales amounts across different countries.

  7. 請注意圖表圖例指出總計,這不一定是有用的資訊。若要變更,請遵循下列步驟:Notice that the chart legend says Total, which is not necessarily useful information. To change that, follow these steps:

  8. 在 [樞紐分析圖欄位] 區段的好,[] 中按一下 [ SalesAmount 總和],和 [值欄位設定In the PivotChart Fields section, in the Values field well, click Sum of SalesAmount, and then click Value Field Settings.

  9. 在 [值欄位設定] 對話方塊的 [自訂名稱] 方塊中輸入網際網路銷售],然後按一下 [確定]。圖表似乎不此時已變更但您會看到所做的變更之後完成下一個步驟。In the Value Field Settings dialog box, in the Custom Name box, type Internet Sales, and then click OK. The chart does not seem to have changed at this point, but you will see the changes after you complete the next step.

  10. 樞紐分析圖欄位] 區段中,展開FactResellerSales,並再選取 [ salesamount]。圖表更新,顯示兩組代表各國銷售金額的橫條。In the PivotChart Fields section, expand FactResellerSales, and then select SalesAmount. The chart updates to show two sets of bars representing sales amounts across different countries.

  11. 若要變更圖表圖例中轉售商通路銷售金額資訊的表示方式,請執行下列步驟:To change how the sales amount information for the reseller channel is represented in the chart legend, follow these steps:

  12. 在 [樞紐分析圖欄位] 區段的好,[] 中按一下 [ SalesAmount 總和],和 [值欄位設定In the PivotChart Fields section, in the Values field well, click Sum of SalesAmount, and then click Value Field Settings.

  13. 在 [值欄位設定] 對話方塊的 [自訂名稱] 方塊中輸入轉售商銷售],然後按一下 [確定]In the Value Field Settings dialog box, in the Custom Name box, type Reseller Sales, and then click OK.

    圖表圖例會分別清楚地指出網際網路銷售和轉售商售額的值。The chart legend clearly indicates which values apply to Internet sales and which to reseller sales.

  14. 在例如空白區域,圖表中的某處按一下滑鼠右鍵,然後選擇 [樞紐分析圖選項Somewhere on the chart, such as in a blank area, right-click, and then choose PivotChart Options.

  15. 在 [樞紐分析圖名稱] 方塊中輸入例如營業區銷售] 報表的名稱,然後按一下 [確定]In the PivotChart Name box, type a name for the report, such as Territory Sales, and then click OK.

  16. 若要移動報表,請按一下樞紐分析圖報表中的空白區域,然後拖曳報表,讓報表的左上角對齊工作表儲存格 D1 的左上角。To move the report, click a blank area in the PivotChart report, and then drag report so that its upper-left corner aligns with the upper-left corner of cell D1 in the worksheet.

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

此時我們已建立樞紐分析圖會顯示來自 SQL Server 中的三個資料表的資訊。資料模型中的活頁簿可讓我們建立單一檢視,例如 [營業區銷售] 報告使用這三個表格。At this point, we have created a PivotChart report that displays information that comes from three tables in SQL Server. The Data Model in the workbook enables us to create a single view, such as the Territory Sales report by using those three tables.

下一步是建立產品訂單和銷售樞紐分析表。The next step is to create the Product Orders and Sales PivotTable report.

建立產品訂單和銷售報表To create the Product Orders and Sales report

  1. 在 Excel 中,按一下 [儲存格 D21。然後在 [插入] 索引標籤上按一下 [樞紐分析表In Excel, click cell D21. Then, on the Insert tab, click PivotTable.

  2. 建立樞紐分析表] 對話方塊中,選擇 [使用外部資料來源] 和 [選擇連線On the Create PivotTable dialog box, choose Use an external data source, and then click Choose connection.

  3. 在 [資料表] 索引標籤上選取 [活頁簿資料模型中的表格] 和 [開啟On the Tables tab, select Tables in Workbook Data Model, and then click Open.

  4. 確認現有工作表已選取,然後按一下 [確定]樞紐分析表 2隨即開啟供編輯。Verify that Existing Worksheet is selected, and then click OK. PivotTable 2 opens for editing.

  5. 在 [樞紐分析表欄位] 區段中,依序展開 [ DimProduct],,然後選取ModelName。報表會隨即更新以顯示產品清單。In the PivotTable Fields section, expand DimProduct, and then select ModelName. The report updates to display a list of products.

  6. 若要取代 [列標籤的預設文字,請按一下儲存格 D21,並輸入 「 產品型號。To replace the default text that says Row Labels, click cell D21, and type Product Model.

  7. 樞紐分析表欄位] 區段中,依序展開 [ FactInternetSales,並再選取 [ orderquantity]In the PivotTable Fields section, expand FactInternetSales, and then select OrderQuantity.

  8. 在 [樞紐分析表欄位] 區段的好,[] 中按一下 [ Orderquantity 總和],和 [值欄位設定In the PivotTable Fields section, in the Values field well, click Sum of OrderQuantity, and then click Value Field Settings.

  9. 在 [值欄位設定] 對話方塊的 [自訂名稱] 方塊中輸入 「 網際網路訂單,然後按一下 [確定]In the Value Field Settings dialog box, in the Custom Name box, type Internet Orders, and then click OK.

  10. 樞紐分析表欄位] 區段中,依序展開 [ FactInternetSales,並再選取 [ salesamount]In the PivotTable Fields section, expand FactInternetSales, and then select SalesAmount.

  11. 在 [樞紐分析表欄位] 區段的好,[] 欄位中選取 [ SalesAmount 總和],和 [值欄位設定In the PivotTable Fields section, in the Values field well, select Sum of SalesAmount, and then click Value Field Settings.

  12. 儲存格格式] 對話方塊中 [類別] 窗格中,選擇 [貨幣] 並再按一下 [確定]In the Format Cells dialog box, in the Category pane, choose Currency, and then click OK.

  13. 按一下[確定]以關閉 [值欄位設定] 對話方塊。現在報表會顯示線上售出各種產品的訂單數量和銷售金額。Click OK to close the Value Field Settings dialog box. The report now shows order quantities and sales amounts for various products sold online.

  14. 在 [樞紐分析表欄位] 清單中,[ FactResellerSales ] 區段中選取 [ orderquantity]In the PivotTable Fields list, in the FactResellerSales section, select OrderQuantity.

  15. 在 [樞紐分析表欄位] 區段的好,[] 中按一下 [ Orderquantity 總和],和 [值欄位設定In the PivotTable Fields section, in the Values field well, click Sum of OrderQuantity, and then click Value Field Settings.

  16. 在 [值欄位設定] 對話方塊的 [自訂名稱] 欄位中輸入 「 轉售商訂單 」,並再按一下 [確定]In the Value Field Settings dialog box, in the Custom Name field, type Reseller Orders, and then click OK.

  17. 在 [樞紐分析表欄位] 區段的 [ FactResellerSales ] 區段中選取 [ salesamount]In the PivotTable Fields section, in the FactResellerSales section, select SalesAmount.

  18. 在 [樞紐分析表欄位] 區段的好,[] 欄位中選取 [ SalesAmount 總和],和 [值欄位設定In the PivotTable Fields section, in the Values field well, select Sum of SalesAmount, and then click Value Field Settings.

  19. 在 [自訂名稱] 方塊中輸入 「 轉售商銷售] 和 [數字格式In the Custom Name box, type Reseller Sales, and then click Number Format.

  20. 儲存格格式] 對話方塊中 [類別] 窗格中,選擇 [貨幣] 並再按一下 [確定]In the Format Cells dialog box, in the Category pane, choose Currency, and then click OK.

  21. 按一下[確定]以關閉 [值欄位設定] 對話方塊。Click OK to close the Value Field Settings dialog box.

    現在報表會顯示在線上和透過轉售商所售出各種產品的訂單數量和銷售金額。The report now shows order quantities and sales amounts for various products sold online and through resellers.

  22. 樞紐分析表報表內的某處按一下滑鼠右鍵,然後選擇 [樞紐分析表選項Somewhere inside the PivotTable report, right-click, and then choose PivotTable Options.

  23. 在 [樞紐分析表名稱] 方塊中輸入等產品訂單和銷售報表的名稱。清除update 自動調整欄寬] 選項,然後再按一下 [確定]In the PivotTable Name box, type a name for the report, such as Product Orders and Sales. Clear the Autofit column widths on update option, and then click OK.

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

我們現在有第二個報表顯示來自 SQL Server 中的三個資料表的資訊。若要使其寬度比對樞紐分析表的寬度調整樞紐分析圖報表拖曳樞紐分析圖報表的右下角使其對齊儲存格 H21 的右下角。Now we have a second report displaying information that comes from three tables in SQL Server. To resize the PivotChart report so that its width matches the width of the PivotTable report, drag the lower-right corner of the PivotChart report so that it aligns with the lower-right corner of cell H21.

下一步是建立儀表板的篩選。The next step is to create a filter for the dashboard.

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

使用 Excel、 數種不同類型的我們可以建立的篩選。例如,我們可以建立簡單的篩選器將欄位放到 [篩選] 區段中的 [欄位] 清單中。我們可以開始建立交叉分析篩選器,或如果我們使用已定義的日期階層的資料來源,我們可以建立時間表控制項。此範例儀表板中,我們將建立交叉分析篩選器。此篩選將會啟用以檢視特定促銷資訊的人員。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 data source that has a date hierarchy defined, we can create a timeline control. For this example dashboard, we'll create a slicer. This filter will enable people to view information for a particular promotion.

若要建立促銷交叉分析篩選器To create the Promotion slicer

  1. 在 Excel 中,按一下 [儲存格 A1。然後,在 [插入] 索引標籤上按一下 [交叉分析篩選器]。In Excel, click cell A1. Then, on the Insert tab, click Slicer.

  2. 在 [現有連線] 對話方塊的 [資料模型] 索引標籤中選取 [此活頁簿中的 [我們已使用的資料模型] 和 [開啟In the Existing Connections dialog box, on the Data Model tab, select the Data Model we've been using in this workbook, and then click Open.

  3. 在 [插入交叉分析篩選器] 對話方塊的 [ DimPromotion ] 區段中選取EnglishPromotionName,並再按一下 [確定]。交叉分析篩選器會出現在工作表。In the Insert Slicers dialog box, in the DimPromotion section, select EnglishPromotionName, and then click OK. The slicer appears on the worksheet.

  4. 移動交叉分析篩選器,將其左上角對齊儲存格 B1 的左上角。Move the slicer so that its upper-left corner aligns with the upper-left corner of cell A1.

  5. 增加交叉分析篩選器的高度,直到看見其中的所有項目。Expand the height of the slicer so that all its items are visible.

  6. 若要變更預設的交叉分析篩選器、 [選項] 索引標籤的 [交叉分析篩選器標題] 方塊中的名稱輸入 [促銷]。To change the default name of the slicer, on the Options tab, in the Slicer Caption box, type Promotion.

現在我們已建立交叉分析篩選器,但尚未將其連線至報表。下一步是將交叉分析篩選器連線至營業區銷售報表與產品訂單和銷售報表。At this point, we have created the slicer but we have not connected it to the reports yet. The next step is to connect the slicer to the Territory Sales and Product Orders and Sales reports.

若要連接至報表促銷交叉分析篩選器To connect the Promotion slicer to the reports

  1. 按一下以選取交叉分析篩選器。然後在 [交叉分析篩選器工具選項] 索引標籤的 [交叉分析篩選器] 群組中按一下 [報表連線]。Click to select the slicer. Then, on the Slicer Tools Options tab, in the Slicer group, click Report Connections.

  2. 在 [回報連線] 對話方塊中,選取 [產品訂單和銷售報告和營業區銷售報表,然後按一下 [確定]。交叉分析篩選器現在已連接至報表。In the Report Connections dialog box, select the Product Orders and Sales report and the Territory Sales report, and then click OK. The slicer is now connected to the reports.

若要測試交叉分析篩選器,請選擇 [項目,例如否折扣。報告的重新整理以顯示銷售和其否折扣輸入類型為促銷的訂單金額。選擇另一個項目中的交叉分析篩選器,例如山區 100 距離。請注意報表會更新以顯示唯一轉售商銷售資訊。這表示該促銷中,所進行任何線上銷售。若要清除交叉分析篩選器,請按一下 [交叉分析篩選器標題旁邊的 [篩選] 工具列命令。報告的重新整理以顯示所有促銷的資訊。To test the slicer, choose an item, such as No Discount. The reports refresh to show sales and order amounts for which No Discount was entered as the promotion type. Choose another item in the slicer, such as Mountain-100 Clearance. Notice that the reports update to show only reseller sales information. This means that for that promotion, no online sales were made. To clear the slicer, click the filter toolbar command next to the title of the slicer. The reports refresh to display information for all promotions.

下一步是儲存活頁簿。The next step is to save the workbook.

若要儲存該活頁簿To save the workbook

  1. 在 Excel 中,在 [檔案] 索引標籤上 [另存新檔] 和 [瀏覽]In Excel, on the File tab, click Save As, and then click Browse.

  2. 在 [檔案名稱] 方塊中輸入 Adventure Works 銷售和訂單 」,例如活頁簿的名稱,然後按一下 [儲存。活頁簿儲存到您的電腦。In the File name box, type a name for the workbook such as Adventure Works Sales and Orders, and then click Save. The workbook is saved to your computer.

此時我們已經建立儀表板。下一步是將它發佈至 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,我們將遵循三個步驟的程序。首先,我們進行活頁簿的顯示方式會影響某些調整。接下來,我們指定我們想要在網頁瀏覽器中顯示活頁簿的方式。最後,我們將該活頁簿發佈至 SharePoint Server 2013。To publish the workbook to SharePoint Server 2013, we'll follow a three-step process. First, we make some adjustments that affect how the workbook is displayed. Next, we specify how we want the workbook to display in a web browser. 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中,其] 索引標籤上按一下滑鼠右鍵,然後選擇重新命名。立即輸入新名稱工作表,例如訂單和銷售],並按下 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 Orders and Sales, and then press the Enter key.

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

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

當您將活頁簿發佈至 SharePoint Server 2013 時,您可以選擇數種顯示瀏覽器檢視選項,如下列清單所述。When you publish a workbook to SharePoint Server 2013, you can choose from several display browser view options, as described in the following list.

  • 工作表檢視]。您可以使它看起來更加 Excel 在瀏覽器視窗中的工作表檢視中顯示的活頁簿。交叉分析篩選器和報表顯示一起在瀏覽器視窗中,就像它們查看 Excel 用戶端。Worksheet view. You can have the workbook displayed in worksheet view in the browser window so that it looks much like it does in Excel. The slicer and reports are displayed together in the browser window, just like they look in the Excel client.

    下列圖像顯示呈現在工作表檢視中的範例儀表板。The following image shows our example dashboard rendered in worksheet view.

    使用資料模型建立的範例儀表板

  • 圖庫檢視。您可以在圖庫檢視中,一次螢幕和沿著畫面左邊的交叉分析篩選器的中心組件中的功能一份報告顯示的活頁簿。Gallery view. You can have the workbook displayed in a gallery view, which features one report at a time in the center part of the screen and a slicer along the left side of the screen.

    下圖顯示在圖庫檢視中呈現的範例儀表板。The following image shows our example dashboard rendered in gallery view.

    圖庫檢視中顯示的範例儀表板

請使用下列其中一項程序指定活頁簿的瀏覽器檢視選項。Use one of the following procedures to specify browser view options for the workbook.

設定活頁簿使用工作表檢視來顯示To configure the workbook to display using worksheet view

  1. 在 Excel 中,在 [檔案] 索引標籤中,按一下 [瀏覽器檢視選項In Excel, on the File tab, click Browser View Options.

  2. 在 [顯示] 索引標籤上使用清單選取 [工作表、 選取 [訂單和銷售] 及 [確定]On the Show tab, use the list to select Sheets, select Orders and Sales, and then click OK

  3. 儲存該活頁簿,並再關閉 Excel。Save the workbook, and then close Excel.

    設定活頁簿使用圖庫檢視來顯示To configure the workbook to display using gallery view

  4. 在 Excel 中,在 [檔案] 索引標籤中,按一下 [瀏覽器檢視選項In Excel, on the File tab, click Browser View Options.

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

  6. 選取 [營業區銷售] 報表及產品訂單和銷售報表,然後按一下 [參數] 索引標籤。Select the Territory Sales report and the Product Orders and Sales reports, and then click the Parameters tab.

  7. 按一下 [新增]、 選取 [促銷交叉分析篩選器,並再按一下 [確定]Click Add, select the Promotion slicer, and then click OK.

  8. 按一下[確定]以關閉瀏覽器檢視選項] 對話方塊。Click OK to close the Browser View Options dialog box.

  9. 然後關閉 Excel。Then close Excel.

下一步是將活頁簿發佈至商務智慧中心網站的文件庫。The next step is to publish the workbook to the Documents library in the Business Intelligence Center site.

若要將活頁簿上傳至文件庫To upload the workbook to a Documents library

  1. 開啟網頁瀏覽器。在位址行中,輸入您要使用的商務智慧中心網站的網站位址 (URL)。位址通常採用表單http://servername/sites/bicenter。Open a web browser. In the address line, type the website address (URL) to the Business Intelligence Center site that you are using. The address typically takes the form http://servername/sites/bicenter.

  2. 按一下 [網站內容],並再按一下 [文件Click Site Contents, and then click Documents.

  3. 按一下 [ + 新增文件以開啟 [新增文件] 對話方塊。Click + new document to open the Add a document dialog box.

  4. 按一下 [瀏覽]以開啟 [選擇要上傳檔案] 對話方塊。Click Browse to open the Choose File to Upload dialog box.

  5. 選取 [ Adventure Works 銷售和訂單的活頁簿,,然後按一下 [開啟Select the Adventure Works Sales and Orders workbook, and then click Open.

  6. 在 [新增文件] 對話方塊中,按一下 [確定]In the Add a document dialog box, click OK.

建立及發佈儀表板之後,即可使用此儀表板探索資料。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 and Orders dashboard. The dashboard opens in a browser window.

此時會開啟儀表板以供檢視,然後即可使用儀表板取得特定問題的答案,如下表所述。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 two countries sold more on the Internet than through resellers?
查看長條圖。請注意,澳洲與德國的網際網路銷售金額均大於轉售商銷售金額。Look at the bar chart. Notice that in Australia and German, Internet sales amounts are greater than reseller sales amounts.
針對 [批發折扣 (41 至 60 個)] 這項促銷,哪個項目的銷售金額最高?For the Volume Discount 41 to 60 promotion, which item had the highest sales amount?
在 [促銷交叉分析篩選器、 選取大量折扣 41 至 60。通知報告更新以顯示轉售商銷售僅限資訊。在樞紐分析表報表中,會發現女性的山區短褲具有銷售金額最高。In the Promotion slicer, select Volume Discount 41 to 60. Notice the reports update to show reseller sales information only. In the PivotTable report, notice that Women's Mountain Shorts has the highest sales amount.
在轉售商通路的所有促銷中,哪項產品的銷售金額最高?What product had the highest sales amounts for the reseller channel across all promotions?
若要回答這個問題,請執行下列步驟。To answer this question, follow these steps.
在 Excel 中開啟活頁簿。請確定交叉分析篩選器會清除使顯示所有促銷的資料。樞紐分析表報表中,按一下 [轉售商銷售] 欄中的列。在 [資料] 索引標籤上按一下 [排序] 以開啟 [排序方式] 值] 對話方塊。[排序選項] 下選取最大到 [最小,然後再按一下 [確定]。請注意樞紐分析表會顯示使用中轉售商通路銷售金額最高山區 200。Open the workbook in Excel. Make sure the slicer is cleared so that data for all promotions is displayed. In the PivotTable report, click a row in the Reseller Sales column. On the Data tab, click Sort to open the Sort by Value dialog box. Under Sort Options, select Largest to Smallest, and then click OK. Notice that the PivotTable report shows Mountain-200 with the highest sales amount in the reseller channel.
是否能將長條圖從銷售金額變更為顯示各國的訂單數量?Is it possible to change the bar chart to display order quantities across different countries instead of sales amounts?
按一下 [開啟樞紐分析圖欄位] 清單中橫條圖。在 [ FactInternetSales ] 區段中選取 [ orderquantity] ,並清除 [ SalesAmount。在 [ FactResellerSales ] 區段中選取 [ orderquantity] ,並清除 [ SalesAmount。若要顯示各國的訂單更新橫條圖。Click the bar chart to open the PivotChart Fields list. In the FactInternetSales section, select OrderQuantity and clear SalesAmount. In the FactResellerSales section, select OrderQuantity and clear SalesAmount. The bar chart updates to display order quantities across the different countries.

另請參閱See also

概念Concepts

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

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