Reporting Action in Analysis Services

Analysis Services allows cube browsers like Excel to fire events which are handled by the Actions defined in the cube. In this blog post, I will explain a specific type of cube action called Reporting Action.  Reporting Action enables information consumers to fire a SQL Server Report when a specific condition is met while browsing the cube.

For the purpose of Illustration I’m using cubes and sample reports from AdventureWorks. I have also created a pivot table sourced from AdventureWorks Cube as shown below (Figure 1). The Reporting Action appears when you right click and select Additional Actions from the pivot table. 

Figure 1 - Pivot Table Sourced From Cube

The objective of this Reporting Action is four-fold:

1. To call the SQL Server Report. For this illustration, the Sales Reason Comparison report will be called from the Excel Pivot Table.

2. To call the report only when a condition is met. In this case, the report will be called only when the Fiscal Year and Product Category is selected either as Row/Column/Filter in the pivot table.

3. To supply parameters to the report.

4. To give a meaningful and dynamic title to the Report Action.

Figure 2 and 3 shows how the end-result should be. The steps to create the Reporting Action are explained below.

Figure 2 - Pivot Table Illustrating Reporting Action

Figure 3 - Report called from Pivot Table

 

Steps to create the Reporting Action

1. From BIDS, open the Analysis Service cube project. Navigate to Actions. Right Click on Action Organizer and click New Reporting Action (Shown in Figure 4).

Figure 4 - Create New Reporting Action

2. Select Target Type as Cells. For this example, Cells target type would be the best choice as we have to specify more than one dimension as conditional expression in the next step. (Figure 5)

3. Specify the path of the SQL Server Report in URL Access format as shown in Figure 5.

Figure 5 - Target Type and Report URL

4. Now,  it’s time to specify the conditions that make the Report visible to the Information Consumer. The condition expression should be a valid MDX expression. In this example, the Report Action should be visible only when the Fiscal Year and Product Category is selected. The MDX expression to achieve that is shown in Figure 6.

Figure 6 - MDX Condition Expression

[Date].[Fiscal].CurrentMember.Level IS [Date].[Fiscal].[Fiscal Year]
AND [Product].[Category].CurrentMember <> NULL

5. Next, supply the parameters to the report. As with conditional expression, the parameters should be a valid MDX expression The parameter name should be same as the one defined in the report (Figure 7). Since the parameter value in the report uses UniqueName, I’m using UniqueName as well.

TIP: In this example the SQL Server Report is Cube-based. Reporting Action can also be used with non-cube based database reports. For database reports, the MDX expression of parameter value can be modified to return the actual value instead of UniqueName. In such case, use [Product].[Category].CurrentMember.Value instead of [Product].[Category].CurrentMember.UniqueName.

Figure 7 - Supply Report Parameters

UrlEscapeFragment([Product].[Category].CurrentMember.UniqueName)

UrlEscapeFragment([Date].[Fiscal].CurrentMember.UniqueName)

6. Now specify an MDX expression to supply the caption/title of the Report Action when the cube is browsed (Figure 8).

Figure 8 – Caption

The following MDX expression is used to specify the title of the Report Action

"Sales Reason for "+
[Product].[Category].CurrentMember.Member_Caption +
" in " + [Date].[Fiscal].CurrentMember.Member_Caption

IMPORTANT: It is important to note that the MDX expressions used in Conditional Expressions, Parameter Values and Caption must evaluate to valid values. If either one of them does not return a valid result, the Report Action will disappear when the cube is browsed. No errors are thrown, but the Report action just does not show up.

TIP: If your Report Action disappears from the scene, do not panic. The easiest way to debug is to switch the cube from Design view to Code View and comment out all the nodes containing MDX Expressions. Then un-comment one by one, deploy your cube and test again to find out the offending MDX expression.

That completes my example for Reporting Action on Analysis Services.

Benny Austin

---

Possibly related posts: (automatically generated)

• Deploying Report Actions in Analysis Services
• Analysis Services Cube Concepts – Made Simple
• AMO Wrappers for Securing Analysis Service Cube
• Reporting Services