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.

Pivot Table Sourced From Cube
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 2 – Pivot Table Illustrating Reporting Action
Figure 3 - Report called from Pivot Table
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
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
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
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
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
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.

Related Post

  • Deploying Report Actions in Analysis Services
  • 27 thoughts on “Reporting Action in Analysis Services

    1. Hi
      actually first i created report action a date hirarchy levels ,after i created a another report action for another dimension hirarchy level,upto to this it is working when i am trying to combination ,if i browse cube and then rt click on the sales i got the combination of both hirarchy but when i click on that report action it is showing only one report value another one is giving 0 how can i solve this

    2. Hi Austin,

      I read the article. It was excellent and easy to understand.

      I implemented the same thing to create a reporting action in one my projects. I am not able to see the action when i am browsing the cube.
      In that action i am adding one parameter too.

      1. Thanks for you feedback. If your report action is not showing up then it indicates one of the MDX expressions used either in Conditional Expressions or Parameter Values or Caption did not evaluate to valid value. Few ways to troubleshoot this (a) Remove all MDX expression from your report action and add one by one iteratively. Deploy and browse the cube to find out which expression is causing the the report action to disappear (b) You could also write an equivalent MDX query and verify that the expression returns expected result (c) If you are using Conditional Expression, check whether the condition is satisfied otherwise the report action will not show up as well.

    3. I need to create a fairly simple report with different date values on columns and measures displayed on the rows. There will also be a filter using a dimension. How can I create report actions which are specific to each measurember so that when the user right-clicks the cell of the measure it only displays the specific report? In the condition box I have tried using a statement like [Measures].[Measure Name].CurrentMemberNULL but that prevents the action from being displayed. Any suggestions would be appreciated. Thanks

      1. Interesting question. You might have already noticed that SSAS allows only one report to be specified for a Reporting Action. However you can create any number of reporting action for the same condition. Which means in your case, I would suggest creating one reporting action for the same condition for each relevant measures. User will be presented with a choice to select the required report when the cell is right-clicked. And if you want users to see only the specific report, try using NOT IsEmpty([Measures].[MeasureName]) instead in the condition.

        1. Hello Benny,

          your post is great, I want to use this NOT IsEmpty([Measures].[Invoice Qty]) you advised. But it is not working how I expected. I need to see REPORT ACTION only on field [Measures].[Invoice Qty], but its visible also on others [MEASURE] fields.

          Is it possible to constrain it onlz for one field ?

        2. Try this. In your report action, set Target Type= Cells, Target Object = All Cells and Condiftion = [Measures].CURRENTMEMBER IS [Measures].[ measure name]. This works for me in AdventureWorks SQL2008R2

      2. This works. In your report action, set Target Type= Cells, Target Object = All Cells and Condiftion = [Measures].CURRENTMEMBER IS [Measures].[ measure name]. This works for me in AdventureWorks SQL2008R2

    4. ya cree una accion en mi cubo desde el visual studio, defini el nombre, target type(Cells), target object(all cell) tambien defini server name, report path y los parametros, y procese el cubo… pero cuando lo abro con excel me dice q no he definido ninguna accion… q tengo q hacer para q poder ver esta accion

      1. comments from ricks translated from spanish to english
        and create an action in my cube from the visual studio, defined the name, target type (Cells), target object (all cell) also defined server name, report path and parameters, and process the cube … but when I open it with excel q tells me I have not set any action … I have q q q do to be able to see this action
        end of ricks comments
        ricks, the report actions usually disappear when one or more MDX expressions is incorrect or evaluate to invalid values. So ensure the MDX expressions supplied at the conditional expression, parameters, caption and other places evaluate to valid expressions. Test your MDX expressions before sticking them in. If you are not sure which expression is failing, remove all the MDX expressions and add one by one. Test your report action after each add. The last add that causes the report action to disappear is the faulty one and needs to be corrected. Good luck.

    5. Hi,

      Is there any way to pass more than one member through to the report?

      for e.g If a user was browsing a hierarchy, the parameter could pass all members in the slice they selected the action from?

      1. Ben, I haven’t personally tried that but my initial thoughts are that it may not be possible. Analysis Service uses URL access to trigger report action. URL access format for parameters with multiple values is specified by repeating the parameter name. For example: http://ReportServerWebServerName/VirtualPath?/ReportFolder/orders&region=east&region=west . This means in cube action the parameter names have to be repeated as many times as the members which is dynamic and Analysis Service does not allow duplicate parameter names.

    6. Hi, I have followed this example. Everything works except that after entering a parameter, I don’t see my action anymore. I made a diff of the code with and without the parameter, and the only difference is this:

      Step Name
      [DIM SEQUENCE].[Step Name]

      Any ideas?

      1. hi Eric, if the action disappears then it’s an indication that atleast one attribute of the report action is invalid. For e.g the MDX expression specified as the condition or parameter or caption does not evaluate to a valid member at execution time. During development it’s a good practise to add the attributes one by one and test the action immediately so that if the action fails you can narrow down the problem to te last attribute added. In your case since you have already narrowed down the problem to the parameters, I would recommend the following:
        1. Check whether the parameter name you specified in the action is same as the name given in the report.
        2. If the report parameter is sourced from dataset, check whether the parameter expression you specified matches the value field of the report parameter. If you are using MDX dataset, just copy the MDX expression that goes into the value and UrlEscapeFragment(<>) and use it as parameter expression. If you are using a non-MDX dataset, then you may have to massage the parameter to match the value field of the report parameter for e.g use [Dim].[Attrib].CurrentMember.Value or [Dim].[Attrib].CurrentMember.Level.Ordinal
        3. Test your MDX expression in Management Studion by framing a simple MDX statement. Keep the slicer as relevant as possible.

        Hope this helps.

    Leave a Reply

    Fill in your details below or click an icon to log in:

    WordPress.com Logo

    You are commenting using your WordPress.com account. Log Out / Change )

    Twitter picture

    You are commenting using your Twitter account. Log Out / Change )

    Facebook photo

    You are commenting using your Facebook account. Log Out / Change )

    Google+ photo

    You are commenting using your Google+ account. Log Out / Change )

    Connecting to %s