Excel 12 MDX and Filtering

[Andreas Flockermann [Bona Vista]]

Hi NG,

I tested these days Excel 12 with ASY. I realized that the Pivot Table uses
sub cubes to filter.
This has some bad side effects with some calculated members that aggregate
using LASTPERIODS (12) over a time period.
When you put the time dim in the filter and you use a calculated measure
with LASTPERIODS (12) the 12 time members are filtered out by the sub cube
and you just get the member in the filter.
In Excel XP the calculated measures work fine because Excel XP uses the
WHERE clause to filter and LASTPERIODS (12) returns 12 members.
Has anybody an idea if this is a Excel bug? Whats wrong here?

Andreas

[Andreas Flockermann [Bona Vista]]

[Deepak Puri]

Hi Andreas,

Differences in behavior between filters in the WHERE clause vs. subcubes
are also discussed in this entry in Michael Barrett Jensen's blog - not
sure what the work-around would be:

http://sqljunkies.com/WebLog/reckless/archive/2006/03/08/18601.aspx
>>
MDX: EXISTING operator and subselects
..
What this really means is that our subselect does not set the current
coordinate in the cube (in Moshas words it "merely does top level Exists
with axis and applies visual totals"). This is really important to
remember if you ever want to use the EXISTING operator for some fancy
MDX calculations, because if your client tools use subselects (which,
for example, Excel 12 does extensively I am told), you might run into
some unexpected results.
..
>>

- Deepak

Deepak Puri
Microsoft MVP - SQL Server

*** Sent via Developersdex http://www.developersdex.com ***

[Deepak Puri]

To update this issue - here's an excerpt from the Q&A transcript of
today's Tech-Ed simulcast: "Microsoft Office Excel 2007 and SQL Server
2005 Analysis Services Integration Explained":

>>
Question: A question which came up in the OLAP Newsgroup: Excel 2007
uses FROM subcube MDX for filter members. This creates problems with
Time Intelligence calcs generated by the Wizard, because the Time
current member is NOT set to the filter member (as happened in Excel
2003). Any comment?

Answer: The semantic of FROM subcube is currently under review by both
Excel and Analysis Sevices teams to make sure semiadditive measures,
distinct count, visual totals, etc work well with Excel filtering. Also
another thing being considered is to reverse to the WHERE clause (like
Excel 2003) for the case where a single member is selected in the
filter. All those changes will show up in the Office 2007 RTM (i.e. the
official release)