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.

SQL SERVER - What are Actions in SSAS and How to Make a Reporting Action kssas-1

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.

SQL SERVER - What are Actions in SSAS and How to Make a Reporting Action kssas-2

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.

SQL SERVER - What are Actions in SSAS and How to Make a Reporting Action kssas-3

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.

SQL SERVER - What are Actions in SSAS and How to Make a Reporting Action kssas-4

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.

SQL SERVER - What are Actions in SSAS and How to Make a Reporting Action kssas-5

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.

SQL SERVER - What are Actions in SSAS and How to Make a Reporting Action kssas-6

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

SQL SERVER - What are Actions in SSAS and How to Make a Reporting Action kssas-7

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

SQL SERVER - What are Actions in SSAS and How to Make a Reporting Action kssas-8

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

SQL SERVER - What are Actions in SSAS and How to Make a Reporting Action kssas-9

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.

SQL SERVER - What are Actions in SSAS and How to Make a Reporting Action kssas-10

SQL SERVER - What are Actions in SSAS and How to Make a Reporting Action kssas-11

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)

SQL Analysis Services, SSAS
Previous Post
SQL SERVER – Repair a SQL Server Database Using a Transaction Log Explorer
Next Post
MySQL – Introduction to CONCAT and CONCAT_WS functions

Related Posts

5 Comments. Leave new

  • Hi Pinal,
    Can you explain me which one is better CHAR or VARCHAR in case of performance and also VARCHAR and nVARCHAR

    Reply
  • Neelakantan
    May 4, 2015 7:33 pm

    Is it Possible to use SSAS Actions in to SSRS Reports

    Reply
  • Neelakantan
    May 4, 2015 7:34 pm

    Is it Possible to use SSAS Actions in to SSRS Reports, Give me any solution for this.

    Reply
  • Fabrice Calvaire
    October 31, 2019 2:43 am

    Hi, thank you for this post.
    Is it possible to use the action in a perspective ?
    I got an message error when I try to use the action in Excel.

    “Impossible to use with calculated member” but my kpi is not a calculated member -_-

    Could you hlp me please ?

    Reply
  • Fabrice Calvaire
    October 31, 2019 2:45 am

    Does actions work with perspective ?

    I got an error message with my action when i use my perspective with Excel.
    “Cant extract data with calculated member” but my KPI is not a calculated member

    Could you help me ?

    Reply

Leave a Reply