Back referencing in an mdx query

Question

I'm just getting going here with mdx, having done a few pretty basic calculations.  Now I have one that has me stumped.  BTW I'm trying to just build members and such in ssas so that I am free to use the excel pivot table feature, rather than doing mdx in ssrs with parameters and such.

I have a measure group that's pretty non-aggregating in nature, atypical for cubes I guess.  Anyway I'm looking at the settings for a bunch of devices for two revisions.  I want to add a calc that will tell me, for the two members selected, are the measures different.

                              Revision 1                 Revision 2
Device       Is Diff    Setting1  Setting2         Setting1   Setting2
asdf-1234    TRUE       1              2                  1            3
adsd-1234    FALSE      4             5                  4            5
adss-1234    FALSE      3             4                  3             4

I tried making a measure and using .prevmember, but that refers to the previous member in the dimension rather than the previous member in the current query.  If I was using ssrs I'd think that I would just use the set defined by STRTOSET(@revisionsFilter), but I'm using excel.  That method also has the drawback of being a measure when what I really need is a dimension value, since I only want to see it once per row.

The calc can return NULL if there are more than two revisions selected.

As always I'm willing to follow up on leads and fiddle with things, but I could really use some direction here.

Thanks,

Ken

Answer 1

Here's the mdx query that excel generates to use the pivot table, with revisions filered down to 2.

SELECT NON EMPTY CrossJoin(Hierarchize({DrilldownLevel({[Revision].[Revision].[All]})}), {[Measures].[Manufacturer],[Measures].[Model],[Measures].[Size],[Measures].[Long Time Delay],[Measures].[Long Time Pickup],[Measures].[Short Time Delay],[Measures].[Short Time Pickup],[Measures].[Instantaneous Delay],[Measures].[Instantaneous Pickup],[Measures].[I2t Function]}) 
DIMENSION PROPERTIES PARENT_UNIQUE_NAME ON COLUMNS ,

NON EMPTY Hierarchize(DrilldownMember({{DrilldownLevel({[Relay Device].[Relay Device Type Tree].[All]})}}, {[Relay Device].[Relay Device Type Tree].[Settings Device Type].&[Low Voltage Breaker],[Relay Device].[Relay Device Type Tree].[Settings Device Type].&[Relay]})) DIMENSION PROPERTIES PARENT_UNIQUE_NAME,[Relay Device].[Relay Device Type Tree].[Settings Device].[Settings Device Family],[Relay Device].[Relay Device Type Tree].[Settings Device].[Settings Device Type] ON ROWS  

FROM (SELECT ({[Revision].[Revision].&[25], [Revision].[Revision].&[1]}) ON COLUMNS  FROM [ETAP Mart]) 
CELL PROPERTIES VALUE, FORMAT_STRING, LANGUAGE, BACK_COLOR, FORE_COLOR, FONT_FLAGS

Sorry it's not formatted great..

Answer 2

Another method that almost works:

Var

 

({[Relay Device].[Relay Device].currentmember} * Revision.Revision.MEMBERS,[Measures].[Long Time Pickup])

I could just check for a variance of 0, but I don't know how to arrive at the correct set.  I want the device currentmember, but for revisions I want all the members that are denoted in the from clause.  Based on what I've read I'd just leave the revision out of the set fed to the var() function and the "default" from the from clause would do its part there.  But when I try this:

Var({[Relay Device].[Relay Device].currentmember},[Measures].[Long Time Pickup])

I get -1.#IND

Answer 3

Well I discovered that the answer to this problem is to upgrade to AS2008, where the context from subselects is not so hidden as it is in AS2005.  Until then I have to throw some formulas inside excel to get around this problem.