使用 SQL Server Analysis Services 数据创建 Excel Services 仪表板

适用于：2013 2016 2019 Subscription Edition SharePoint in Microsoft 365

本文分步介绍如何使用Excel 2016创建包含多个报表和使用外部数据连接的筛选器的基本销售仪表板。 本文采用的仪表板示例与下图类似：

图：基本销售仪表板示例

本文还介绍如何将仪表板发布到 SharePoint Server 2013，其他人可在其中查看和使用它。 通过按照本文中的步骤操作，您将了解如何在工作表中创建和排列不同的报表，以及将筛选器连接到这些报表。

开始之前

在开始此操作之前，请查看有关先决条件的以下信息：

• Excel 必须安装在用于创建和发布仪表板的计算机上。

• 本方案使用 Adventure Works 示例数据和 SharePoint Server 2013 中的商业智能中心网站。 如果您没有示例数据和商业智能中心网站，请使用为商业智能解决方案配置 AdventureWorks中的说明让 IT 管理员为您配置这些内容。

• 必须在所使用的 SharePoint 环境中配置 Excel Services。

• 您必须了解一些有关用于 Excel Services 的身份验证设置的信息。 可使用 Windows 身份验证对 Kerberos 委派 (安全存储服务) 或 OLAP 数据源（EffectiveUserName 功能）提供用户访问权。

  有关如何配置安全存储的信息，请参阅在 SharePoint Server 中规划安全存储服务和在 SharePoint Server 中配置安全存储服务。 有关如何为 OLAP 数据源配置 EffectiveUserName 功能的信息，请参阅 在 SharePoint Server 中使用 Analysis Services EffectiveUserName。

规划仪表板

在开始创建仪表板之前，建议您先创建一个仪表板计划。 该计划不必庞大或复杂。 但是，它应该让您清楚要在仪表板中包含的内容。 为了帮助您准备仪表板计划，请考虑以下问题：

• 谁将使用此仪表板？

• 他们希望查看哪些类型的信息？

• 是否存在可用于创建仪表板的数据？

我们的示例仪表板是作为原型而设计的，您可以通过它了解如何创建和发布 Excel Services 仪表板。 若要了解我们如何为类似的仪表板创建仪表板计划，请参阅下表。

表：仪表板示例的基本计划

问题	响应
谁将使用此仪表板？	仪表板专供对虚拟公司 Adventure Works Cycles 的销售信息感兴趣的销售代表、销售经理、公司高管和其他利益干系人使用。
将如何使用仪表板？ 也就是说，仪表板使用者希望查看哪些类型的信息？	销售代表、经理、高管和其他仪表板使用者希望使用仪表板查看、研究和分析数据。 仪表板使用者至少希望查看以下类型的信息： 不同产品类别的销售额 不同销售区域的销售额 Internet 和经销商渠道的销售 仪表板使用者希望使用仪表板查看、研究和分析数据，以获得特定问题的答案。 仪表板使用者还希望能够使用筛选器重点关注更具体的信息，如特定时期的销售。
是否存在可用于创建仪表板的数据？	是的。 Adventure Works 示例数据库包含我们要用于仪表板的数据。 由于此示例数据是多维数据集，因此，它可以允许我们创建交互式报表，以便仪表板用户能够通过查看不同级别的详细信息来分析数据。
仪表板应包含哪些项目？	我们的仪表板示例包括以下项目： 与 SQL Server Analysis Services 的数据连接 显示不同产品类别的产品销售信息的报表 显示不同销售区域的销售信息的报表 显示不同销售渠道的订单和销售信息的报表 显示不同产品类别的订单和销售的报表 仪表板使用者可用来查看特定时段或某时间范围内信息的筛选器

现在我们已经创建了仪表板计划，因此可以开始创建仪表板。

创建仪表板

若要创建仪表板，我们首先要创建数据连接。 然后，使用该数据连接创建要使用的报表和筛选器。 之后，我们将工作簿发布到 SharePoint Server 2013。

部分 1：创建数据连接

我们的仪表板示例使用与 SQL Server 2012 Analysis Services 中存储的数据连接的单一数据连接。 我们将使用此数据为仪表板创建报表和筛选器。

创建与 Analysis Services 数据的连接

1. 打开 Microsoft Excel。

2. 若要创建工作簿，请选择“ 空白工作簿”。

3. 在“ 数据 ”选项卡上，选择“ 获取外部数据 ”组，然后选择“ 来自其他源”，然后选择“ 从 Analysis Services”。

   随即打开数据连接向导。

4. 在"连接数据库服务器"页的"服务器名称"框中，指定要使用的 Analysis Services 数据所在服务器的名称。

