SSAS Actions are powerful! You can open web pages, open sql server reporting services, customize drill through reports among other things using actions. In this post, you will see a common requirement from users to navigate to a corporate intranet site from the cube – and usually it needs to be dynamic.
For example, user is interested in seeing the Order Entry Page hosted on the corporate intranet site by using the Order ID from the SSAS cube.
Here’s how you can set it up:
1. Open SSAS Cube in SQL Server Data Tools:
2. Navigate to Actions tab:
3. Here you’ll see three types of action that you can configure
a. Standard (this have five subtypes including the URL action)
b. Drill Through
c. report action
4. For the purpose of this blog post, let’s focus on standard action:
5. Once you click on the “New Action” it will ask you to configure the action:
a. Name: Enter the desired name here
b. Target Type: In this case, Order ID is an attribute member but you will have to choose appropriate target type for your scenario
c. Target Object: In this case, it’s something like [Order].[Order ID] – in your case, you’ll have to choose an appropriate target object
d. Type: URL in this case (also don’t forget to check books online for what other types can do as well)
e. Action Expression: the format of the Action Expression if it’s driven by a parameter would go something like:
[code language=”SQL”]
"http://servername/site/Pages/OrderRef.aspx?Search&ID="+[Order].[Order ID].currentmember.member_caption
[/code]
f. Additional Properties: I like to set the Caption to clearly indicate the user that they are opening the “Order Form for Order ID 123999”. You can do that by setting the caption property. The format goes like this:
[code language=”SQL”]
"Open Order Entry page for Order ID: "+[Order].[Order ID].currentmember.member_caption
[/code]
Also set the caption is MDX to True if you are using above format.
That’s about it, don’t forget to test it (after deploying the cube) using excel or other end-user tool of your choice. In the Pivot Table, use the Order ID attribute in Row/Column labels > Right Click on any attribute member of Order ID attribute > Additional Actions > The caption with dynamic order id should show by here for users to click and navigate to the specified URL: