SSAS Cube Action: Cells Target type, URL Action type Example
Originally, we were charged with figuring out how to display SSAS cube measure descriptions via ToolTip in Excel 2007. If that's your plan, forget it - after some reading up on the interwebs, it appears that Excel doesn't even request the Description property. Additionally, if you want to add a description to Calculated Members, you have to hack it in (yuck).
So we went with a simple, albeit relatively crude (but effective), alternative - implementing a URL action for Cells so users can easily link out to a definition of the measure they're looking at.
Create a new action in your cube (Open up the cube definition, Actions tab) and configure similar to this:
Name: View Member Definition
Action Target
Target Type: Cells
Target object: All cells
Action Content
Type: URL
Action expression: "http://i.domain.com/doc/Defs.aspx#"
+ [Measures].CurrentMember.Name
Additional Properties
Invocation: Interactive
Description: View Member Definition
"View Definition Of " + [Measures].CurrentMember.Name + "..."
Caption is MDX: TrueWhen you're finished, it should look something like this:
For a possible way to implement the aforementioned Definitions.aspx, check out http://timlaqua.com/2009/03/scrolling-to-and-highlighting-anchor-target-via-javascript/ - which describes an early endpoint we used for this project.
Social Networks
What I'm Doing...
- @joekieffer good luck! in reply to joekieffer 2 weeks ago
- @ben_p bah, i'm proud of him - that's a ridiculous name to have on a resume. give him at least 2 years before you start the peer pressure in reply to ben_p 2010-06-11
- @michaelfransen ha, I was pretty sure u were ignoring me ;-) ty. in reply to michaelfransen 2010-06-11
- Last day at W3i today - moving to Trek Bicycle Corp! 2010-06-11
- Anyone know how to mimic gears.geolocation.getPermission() with the html5 navigator.geolocation object? want to add a message to the dialog 2010-06-07
- More updates...
Recent Posts
- Excel 2007 Hangs When Trying to Edit an OLAP Cube Filter
- Locating Rampant Database Growth
- Script sp_configure Output To Migrate Settings Between SQL Servers
- Script RESTORE DATABASE … WITH MOVE Stub
- VersionOne Nag: Reminding Us To Burn Our Points
March 13th, 2009 - 10:07
Tim, that is very frustrating. The best workaround I’ve found is to use the Search feature of OLAP PivotTable Extensions which will show the descriptions:
http://olappivottableextend.codeplex.com
March 13th, 2009 - 14:25
Yeah, I evaluated that option as well. The only issue there is that you’re still just working with the Description property of real (not calculated) measures. If you use the method I outlined, you can describe measures in as much detail as you like, with formatting, etc.
This also allows you to remove the BI Developer(s) from the definition maintenance process because the definitions are stored outside of the Cube.
March 25th, 2009 - 13:51
Very good approach and well done.
April 30th, 2009 - 18:11
I am trying to make your solution work for dimension attributes and must have the syntax wrong – this is what I have that isn’t working:
“View Definition Of ” + [Dimensions].CurrentMember.Name + “…”
May 2nd, 2009 - 06:35
There is no [Dimensions] dimension, you have to specify an actual dimension (so you would have to have a unique action for each dimension). Do you want users to be able to fire the action by right-clicking Hierarchy/Attribute headings?
May 18th, 2010 - 13:39
Tim,
I have the same problem as Joe Horton. I’d like to be able for the user to click on the dimension name and hyperlink off to a web page. I don’t want to define the values, but the name of the hierarchy in the dimension. For example, we have an [Account] dimension, and one of the attributes is [Account Category]. I’d like the user to be send “Account Category” to a web page.
Thoughts? (thanks in advance)
March 31st, 2010 - 03:45
Hi, i’m a complete noob at this, but i really want to learn. I’ve read a bit about this, but I cant seem to find a solution. How could i create an action that will let me do a google search on an item?
March 31st, 2010 - 07:02
By item, do you mean a particular member in an attribute hierarchy? So the theory is you would right-click on the caption of a member and it would do a google search? You would make a URL action and use the Member_Name of the member as the query in the google search querystring.
http://www.databasejournal.com/features/mssql/article.php/10894_3647056_1/Actions-in-Analysis-Services-2005–The-URL-Action.htm
April 1st, 2010 - 02:32
Hi I am also new to this. I am aslo trying to do a google search on an item. It works but not entirely. My problem is that if the item has an ‘&’ sign the string breaks.
For example I could have a string like this: “SSAS & Cube” so when i google search this item only “SSAS” will be searched for.
Any ideas on how to fix this?
April 1st, 2010 - 06:28
Yeah, you have to URL encode special characters. I’m not aware of any URL encoding functions in MDX or VBA off hand, but you can just use Replace() to do it:
"http://whatever.com/?q=" + [dim].[hierarchy].current_member.name
to
"http://whatever.com/?q=" + Replace([dim].[hierarchy].current_member.name, "&", "%26")
http://www.w3schools.com/tags/ref_urlencode.asp
April 1st, 2010 - 07:24
Hey Tim
Thanks for the reply. Im trying what you suggested but it does not seem to work. It does not even give me a option to google search it.
This is what I have:
“http://www.google.com/?q=” + Replace ([Menu Items].[Menu Item].currentmember.name, “&”, “%26″)
April 1st, 2010 - 07:36
Try:
"http://www.google.com/?q=" + vba!Replace([Menu Items].[Menu Item].currentmember.name, "&", "%26")
If you’re still having problems, I can test it out on an instance.
April 1st, 2010 - 07:46
Nope it still doing the same. I get no option to google search it.
April 1st, 2010 - 07:57
I see a space in your example between the Replace and the opening ( – there shouldn’t be a space there. Also check to make sure your double quotes are straight double quotes and not the fancy/slanted quotes (this is often a problem if you cut/paste from places).
April 1st, 2010 - 08:07
I tried all that also, still nothing happends…
April 4th, 2010 - 08:46
Yeah, I poked around a bit – looks like MDX doesn’t have access to Replace(). Have you considered adding a calculated column to the data source view that does the REPLACE() in T-SQL and then adding that calculated column as a member property to the [Menu Item] attribute? If you went that route, you would change
[Menu Items].[Menu Item].currentmember.nameto
[Menu Items].[Menu Item].currentmember.Properties("urlSafeName")April 21st, 2010 - 07:33
We spent some time poking around on this one recently with Tim Peterson and while vba!Replace isn’t accessable to MDX, excel!Substitute is available, so the following should work for you:
"http://www.google.com/?q=" + excel!Substitute([Menu Items].[Menu Item].currentmember.name, "&", "%26")
http://office.microsoft.com/en-us/excel/HP052092861033.aspx
May 5th, 2010 - 05:01
Thanks for your help Tim I tried that too but also does not work. But no need to worry, it is no longer required for me to do that.
Thanks!
June 18th, 2010 - 11:45
Ok, I looked for a long time for an answer to this – a pretty crucial functionality for a URL action. The excel!Substitute function does work, but you have to have Excel installed on the server that SSAS is installed on. That’s kind of crazy and MS gets a lot of complaints about it. Not addressed in 2008.