5. 在"登录凭据"部分，执行以下步骤之一：

• 如果你的组织正在使用 Windows 身份验证，请选择“ 使用 Windows 身份验证”，然后选择“ 下一步”。

• 如果组织正在使用特定的用户凭据，请选择“ 使用以下用户名和密码”，指定适当的用户名和密码，然后选择“ 下一步”。

  提示

  如果不知道要选择哪个选项，请联系 SharePoint 管理员。

6. 在 “选择数据库和表 ”页上，选择 AdventureWorksDW2012Multidimensional-EE 数据库，然后选择 Adventure Works 多维数据集，然后选择“ 下一步”。

7. 在 “保存数据连接文件和完成 ”页上，选择“ 完成”。

8. 在 “导入数据 ”页上，选择“ 仅创建连接” 选项，然后选择“ 确定”。

9. 保持 Excel 的打开状态。

此时，我们已经创建了与 Analysis Services 中 Adventure Works 多维数据集的连接。 默认情况下，此数据连接保存在计算机"文档"库中的"我的数据源"文件夹中，且嵌入工作簿内。 我们将使用嵌入仪表板工作簿的 ODC 连接。

下一步是创建仪表板的报表。

部分 2：创建报表

对于我们的仪表板示例，我们将创建四个报表，如下表所述：

表：仪表板报表

报表类型	报表名称	说明
数据透视图报表	ProductSales	显示不同产品类别销售额的条形图报表。
数据透视图报表	GeoSales	显示不同销售区域销售额的条形图报表。
数据透视表	ChannelSales	显示 Internet 和经销商渠道的订单量和销售额的表。
数据透视表	OrderSales	显示不同产品类别的订单量和销售额的表。

我们首先创建 ProductSales 报表。

创建 ProductSales 报表

1. 在 Excel 中的“ 插入 ”选项卡上的“ 图表 ”部分中，选择“ 数据透视图”。

   此时将显示 “创建数据透视图 ”对话框。

2. 在 “选择要分析的数据 ”部分中，选择“ 使用外部数据源 ”选项，然后选择“ 选择连接”。

   此时会显示“现有Connections”对话框。

3. 在此工作簿部分中的Connections中，选择 AdventureWorksDW2012Multidimensional-EE 数据连接，然后选择“打开”。

4. 在 “创建数据透视图 ”对话框中，选择“ 现有工作表 ”选项，然后选择“ 确定”。

   将打开“图表 1”以供编辑。

5. 在“数据透视图字段”列表中，指定以下选项：

• 在 “销售摘要 ”部分中，选择“ 销售额”。

  “销售额” 显示在 “值 ”部分中，报表将更新为显示单个条形图。

• 在“ 产品 ”部分中，选择“ 产品类别”。

  产品类别 显示在 “轴 ”部分中，报表将更新以显示不同产品类别的销售额。

6. 若要按降序对这些条形图进行排序，请执行以下步骤：

7. 在 “数据透视图字段” 列表中，选择“ 产品类别” 维度，然后选择显示的向下箭头。

   此时将显示 “选择字段 ”对话框。

8. 若要打开“ 排序 (类别) 对话框，请选择” 更多排序选项...“。

9. 在 “排序选项 ”部分中，选择“ 降序 (Z 到 A) 依据 ”选项，然后使用列表选择“ 销售额”。

10. 选择“确定”。

11. 为确保有更多报表空间，我们会移动数据透视图报表，以使其更加靠近工作表的左上角。 为此，请拖动报表以便报表左上角与工作表中单元格 B10 的左上角对齐。

12. 为避免日后混淆报表名称，我们将为该报表指定新名称。 在“ 分析 ”选项卡的“ 数据透视图 ”组中的“ 图表名称 ”框中，删除显示 Chart1 的文本，键入“ProductSales”，然后在键盘上按 <Enter>。

    提示

    确保您指定的名称仅包含字母数字字符（无空格）。

13. 为确保日后不会遇到报表大小问题，我们将指定报表的大小设置。 为此，请按照下列步骤操作：

14. 在报表的空白部分（例如报表右上角）中，右键单击，然后选择“ 设置图表区域格式”。

    将打开“设置图表区格式”列表。

15. 在“ 图表选项”下，选择“ 大小和属性” 工具栏命令。

16. 展开“ 大小 ”部分，然后选择“ 锁定纵横比 ”选项。

17. 展开 “属性” 部分，选择“ 不要移动或调整单元格大小 ”选项，并验证是否已选中 “锁定 ”选项。

18. 若要选择性地为报表指定备用文本，请展开 “替换文字” 部分，然后输入要用于报表的文本。

