I have a SSAS 2012 cube, which has a report action. This action should be conditionally available, based on the user being required to select a specific Scenario.
The action itself works fine, the problem I'm having is that the condition I have defined for the action only works most of the time.
While browsing the cube in Excel, if I add Activity (a measure) to Values, and I put scenario across columns, so long as I right click an activity cell (excluding Grand Total), the action is available as expected. A couple columns of example data as it would appear:
2010 Actuals 2011 Actuals
Activity -13,812,430 11,938,733
While browsing the cube, if, in addition to the above configuration, I put an Account hierarchy on rows, and expand the lowest level, the action will not be available for some cells, while available for most others. A couple columns of example data as it would appear:
Row Labels 2010 Actuals 2011 Actuals
123 - ABC -1,889 41,251
456 - DEF -562,243 -2,053,282
789 - GHI -25,000 -12,454
Since the action condition only requires that a specific scenario be selected (2010 Actuals, 2011 Actuals, ect), I would expect that right clicking on any of the measure values in the example data would have the action available. The action is available for 5 of the 6 cells, but is not available for 456 - DEF 2011 Actuals. What could cause this?
The condition MDX in the action is:
[Scenario].[SA - Scenario].CurrentMember <> [Scenario].[SA - Scenario].[All]
While running SQL profiler, if I right click a cell that behaves correctly, and click 'show details', the MDX that gets sent is:
DRILLTHROUGH MAXROWS
1000 SELECT FROM [SomeName] WHERE
([Measures].[Activity]
,[Account].[A1 - Level 1].[Level 1].&[Balance Sheet].&[Assets].&[Current Assets].&[Cash].&[Cash in Bank].&[456 - DEF]
,[Scenario].[SA - Scenario].[Scenario].&[79])
If I click a cell that does not have the action available, the MDX sent is:
DRILLTHROUGH MAXROWS
1000 SELECT FROM [SomeName] WHERE
([Measures].[Activity]
,[Account].[A1 - Level 1].[Level 1].&[Balance Sheet].&[Assets].&[Current Assets].&[Cash].&[Cash in Bank].&[456 - DEF]
,[Scenario].[SA - Scenario].[Scenario].&[80])
The only difference between the MDX sent for the cells appears to be the Scenario value, which I believe is correct, since we're selecting the same member from Account, just a different scenario. In one case, the condition appears to evaluate true, while in the second case it appears to evaluate false, since the action is not available.
Admittedly, I'm not very good with MDX. However, since most of the time the action is available, I believe the expression is valid and works, otherwise, the action should never be available. (This is the case when I put in a bogus condition).
As a sanity check, if I set the MDX condition to be TRUE, the action is always available as one would expect. If I switch out Account for another dimension, and drill to the lowest level, I cannot reproduce this. It appears to be related somehow to members, for certain scenarios, under the lowest account level.
I've looked at how the Account dimension is set up, compared to other dimensions, and I don't see anything that jumps out at me as being different structure wise.
What else could cause this behavior that I have not yet looked into?
Your expression will evaluate a measure for the current scenario and the grand total and compare the two numbers:
[Scenario].[SA - Scenario].CurrentMember <> [Scenario].[SA - Scenario].[All]
Try the following:
Not ([Scenario].[SA - Scenario].CurrentMember Is [Scenario].[SA - Scenario].[All])