Prologika Forums

With an Action on a cell is there a way of dynamically passing the members in all hierarchies?

Stored procedures are called before the URL is activated on drill throughs so I thought it'd save a lot of maintenance to create a stored procedure to generate the URL.

Problem is I can't find a way in the action to dynamically get all the hierarchy members that may contribute to the cell. i.e. Any rows, any columns, any slicers.

e.g.  for the action expression

"http://" + AdventureWorks!GimmeAURL(<<MDX for the cell>>)

or

"http://" + AdventureWorks!GimmeAURL(<<list of unique names of hierarchy members and qualifying the cell>>,[Measures].CurrentMember.UniqueName)

I can accomplish the second way by hardcoding every hierarchy that may be used.

e.g.

"http://" + AdventureWorks!GimmeAURL([Time].[Aiscal].CurrentMember.UniqueName+","+[Time].[Calendar].CurrentMember.UniqueName+","+[Product].[Hierarchy1].CurrentMember.UniqueName+","+ ... [Product].[Hierarchyx].CurrentMember.UniqueName+","+ ... [Dimensionx].[Hierarchyx].CurrentMember.UniqueName,[Measures].CurrentMember.UniqueName)

but that's a lot of typing and requires modification if you add a new hierarchy that could contribute to the cell.

Adding another parameter for a report code and you could use the same action expression from just about anywhere. e.g.

"http://" + AdventureWorks!GimmeAURL("REPORT1",<<list of unique names of hierarchy members and qualifying the cell>>,[Measures].CurrentMember.UniqueName)

(at the end , the program the URL calls either matches the passed parameters to the reports parameters , or uses the passed parameters and the UDM metadata to generate a query)

Simply put I'm after a way of getting hierarchy members in an action without hardcoding hierarchy names.

Any ideas appreciated.

It's the example in 11.4.3 that made me think this may be possible. All the hierarchies isn't essential but all that go towards referencing the selected cell is.

From an action I want to construct a URL to pass to a non-UDM aware product.

Can I construct a query (like on the top of p. 377) or a set of hierarchies without hardcoding and dimension names?

In the example is there something I could pass that I could derive the members from

rather than hardcoding

[Customer].[Customers by Geography].[City].Members,[Product].[Product by Category].SubCategory].&[1]

into the action.

Try this stored procedure and see if it works for you. If I manage to find more elegant approach, I wll post it here. It would have been nice if the Context object exposes the selection context, as the OWC PivotTable component does so you don't have to traverse the hierarchy... Remove the conditional check if you want to get all dimension members (including the default members).

   Public Shared Function GetTupleCoordinates(ByVal tuple As Tuple) As String

        Dim tupleContext = String.Empty
        Dim val As MDXValue
        Dim d As Microsoft.AnalysisServices.AdomdServer.Dimension
        Dim h As Microsoft.AnalysisServices.AdomdServer.Hierarchy

        For Each d In Context.CurrentCube.Dimensions
            For Each h In d.Hierarchies
                Dim expr As Expression = New Expression(String.Format("{0}.CurrentMember", h.UniqueName))
                val = expr.CalculateMdxObject(tuple)
                If (String.Compare(val.ToMember().UniqueName, h.DefaultMember, True) <> 0) Then
                    tupleContext += IIf(tupleContext <> String.Empty, ",", String.Empty) + val.ToMember().UniqueName
                End If
            Next
        Next

        Return tupleContext

    End Function

Invocation syntax in your action:

"http://" + AdventureWorks.GetTupleCoordinates(Measures.CurrentMember)

Looks good thanks Teo.

It's the Context object I need to explore.

Cheers

Of course, it would be more effiicient if you don't use the Expression object whatsover :-)

    Public Shared Function GetTupleCoordinates1(ByVal tuple As Tuple) As String

        Dim tupleContext = String.Empty
        Dim d As Microsoft.AnalysisServices.AdomdServer.Dimension
        Dim h As Microsoft.AnalysisServices.AdomdServer.Hierarchy

        For Each d In Context.CurrentCube.Dimensions
            For Each h In d.Hierarchies

                If (String.Compare(h.CurrentMember.UniqueName, h.DefaultMember, True) <> 0) Then
                    tupleContext += IIf(tupleContext <> String.Empty, ",", String.Empty) + h.CurrentMember.UniqueName
                End If
            Next
        Next
        Return tupleContext

    End Function

Hi Tlachev,

First thanks for posting the above code, it works almost perfectly for me.

However, the code has one problem it does not work when we select multiple page filters, the action menu does not come up when right clicked in cube cell.

I request you to please help me on this problemas as early as possible.

regarsds

sanjay

If CurrentMember doesn't return the selected member, then your browser is most likely doing more exotic things, such as using session sets to filter the query results. SQL Profiler can prove this. There is no solution in this case for getting the filter context.

Hi Teo,

Thank you very much for the quik reply.

Your comments are true.

On week end i also find that drillthrough has a requirement that no more than one member can be specified for each axis. (as in MDX Soultions by Wiley, page 414).

Thanks once again for the code posted by you to capture current context of the pivot table sections which helped me create dynamic drill through action in SAS cube. 

Further, I would like to know more about the code you posted and MSAS programming approach, as i used your code as is, I was looking for your book, Applied MSAS but it seems its not available in INDIA, MUMBAI.

Have Good Day!!!

Thanks

Sanjay