tim laqua dot com Thoughts and Code from Tim Laqua

12Mar/0921

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: True

When you're finished, it should look something like this:
ssascubecellsurlaction

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.

Comments (21) Trackbacks (0)
  1. 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

  2. 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.

  3. Very good approach and well done.

  4. 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 + “…”

  5. 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?

    • 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)

      • I am looking to accomplish the same thing as Jonathan…when a user right-clicks the dimension, hierarchy or attribute they should be taken to a separate definition for each…is this possible?

        Thanks

        Ben

  6. 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?

  7. 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?

    • 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

      • 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”)

        • 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.

          • Nope it still doing the same. I get no option to google search it.

          • 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).

  8. I tried all that also, still nothing happends…

    • 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.name
      to
      [Menu Items].[Menu Item].currentmember.Properties("urlSafeName")

    • 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

  9. 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!

  10. 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.

  11. Hi Tim

    I implement what u said on adventure worksdw but when i right click the cell and click on View defintion of sales Amount…… i am getting an error: The IP address for the website you requested could not be found.


Leave a comment

No trackbacks yet.