19. 关闭"设置图表区格式"列表。

20. 通过使用诸如 Adventure Works 销售这样的文件名来保存工作簿。

21. 保持工作簿处于打开状态。

此时，我们已经创建了数据透视图报表。 下一步是创建另一个数据透视图报表并将其命名为 GeoSales，以便显示不同地理位置的销售额。

创建 GeoSales 报表

1. 在 Excel 中，在用于创建 ProductSales 报表的同一工作表上，选择单元格 K10。

2. 在“ 插入 ”选项卡上，选择“ 数据透视图”。

3. 在 “选择要分析的数据 ”部分中，选择“ 使用外部数据源 ”选项，然后选择“ 选择连接”。

   此时会显示“现有Connections”对话框。

4. 在此工作簿部分中的Connections中，选择 AdventureWorksDW2012Multidimensional-EE 数据连接，然后选择“打开”。

5. 在 “创建数据透视图 ”对话框中，选择“ 现有工作表 ”选项，然后选择“ 确定”。

   将打开“Chart2”以供编辑。

6. 移动该图表，使其左上角与工作表中单元格 J10 的左上角对齐。

7. 在“数据透视图字段”列表中，指定以下选项：

• 在 “销售摘要 ”部分中，选择“ 销售额”。

• 在“销售区域”部分，将“销售区域”拖动到“图例”部分。

  报表将更新以显示一个展示欧洲、北美洲和太平洋地区销售额的条形图。

8. 通过以下步骤指定报表的大小设置：

9. 在报表的空白部分中，右键单击，然后选择“ 设置图表区域格式 ”选项。

   将打开“设置图表区格式”列表。

10. 在“ 图表选项”下，选择“ 大小和属性” 工具栏命令。

11. 展开“ 大小 ”部分，然后选择“ 锁定纵横比 ”选项。

12. 展开 “属性” 部分，选择“ 不要移动或调整单元格大小 ”选项，并验证是否已选中 “锁定 ”。

13. 若要选择性地为报表指定替换文字，请展开 “替换文字” 部分，然后输入要用于报表的文本。

14. 关闭“设置图表区格式”列表。

15. 为该报表指定新名称。 在“ 分析 ”选项卡的“ 数据透视图 ”组中，在“ 图表名称 ”框中，删除 Chart2 的文本，键入“GeoSales”，在键盘上按 <Enter>。

16. 在“ 文件 ”选项卡上，选择“ 保存”。

17. 保持工作簿处于打开状态。

此时，我们已经创建了两个报表。 下一步是创建 ChannelSales 报表。

创建 ChannelSales 报表

1. 在 Excel 中，在用于创建先前报表的同一工作表上，选择单元格 B26。

2. 在“ 插入 ”选项卡上，选择“ 数据透视表”。

3. 在 “选择要分析的数据 ”部分中，选择“ 使用外部数据源 ”选项，然后选择“ 选择连接”。

   此时会显示“现有Connections”对话框。

4. 在此工作簿部分中的Connections中，选择 AdventureWorksDW2012Multidimensional-EE 数据连接，然后选择“打开”。

5. 选择“ 现有工作表” 选项，然后选择“ 确定”。

   将打开“PivotTable3”以供编辑。

6. 在“数据透视表字段”列表中，指定以下选项：

• 在 “销售订单 ”部分中，选择“ 订单计数”。

• 在 “销售摘要 ”部分中，选择“ 销售额”。

• 在 “销售渠道 ”部分中，选择“ 销售渠道”。

  报表将更新以显示一个展示 Internet 和经销商渠道的订单计数和销售额的表。

7. 选择单元格 B26，然后在 编辑 栏中删除行标签的文本，并输入“渠道销售”。 然后，在键盘上按 <Enter>。

8. 为该报表指定新名称。 在“ 分析 ”选项卡的“ 数据透视表 ”组中，在“ 数据透视表名称 ”框中，删除数据透视表3 的文本，键入 ChannelSales，然后在键盘上按 <Enter>。

9. 在“ 文件 ”选项卡上，选择“ 保存”。

10. 保持 Excel 工作簿的打开状态。

此时，我们已经使用相同的数据源创建了三个报表。 下一步是创建 OrderSales 报表。

创建 OrderSales 报表

1. 在 Excel 中，在用于创建先前报表的同一工作表上，选择单元格 H26。

2. 在“ 插入 ”选项卡上，选择“ 数据透视表”。

3. 在 “选择要分析的数据 ”部分中，选择“ 使用外部数据源 ”选项，然后选择“ 选择连接”。

   此时会显示“现有Connections”对话框。

