SQL SERVER – What are Actions in SSAS and How to Make a Reporting Action
- by Pinal Dave
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 in 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.
Analysis server supports 3 types of actions :-
Report
Drill-through
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 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.
Go to SQL server data tools and open the analysis services project. Navigate to actions and click on new reporting action.
2.) Specify the name of the action and choose target type as attribute members since we have to create the action on members for a attribute.
3.) Specify the Target object of your report action. Target object would be the dimension or attribute on which you want the report to appear. In our case it is 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 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. Koenig Solutions offers SSAS training which contains all Analysis Services including Reporting in great detail.
In my next blog post I will talk about drill-through actions.
Author: Namita Sharma, Senior Corporate Trainer at Koenig Solutions.
Reference: Pinal Dave (http://blog.sqlauthority.com)Filed under: PostADay, SQL, SQL Authority, SQL Query, SQL Server, SQL Tips and Tricks, T SQL Tagged: SSAS
