Building Queries Based on Microsoft Excel

Objective

After completing this lesson, you will be able to build and run a query in a Web Intelligence document using Microsoft Excel as a data source.

Microsoft Excel File as a Data Source

You can build queries using Excel files. Instead of connecting to a formal database, you can provide a self-contained dataset directly from a spreadsheet.

This approach is useful when you want to:

  • Create reports quickly before a formal data model is available.
  • Combine your own data with data from an existing corporate report.
  • Review small, standalone datasets for a specific project.

If you are working in the web client, ensure that the Excel file you want to use as a data source is available in the SAP BI Platform Repository, Google Drive, or Microsoft OneDrive (including SharePoint Online).

Only the Web Intelligence Rich Client supports files stored on your local computer.

Steps to Create a Query from a Microsoft Excel File

In the Select a Data Source dialog box, you can find the SAP BI Platform Repository category, which includes Excel.

The Excel option is highlighted for selecting an Excel spreadsheet as a data source.

When you build a new query using an Excel file, you can browse the SAP BI Platform Repository by Personal Categories, Corporate Categories, Favorites, Personal Folders, and Public Folders.

In the Excel Data Provider dialog box, you can set properties to interpret the Excel file:

  • Sheet name: The name of the worksheet containing the data.
  • Range definition: The data in the specified range is treated as query data.
  • Range name: The data in the named range is treated as query data.
  • First row contains column names: The first row in the range supplies the names of the result objects.
Excel Data Provider form selects a BI Platform Repository file, specifies Report 1 sheet, and includes all fields with column names in the first row.

The Query panel appears and looks the same no matter which data source you use. It includes three main panes:

  • The Data Outline pane lists objects available in the data source.
  • The Results Objects pane lists objects to include in your analysis.
  • The Query Filters pane lists filters you can apply to your query.
Query builder selects Year, Store name, and Sales revenue as result objects with a filter for City set to New York.

When your query is complete, click Run.

Let's Summarize What You've Learned

  • Use Microsoft Excel files as data sources for building queries in Web Intelligence.
  • Access Excel files from the SAP BI Platform Repository, Google Drive, or Microsoft OneDrive.
  • Define Excel file properties in the Excel Data Provider dialog box before querying.

Document with Multiple Queries

Business Example

Your manager asks you to create a single report for an upcoming regional performance review. She needs to see the sales objectives, the actual sales revenue, and the manager responsible for each store. This analysis will focus on key stores in California, Colorado, DC, and New York.

ExerciseStart Exercise

Learning

SAP FacebookSAP YoutubeSAP LinkedIn