4. 在此工作簿部分中的Connections中，选择 AdventureWorksDW2012Multidimensional-EE 数据连接，然后选择“打开”。

5. 选择“ 现有工作表” 选项，然后选择“ 确定”。

   将打开“PivotTable4”以供编辑。

6. 在“数据透视表字段”列表中，指定以下选项：

• 在 “销售订单 ”部分中，选择“ 订单计数”。

• 在 “销售摘要 ”部分中，选择“ 销售额”。

• 在“ 产品 ”部分中，选择“ 产品类别”。

  报表将更新以显示一个展示不同产品类别的订单计数和销售额的表。

7. 选择单元格 H26，然后在 “编辑 ”栏中删除 “行标签”的默认文本，然后键入“产品”。 然后，在键盘上按 <Enter>。

8. 为该报表指定新名称。 在“分析”选项卡的“数据透视表”组中的“数据透视表名称”框中，删除“数据透视表4”的文本，键入“OrderSales”，然后按键盘上的 Enter<>。

9. 在“ 文件 ”选项卡上，选择“ 保存”。

10. 保持工作簿处于打开状态。

此时，我们已经为基本仪表板创建了四个报表。 下一步是创建筛选器。

部分 3：创建筛选器

使用 Excel，我们可以创建多种不同类型的筛选器。 例如，我们可以通过在"字段"列表的"筛选器"部分输入字段来创建一个简单筛选器。 我们可以创建切片器，或者，如果我们使用的是诸如 Analysis Services 的多维数据源，则可以创建日程表控件。 在此仪表板示例中，我们将创建日程表控件。 利用该筛选器，用户可以查看特定时间的信息。

创建日程表控件

1. 在 Excel 中，在用于创建报表的同一工作表上，选择单元格 B1。

2. 在“ 插入 ”选项卡上的“ 筛选器” 组中，选择“ 时间线”。

   此时会显示“现有Connections”对话框。

3. 在此工作簿部分中的Connections中，选择 AdventureWorksDW2012Multidimensional-EE 数据连接，然后选择“打开”。

4. 此时将显示 “插入时间线 ”对话框。

5. 选择“ 日期 ”选项，然后选择“ 确定”。

   将打开日程表控件。

6. 移动日程表控件，以使其左上角与单元格 B1 的左上角对齐。

7. 若要加宽日程表控件，可以使用尺寸控点，将控件右侧的尺寸控点拖到 M 列。

8. 选择时间线控件，然后在“选项”选项卡上的“时间线”组中，选择“报表Connections工具栏”命令。

   此时将显示“报表Connections”对话框。

9. 选择“ ChannelSales”、“ GeoSales”、“ OrderSales”和“ ProductSales”，然后选择“ 确定”。

10. 在“ 文件 ”选项卡上，选择“ 保存”。

11. 保持 Excel 工作簿的打开状态。

此时，我们已经创建了一个仪表板。 下一步是将其发布到 SharePoint Server 2013，供其他人使用。

发布仪表板

若要将工作簿发布到 SharePoint Server 2013，我们将遵循一个四步过程。 首先，我们进行一些调整，这些调整会影响工作簿的显示方式。 其次，为我们使用的外部数据连接指定 Excel Services 数据身份验证设置。 然后，指定工作簿的发布选项。 最后，我们将工作簿发布到 SharePoint Server 2013。

我们首先对工作簿进行一些调整。 默认情况下，我们的仪表板示例会在包含仪表板的工作表中显示网格线。 此外，默认情况下，工作表称为 Sheet1。 我们可以进行一些细微的调整，以改进仪表板的显示方式。

改善工作簿的显示外观

1. 在 Excel 中，选择“ 视图 ”选项卡。

2. 若要从视图中删除网格线，请在“视图”选项卡上的“显示”组中，清除“网格线”复选框。

3. 若要删除行标题和列标题，请在“视图”选项卡上的“显示”组中，清除“标题”复选框。

4. 若要重命名工作表，请右键单击其 “Sheet1”选项卡，然后选择“ 重命名”。 输入工作表的新名称（如 SalesDashboard），然后在键盘上按 <Enter>。

5. 在“ 文件 ”选项卡上，选择“ 保存”。

6. 保持工作簿处于打开状态。

我们希望我们创建的工作簿在发布时所使用的外部数据连接仍然保持活动状态。 若要确保数据连接保持活动状态以便在 Excel Services 中支持数据刷新，我们必须指定身份验证设置。

指定外部数据连接的身份验证设置

1. 在 Excel 的“数据”选项卡上，选择“Connections工具栏”命令。

   此时将显示“工作簿Connections”对话框，并显示 AdventureWorksDW2012Multidimensional-EE 数据连接。

