Business intelligence capabilities in Excel Service (SharePoint Server 2013)

APPLIES TO: yes-img-132013 no-img-162016 no-img-192019 no-img-seSubscription Edition no-img-sopSharePoint in Microsoft 365

Excel offers certain business intelligence capabilities that make it easier than ever to explore and analyze data. Many of these features are supported by Excel Services. Read this article to learn about which capabilities in Excel are supported in SharePoint Server 2013 and Office Web Apps Server.

Important

The information in this article applies to on-premises deployments of SharePoint Server 2013.

Business intelligence capabilities in Excel 2013

Using Excel 2016, you can create powerful reports, scorecards, and dashboards. You can bring data into Excel, sort, and organize data, and use it to create reports and scorecards. You can also use powerful analytic capabilities in Excel to visualize and explore data. The following table describes business intelligence features in Excel.

Table: Overview of business intelligence capabilities in Excel 2013

Task area Capability Description
Get data
Native data
Native data is data that was imported into Excel and lives in Excel without maintaining an external data connection. This is useful for working with static data, working with data that is updated manually, and creating workbooks that are published in locations where external data connections are not supported.
Get data
External data
External data is data that resides on another computer and is accessed in Excel through one or more external data connections. Excel can connect to many kinds of data sources.
Work with data
Data Models
A Data Model is a dataset that consists of multiple tables. You can use Data Models to bring together data from different databases and create relationships between tables of data. You can use a Data Model as a source for reports, scorecards, and dashboards. For more information, see PowerPivot: Powerful data analysis and data modeling in Excel.
Work with data
Flash Fill
Flash Fill is functionality that enables you to quickly and easily format data that is displayed in a table in Excel. For example, suppose that you have imported data into Excel and you have a column that lists dates and times, and you only want to use dates. You can insert a new column, type the date that corresponds to the first item in the format that you want to use, and then use Flash Fill to automatically do this for all the rows in your table.
Create reports
Quick Analysis
Quick Analysis is functionality that enables you to select a range of data and see recommended ways to visualize that information. When you use Quick Analysis, you can see what your chart or table will resemble before actually selecting which chart type to use.
Create reports
Reports
Reports can include tables, line charts, bar charts, scatter plots, radar charts, and many other kinds of charts. You can use Excel to create powerful, interactive PivotChart reports and PivotTable reports to display relevant information by using lots of data sources.
Create reports
Scorecards
Scorecards are a special kind of report designed to show whether performance is on- or off-target for one or more metrics at a glance. Scorecards typically contain one or more key performance indicators (KPIs) that compare actual values to target values and use a graphical indicator, such as colors or symbols, to show performance at a glance.
In Excel, you can use KPIs that were defined in an external database or use Power Pivot to create your own KPIs.
Create reports
Power View
Power View is an add-in for Excel that you can use to create highly interactive views. Power View enables you to quickly and easily create reports, scorecards, and dashboards. You can configure connections between items in a view so that values in one report can be used as parameters for other reports in the view. For more information about Power View, see Power View: Explore, visualize, and present your data.
Create or edit reports
Field List and Field Well
When you create a PivotChart or PivotTable report, you use the Fields section to specify what information to display in the reports. The Fields section contains the Field List and Field Well.
The Field List lists items such as dimensions and members from the data source that you are using for the PivotChart or PivotTable report. You can also use the Field List to determine whether you are using native data or external data.
The Field Well shows which items from the Field List are displayed in the report. You can change what information is displayed in a PivotChart or PivotTable report by selecting (or clearing) different items and measures.
Apply filters
Timeline control
The Timeline control is a special kind of time filter that you can use in Excel. To add a timeline control to a worksheet, at least one report on the worksheet must use data that contains a calendar date/time hierarchy. You can use the timeline control to select a single time period or a range of time, and any reports that are connected to the timeline are automatically updated to show information for that time period.
Apply filters
Slicers
Slicers are a kind of filter that you can use to select one or more items to use as a filter for reports and scorecards in a worksheet. For example, suppose that you want to see sales information for different colors of items, such as shirts that your company carries. You can create a slicer that lists shirt colors, connect it to a sales report, and then use the slicer to view sales information for the colors that are selected in the slicer.
Explore and analyze data
Quick Explore
Quick Explore is functionality that enables you to click a value in a PivotChart or PivotTable report that uses SQL Server Analysis Services data or Power Pivot data, and then see additional information about that value displayed as a chart type. You can also use Quick Explore to drill up (or down) to view higher (or lower) levels of detail in a PivotChart or PivotTable report.
Use advanced analytic capabilities
Calculated Members and Calculated Measures
Calculated Members and Calculated Measures are items that you can define by using Multidimensional Expressions (MDX) queries in Excel. You can create calculated members and calculated measures for PivotChart or PivotTable reports that use multidimensional data that is stored in Analysis Services.
> [!IMPORTANT]> Calculated Members and Calculated Measures are not the same things as Calculated Fields. You can only create Calculated Members and Calculated Measures when you have created a PivotTable report or a PivotChart report that uses data that is stored in Analysis Services. To create calculated members or calculated measures, you should be familiar with the database that you are using and be proficient in writing MDX queries.
Use advanced analytic capabilities
Calculated Fields
Calculated Fields enable you to change a Data Model that was created by using Power Pivot. When you create Calculated Fields, custom columns are added to the Data Model. You can then use those columns in reports that you create using that Data Model.
> [!NOTE]> To create calculated fields, you should be familiar with the database that you are using and be proficient in writing Data Analysis Expressions (DAX).

