SQL SERVER – What are Actions in SSAS and How to Make a Reporting Action

Actions are used for customized browsing and drilling of data for the end-user. It’s an event that a user can raise while accessing the cube data. They are used in cube browsers like excel and are triggered when a user with a client tool clicks on a particular member, level, dimension, cells or may be the cube itself. For example a user might be able to see a reporting services report, open a web page or drill through to detailed information related to the cube data. Let us learn about how make a reporting action.

The analysis server supports 3 types of actions:-

1. Report
2. Drill-through
3. Standard Actions

In this blog post, I will explain the Reporting action. The objective of this action is to return a report with details of the product, where the sales amount is greater than 1000 in the cube browser analysis. You need to create a basic cube first with the facts and dimensions you want in the analysis. Following are the steps to create reporting action.

1. Go to SQL server data tools and open the analysis service project. Navigate to actions and click on the new reporting action.

2.) Specify the name of the action and choose the target type as attribute members since we have to create the action on members of an attribute.

3.) Specify the Target object of your report action. A Target object would be the dimension or attribute on which you want the report to appear. In our case it is a product name.

4.) Next, you have to define the condition on which you want the report link to appear. However, this is an optional feature. In this example we are specifying a condition, which will check if the sales amount is greater than 10,000. So, that the link appears only for those products where the defined condition is met.

5.) Next, you have to specify the server name on which the report is present, report path and the report format in which you want the report to appear.

6.) Additionally, you can specify the parameters. 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.

7.) Deploy your solution after you are done with specifying parameters and go to the cube browser.

8.) Click on the analyze in excel button, this will open your cube in excel

9.) Make an analysis which shows product names and their sales amount.

10.) Right click on a product where the sales amount is greater than 10000 you will see the reporting action link. Click on that and you will be taken to your reporting services report.

11.) Clicking on the link will take you to the URL of the report. I created this report using report project wizard in SQL server data tools.

So, this is how we can launch reports from a cube browser. Similarly, you can open web pages, run applications and a number of other tasks. Thank you Namita for helping to write this blog post.

Reference: Pinal Dave (https://blog.sqlauthority.com)