2. 选择“属性”。

3. 在“连接属性”对话框中的“定义”选项卡上，选择“Excel Services”旁边的“身份验证设置...”。

4. 在“Excel Services身份验证设置”对话框中，执行以下步骤之一：

• 如果Excel Services配置为使用 Windows 身份验证或 EffectiveUserName 功能，请选择“使用经过身份验证的用户帐户”，然后选择“确定”。

• 如果 Excel Services 配置为使用 安全存储服务，则选择"使用存储的帐户"。 在“ 应用程序 ID ”框中，指定安全存储目标应用程序 ID，然后选择“ 确定”。

• 如果Excel Services配置为使用无人参与服务帐户，请选择“无”，然后选择“确定”。

  重要

  如果不知道要选择哪个选项，请联系 SharePoint 管理员。

5. 若要关闭“ 连接属性 ”对话框，请选择“ 确定”。

6. 如果看到一条消息，指出工作簿中的连接将不再与外部文件中定义的连接相同，请选择“ 是”。

7. 若要关闭“工作簿Connections”对话框，请选择“关闭”。

在为仪表板创建报表时，我们已给每个报表指定了唯一名称并在 Excel 中将其定义为已命名项。 除了将工作簿发布到 SharePoint Server 2013 之外，还应发布定义的命名项。 这样，就可以稍后在其自己的 SharePoint Web 部件中显示命名项。 我们通过为工作簿指定发布选项来执行此操作。

提示

本文未介绍如何在包含已命名项的 SharePoint Web 部件中显示这些项目。 因此，以下过程是可选的。 不过，我们建议您最好执行以下过程。 以免日后还须重新发布工作簿。

指定工作簿的发布选项

1. 在“ 文件 ”选项卡上，选择“ 信息”，然后选择“ 浏览器视图选项”。

2. 选择 “浏览器视图选项”。

3. 在"显示"选项卡上，使用列表选择"工作簿中的项目"。

4. 选择“ 所有图表”，然后选择“ 所有数据透视表”，然后选择“ 确定”。

5. 在“ 文件 ”选项卡上，选择“ 保存”。

6. 保持工作簿处于打开状态。

下一步是将工作簿发布到 SharePoint Server 2013。

将工作簿发布到 SharePoint Server

1. 在 Excel 中的“ 文件 ”选项卡上，依次选择“ 另存为”、“ 计算机”和“ 浏览”。

   此时将显示“ 另存为 ”对话框。

2. 在地址行中，输入Excel Services受信任文件位置的 SharePoint 地址。

3. 选择“保存”。

   工作簿将发布到您指定的 SharePoint 库中。

4. 关闭 Excel 工作簿。

现在我们已经创建并发布仪表板，我们可以使用它来分析数据了。

使用仪表板

仪表板发布到 SharePoint Server 2013 后，用户可查看和使用它。

打开仪表板

1. 打开 Web 浏览器。

2. 在地址栏中，输入发布仪表板的商业智能中心站点的地址。

3. 选择“ 网站内容”，然后选择“ 文档”。

4. 选择 Adventure Works Sales 仪表板。

   仪表板将打开以供查看。

现在仪表板处于可供查看的打开状态，我们可以使用它来获取特定问题的答案，如下表中所述的问题和答案。

表：使用仪表板获取特定问题的答案

问题	操作
哪一个产品类别的订单量最大？	使用 OrderSales 报表，查看各个产品类别的“订单计数”列。 您可以看到“附件”的订单量最大。 此外，配件在所有四个产品类别中总销售额也最少。
该公司通过 Internet 渠道销售的商品更多，还是通过经销商渠道销售的商品更多？	使用 ChannelSales 报表，您可以看到，尽管 Internet 渠道的订单较多，但经销商渠道的销售额最大。
哪一年的总销售额最大？	使用屏幕顶部附近的时间线控件，选择“月份”旁边的向下箭头，然后选择“年”。 使用该控件每次选择一个年份。 2007年销售额最高。
该公司迄今为止哪个销售区域的销售额最大？	若要从时间线控件中清除筛选器，请在时间线控件的右上角选择“清除筛选器”工具栏命令。 北美此特定公司的销售额最高。
自行车的哪一个子类别总体销售额最大？	使用 ProductSales 报表，双击 “自行车”的栏。 报表将更新以显示三个子类别： 山地自行车、 公路自行车和 旅游自行车。 “山地车”子类别的销售额最高。

另请参阅

概念

Excel Service (SharePoint Server 2013) 中的商业智能功能

使用数据模型创建 Excel Services 仪表板 (SharePoint Server 2013)