Business intelligence capabilities supported in Excel Services in SharePoint Server

After you have created reports, scorecards, and dashboards using Excel, you can publish those items to SharePoint Server 2013 to make them available to people in your organization. The following table summarizes which business intelligence features are supported by Excel Services (on premises).

Table: Overview of business intelligence capabilities in Excel Services

Item Supported in Excel Services in the on-premises version of SharePoint Server 2013?
External data connections
Most external data connections are supported in Excel Services. Excel Services supports SQL Server Analysis Services (SSAS), SQL Server databases, and OLE DB and ODBC data sources. For more information, see Data authentication for Excel Services (SharePoint Server 2013).
Data Models
Data Models are supported in Excel Services as long as an instance of SQL Server Analysis Services is specified for Excel Services. For more information, see Plan Excel Services Data Model settings in SharePoint Server 2013.
Flash Fill
Flash Fill is not supported when you view a workbook in a browser window. You can edit a workbook in Excel to use Flash Fill.
Quick Analysis
Quick Analysis is not supported when you view a workbook in a browser window. You can edit a workbook in Excel to use Quick Analysis.
Reports and scorecards
Reports, scorecards, and dashboards that were created by using Excel are supported in Excel Services. You can view, sort, filter, and interact with PivotTable reports and PivotChart reports in a browser window much such as you would by using the Excel client. This includes views that were created by using Power View.
Field List and Field Well (for PivotChart and PivotTable reports)
The ability to open and use the Field List and Field Well in a browser window is supported in Excel Services.
Timeline control
The ability to use an existing timeline control is supported in Excel Services. However, to add a timeline control to a workbook, you must use the Excel client.
Slicers
The ability to use existing slicers is supported in Excel Services. However, to add slicers to a workbook, you must use the Excel client.
Quick Explore
The ability to use Quick Explore is supported in Excel Services. You can use Quick Explore to drill up and down to view higher or lower levels of information. However, you can't create new views using Quick Explore in a browser window.
Calculated Members and Calculated Measures
The ability to use existing calculated members and calculated measures is supported in Excel Services. However, to create calculated members and calculated measures, you must use the Excel client.
Calculated Fields
The ability to use existing calculated fields is supported in Excel Services. However, to create calculated fields, you must use Power Pivot for Excel.
Business Intelligence Center
A Business Intelligence Center site template is available in SharePoint Server 2013. This enables you to create a site that serves as a central location to store business intelligence content, such as reports, scorecards, and dashboards. For more information, see Configure a Business Intelligence Center in SharePoint Server 2013.
Excel Web Access Web Part
An Excel Web Access Web Part is available in SharePoint Server 2013. This enables you to display all or part of an Excel workbook that was published to SharePoint Server 2013. For more information about how to use the Excel Web Access Web Part, see Display Excel content in an Excel Web Access Web Part.

Business intelligence capabilities supported in Office Web Apps Server 2013 and SharePoint Server 2013

Office Web Apps Server is the online companion to Office Word, Excel, PowerPoint, and OneNote applications. Office Web Apps Server gives users a browser-based viewing and editing experience by providing a representation of an Office document in the browser. Organizations that have SharePoint Server 2013 might also be using Office Web Apps Server, and that has certain implications for which business intelligence capabilities are supported in a browser window.

Important

If your organization is using Office Web Apps Server together with SharePoint Server 2013, then your organization can use either Office Web Apps Server or SharePoint Server 2013 to display workbooks. This decision affects which business intelligence capabilities are supported in a browser window. > To enable people to interact with workbooks that contain a Data Model or Power View views in a browser window, configure Excel Services in SharePoint Server 2013 to display workbooks. This requires a SharePoint admin to run the New-SPWOPISupressionSetting cmdlet on the server where SharePoint Server 2013 in installed. For more information, see New-SPWOPISuppressionSetting.

