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.
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.
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).
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.
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.
[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.
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.
another good #ssas article regaring reporting actions from @BennyAustin bennyaustin.wordpress.com/2009/07/19/rep… #powerbi—
Alex Ivory (@aivoryuk) August 12, 2014
27 thoughts on “Reporting Action in Analysis Services”
Is it Possible to use SSAS Actions in to SSRS Reports? Give me any possible Way
Neelakantan, reporting actions are meant for self-service cube browsers like Excel and do not work with reporting tools like SSRS.
Thank you Sir….Really Simple and Helpful…!!!
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
Looks like report issue. Check whether the correct parameters are being passed to the report.
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.
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.
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
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.
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 ?
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
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
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….
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
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.
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?
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®ion=east®ion=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.
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:
[DIM SEQUENCE].[Step Name]
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.
Related to this post, check-out Deploying Report Actions in Analysis Services – http://wp.me/pxNuz-7Z
Thank you so much for supplying the proper parameter syntax, I’ve been looking for this!