Hi,  I only want to show perspective to my customers and I set cube (default perspective) Visible as False which means no cube only perspective. Then I found CurrentCellAttributes doesn't work. Actually CurrentMember in the source doesn't work. Any workaround?

Thanks!

I'm able to reproduce the problem you're seeing. We hadn't tested when the base cube was not visible.

The problem is that the code used to try to use the perspective cube in prior releases, but we ran into all sorts of problems. So we changed the code to find the base cube for the perspective and loop the dimensions in the base cube. But in your case the base cube is hidden.

One workaround I found is adding ShowHiddenCubes=true to the connection string. But that rather defeats the purpose of hiding the base cube.

I'll consult the other developers of ASSP and see if we have any ideas.

The line of code that blows up with the base cube is Visible=false is:

            foreach (Dimension d in Context.Cubes[CubeName].Dimensions)

Context.Cubes[CubeName] returns null if the base cube is not visible.

Thanks furmangg! Acctually in my testing, it's the CurrentMember which doesn't work. For example in DrillThrough.cs\CurrentCellAttributesForCube(string CubeName), the reference for CurrentMember in h.CurrentMember.Type and coordinate.Append(h.CurrentMember.UniqueName) doesn't work if the base cube is invisible. Context.Cubes[CubeName].Dimensions is ok if you specify the CubeName as the perspective name. However if I keep set the base cube as *visible* and create the perspective, when I use Excel connecting to the perspective, the CurrentCellAttributesForCube works if I specify the CubeName as perspective name. The same function doesn't work if the base cube is invisible. I didn't test it with SSAS 2012. A comment from http://connect.microsoft.com/SQLServer/feedback/details/626767/hierarchy-currentmember-throwing-adomdserver-adomdexception-for-non-default-perspective#details said 'This issue has been fixed for our upcoming version of SQL Server'. Not tested whether it is...

Great observation. Yes, it does look like the following test query works in AS2012 but not in AS2008R2.

with member test as ASSP.CurrentCellAttributesForCube("Channel Sales")
select test on 0
from [Channel Sales]
where [Date].[Day Name].&[1]

So I guess we could add in some code that looked at the version of SSAS and if it's AS2012 then don't bother looking at the base cube to workaround the bug referenced.

What do you think? I guess you could compile a custom version of ASSP which doesn't look for the base cube in AMOHelpers.GetCurrentCubeName().

Thanks for your testing! Maybe I didn't report clearly.

With AS2008/R2, if using perspective, Context.CurrentCube returns the perspective, so as http://social.msdn.microsoft.com/Forums/en/sqlanalysisservices/thread/e8a947c7-d5ed-4a0f-98b5-30c8f4457c92, the lastest version of ASSP can use GetCurrentCubeName to return base cube name, even if user is using perspective.

However, if the base cube is set to invisible, even if we do as above or use Context.Cubes["Perspective Name"] directly, the CurrentCellAttributesForCube or CurrentCellAttributes still don't work. The reason is for AS 2008/R2 when base cube is invisible, Hierachy.CurrentMember doesn't work (when base cube is invisible).  So it's not related with the cube name but the Hierachy.CurrentMember ( therefore cannot fixed by modification of AMOHelpers.GetCurrentCubeName()). If this is the case, maybe we'd better leave it as is and make it a known issue. I don't think many people will set the base cube as invisible. And seems in your testing, AS2012 doesn't exist this problem.

In my testing, if I comment the 2 references for CurrentMember in CurrentCellAttributesForCube, it will not throw error. Or if I set the base cube as visible, it doesn't throw error either.

I cannot upgrade to AS2012 right away. So far my workaround is not using this SP but write all the hierarchy.CurrentMember.UniqueName directly to the drillthrough action.

Thanks again.

I think I'm understanding you.

As I posted above, I think we can loop through the perspective dimensions in AS2012 without error. That bug is apparently fixed. That wasn't the case in prior versions such as AS2008R2, so we worked around the error by looping through the base cube dimensions.

I don't know of a way to fix the code to work in AS2008R2 with the base cube hidden (other than using the ShowHiddenCubes connection string as mentioned above in the client tool which is firing the action).

But since that bug is fixed in AS2012, after you upgrade, you should be able to compile a custom version of ASSP which does not return the base cube in AMOHelpers.GetCurrentCubeName().

In my experience, hiding the base cube is not very common. (If others disagree, chime in.) I suppose we could change the code to detect AS2012 and a hidden base cube and then loop the perspective dimensions. But frankly, unless lots of other people have a hidden base cube, this probably won't get prioritized very highly on the list of code changes we plan to make. Is that fair?

So I'd suggest upgrading to AS2012 and then compiling a custom version of ASSP. I'm sorry that's not an answer you're going to enjoy hearing.

Thanks furmangg.