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 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).

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.

Deploying Report Actions in Analysis Services

another good #ssas article regaring reporting actions from @BennyAustin http://t.co/x6AjQbzEeO #powerbi
— Alex Ivory (@aivoryuk) August 12, 2014

27 thoughts on “Reporting Action in Analysis Services”

Neelakantan says:

May 5, 2015 at 00:07

Is it Possible to use SSAS Actions in to SSRS Reports? Give me any possible Way

Reply
1. Benny Austin says:
  
  May 7, 2015 at 08:56
  
  Neelakantan, reporting actions are meant for self-service cube browsers like Excel and do not work with reporting tools like SSRS.
  
  Reply
Pingback: Drillthrough personalizado sobre medidas calculadas en SSAS (SQL Server Analysis Services) | SolidQ Blogs
Pingback: Reporting Action in Analysis Services | Benny Austin | Sladescross's Blog
Pingback: How to: Criar uma ação de Report no Cubo | Thiago Patrocinio
Pingback: Reporting Actions – Its All About the Nutmeg ! « popbi.com
Pingback: Reporting Actions – Its All About the Nutmeg ! « POP-BI
Abdul Khan says:

December 2, 2011 at 18:01

Thank you Sir….Really Simple and Helpful…!!!

Reply
malli says:

September 21, 2011 at 21:49

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

Reply
1. Benny Austin says:
  
  September 22, 2011 at 14:44
  
  Looks like report issue. Check whether the correct parameters are being passed to the report.
  
  Reply
Ganga Sagar says:

July 30, 2011 at 01:16

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.

Reply
1. Benny Austin says:
  
  July 30, 2011 at 14:24
  
  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.
  
  Reply
ttoennies says:

March 11, 2011 at 01:56

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

Reply
1. Benny Austin says:
  
  March 12, 2011 at 13:31
  
  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.
  
  Reply
  1. Olda says:
    
    June 13, 2011 at 19:58
    
    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. Benny Austin says:
    
    June 14, 2011 at 21:24
    
    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. Benny Austin says:
  
  June 14, 2011 at 21:29
  
  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
  
  Reply
ricks says:

July 18, 2010 at 02:10

pingback from http://social.msdn.microsoft.com/Forums/es-ES/sqlserveres/thread/47378b46-aab8-4aaa-a646-bfc596ccf267

hoy estoy creando una accion de drill down y me funciono bien la primera vez, pero cuando la quiero volver a consultar ya no aparece en el excel… ya la borre y la volvia a crear en el cubo y no me aparece… q puedo hacer….

Reply
ricks says:

July 12, 2010 at 13:12

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

Reply
1. Benny Austin says:
  
  July 14, 2010 at 09:41
  
  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.
  
  Reply
Ben says:

October 27, 2009 at 01:13

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?

Reply
1. bennyaustin says:
  
  October 27, 2009 at 22:45
  
  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.
  
  Reply
Eric Ladouceur says:

October 10, 2009 at 05:46

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?

Reply
1. bennyaustin says:
  
  October 10, 2009 at 18:10
  
  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.
  
  Reply
bennyaustin says:

September 8, 2009 at 18:58

Related to this post, check-out Deploying Report Actions in Analysis Services – http://wp.me/pxNuz-7Z

Reply
Pingback: Deploying Report Actions in Analysis Services « Benny Austin
Pete Fosz says:

July 20, 2009 at 23:53

Thank you so much for supplying the proper parameter syntax, I’ve been looking for this!

Reply