The following table summarizes the business intelligence features that are supported by Office Web Apps Server and by SharePoint Server 2013.

Table: Overview of business intelligence capabilities in Office Web Apps Server and SharePoint Server

Excel Capability What's supported when Office Web Apps Server is used to display workbooks What's supported when SharePoint Server 2013 is used to display workbooks
External data connections and authentication methods
Office Web Apps Server supports some, but not all, kinds of secure external data connections. Data connections that use Secure Store Service or an unattended service account are supported as long as SharePoint Server 2013 is configured to use these methods.
SharePoint Server 2013 supports most kinds of external data connections. These include SQL Server Analysis Services (SSAS), SQL Server databases, and OLE DB and ODBC data sources.
For more information about authentication methods, see Data authentication for Excel Services in SharePoint Server 2013 .
Data Models
Office Web Apps Server enables you to view workbooks that contain Data Models that use native data. However, you can't explore data in items such as PivotTable reports, PivotChart reports, and timeline controls that use a Data Model as the data source.
SharePoint Server 2013 enables you to view, interact with, and refresh workbooks that contain Data Models using native or external data. This includes the ability to explore data in PivotChart reports and PivotTable reports and to use timeline controls in a browser window.
Flash Fill
Office Web Apps Server does not support using Flash Fill when you edit a workbook in a browser window. However, you can open the workbook in Excel to use Flash Fill.
SharePoint Server 2013 does not support Flash Fill when you view a workbook in a browser window. However, you can open the workbook in Excel to use Flash Fill.
Quick Analysis
Office Web Apps Server does not support using Quick Analysis when you edit a workbook in a browser window. However, you can use open the workbook in Excel to use Quick Analysis.
SharePoint Server 2013 does not support Quick Analysis when you view a workbook in a browser window.
Reports and scorecards
Office Web Apps Server enables you to view, sort, and filter reports, scorecards, and dashboards that were created by using Excel. However, Office Web Apps Server does not support views that were created by using Power View.
SharePoint Server 2013 enables you to view, sort, and filter reports, scorecards, and dashboards that were created by using Excel. This includes views that were created by using Power View.
Field List and Field Well (for PivotChart and PivotTable reports)
Office Web Apps Server enables you to open and use the Field List and Field Well in a browser window.
SharePoint Server 2013 enables you to open and use the Field List and Field Well in a browser window.
Timeline control
Office Web Apps Server enables you to use an existing timeline control. To add a timeline control to a workbook you must use the Excel client.
SharePoint Server 2013 enables you to use an existing timeline control. To add a timeline control to a workbook you must use the Excel client.
Slicers
Office Web Apps Server enables you to use slicers.
SharePoint Server 2013 enables you to use slicers.
Quick Explore
Office Web Apps Server enables you to use Quick Explore.
SharePoint Server 2013 enables you to use Quick Explore.
Calculated Members and Calculated Measures
Office Web Apps Server supports workbooks that contain calculated members and calculated measures. However, to create calculated members and calculated measures, you must use the Excel client.
SharePoint Server 2013 enables you to use existing calculated members and calculated measures. However, to create calculated members and calculated measures, you must use the Excel client.
Calculated Fields
Office Web Apps Server does not support calculated fields.
SharePoint Server 2013 enables you to use existing calculated fields. However, to create calculated fields, you must use Power Pivot for Excel.
Business Intelligence Center
You can use the Business Intelligence Center site template to create a central location to store business intelligence content, such as reports, scorecards, and dashboards. For more information, see Configure a Business Intelligence Center in SharePoint Server 2013.
You can use the Business Intelligence Center site template to create a central location to store business intelligence content, such as reports, scorecards, and dashboards. For more information, see Configure a Business Intelligence Center in SharePoint Server 2013.
Excel Web Access Web Part
The Excel Web Access Web Part that is available in SharePoint Server 2013 enables you to display all or part of an Excel workbook that was published to a SharePoint library in a Web Part on a SharePoint page. Even if Office Web Apps Server is used to display workbooks in a browser window, the Excel Web Access Web Part uses Excel Services in SharePoint Server 2013 to display content in that Web Part.
For more information about how to use the Excel Web Access Web Part, see Display Excel content in an Excel Web Access Web Part.
The Excel Web Access Web Part that is available in SharePoint Server 2013 enables you to display all or part of an Excel workbook that was published to a SharePoint library in a Web Part on a SharePoint page.
For more information about how to use the Excel Web Access Web Part, see Display Excel content in an Excel Web Access Web Part.

To learn more about Office Web Apps Server, see Overview of Office Web Apps Server .

See also

Concepts

Overview of Excel Services in SharePoint Server 2013

Other Resources

What's new in business intelligence in SharePoint Server 2013

Office Web Apps