Perform an OLAP server action in a PivotTable report

If your PivotTable report is connected to a Microsoft SQL Server Analysis Services Online Analytical Processing (OLAP) database, you can perform a server action by using Microsoft Office Excel. A server action is an auxiliary command that performs related tasks on the data in your report.

What do you want to do?

Learn more about server actions

Perform a server action

Learn more about server actions

A server action is an optional but useful feature that an OLAP cube administrator can define on a server that uses a cube member or measure as a parameter into a query to obtain details in the cube, or to start another application, such as a browser.

The following are common examples of using server actions in a PivotTable report:

• You are a sales representative who wants to prepare for an important customer visit. You use a server action to display a customer profile in a Web page directly from a standard PivotTable report of your sales area. The Web page contains links to additional information, such as maps and recommended restaurants.

• You are a business analyst for a hospital supplier and notice that a valued customer has reduced its orders by 35% in the last year. You want to find out why, so you use a server action to view the customer's order history on a separate worksheet.

• You are a district sales manager who wants to probe a product sales figure and see which customers have contributed most to those sales by "drilling through" to the details contained in a fact table on the cube, so you can plan sales targets and goals for the next fiscal quarter.

There are five types of server actions.

URL     This commonly-used server action displays a Uniform Resource Locator (URL) in a browser. For example, you can click a customer name and then display a customer profile on a Web page.

An OLAP cube administrator can define a URL server action on dimension members, hierarchies, hierarchy members, attribute members, measure cells, or the entire cube.

Report     This server action creates a Microsoft SQL Server Reporting Services report by building a report-based URL that takes the following syntax form:

http://<reporting server name>/<server directory>

An OLAP cube administrator can define a Report server action on dimension members, hierarchies, hierarchy members, attribute members, measures, or the entire cube.

Rowset     This action returns a rowset based on a condition specified in an MDX statement. This action is useful for linking a rowset to data in a different but related cube in the same OLAP database, such as store sales from several years ago. The data is displayed in a worksheet as an Excel table.

An OLAP cube administrator can define a Rowset server action on dimension members, hierarchies, hierarchy members, attribute members, measures, or the entire cube.

Drill Through     This action queries the cube based on a condition specified in an MDX statement and returns data from the fact table that is the source of the measure's aggregated values. The data is displayed in a worksheet as an Excel table. The MDX statement specifies which columns should be returned from the fact table and the maximum number of records to return, specified in the Maximum number of records to retrieve property under the OLAP Drill Through section on the Usage tab of the Connection Properties dialog box. For more information, see Connection properties.

An OLAP cube administrator can define a Drill Through server action only on measures.

Show Details     This action queries the cube based on a condition specified in an MDX statement and returns data from the fact table that is the source of the measure's aggregated values. This action is available by default but can be controlled by the Enable show details check box under the PivotTable Data section on the Data tab of the PivotTable Options dialog box. For more information, see PivotTable options.

An OLAP cube administrator can define a Show Details server action only on measures.

Notes: 

• The following server actions are not supported in Office Excel: Proprietary, Statement, and Dataset.

• Although an OLAP cube administrator can define a server action on a set, a server action on a set is not supported by Office Excel.

• Server Actions are not supported on a report filter.

Top of Page

Perform a server action

• In a PivotTable report, right click a column or row label, click Additional Actions, and then select the command defined by the OLAP cube administrator.

  For example:

  • URL     A URL server action command, such as Customer Profile.

  • Report     A Report server action command, such as Q1Summary.

  • RowSet     A RowSet server action command, such as Product Details.

  • Drill-Through     A Drill-through server action command, such as Sales Numbers.

  • Show Details     

    Tip: You can also double-click the cell that contains the measure.

    If there are no server actions defined by the OLAP cube administrator, you see the menu entry (No actions defined).

Top of Page