Bug in OWC11 - Wrong Totals

Calculated measure shows incorrect totals!!!
[Measures].[Quantity (All Category)] = ([Measures].[Qty], [Category].[All Category])
let's say there are 3 categories ([Category].[Cat1], [Category].[Cat2], [Category].[Cat3]) with [Measures].[Qty] = 20 each.
The 3 categories are shown on the columns in the owc 11 pivottable and the [Measures].[Quantity (All Category)] shown as 60 for each category. But when i filtered out one of the category. The remaining 2 categories' [Measures].[Quantity (All Category)] shows as 40!!
I did not have this problem with using Crystal Analysis v10.

I'm using Analysis Services 2000. Is there a bug with pivottable services/MSOLAP8.0 driver?
Timmy

Here's my response to this question in the OLAP newgroup:

http://groups.google.com/group/microsoft.public.sqlserver.olap/msg/4b799055ad497b73

>>

This may be related to the "Visual Totals Mode" used by Excel and OWC, as explained in this past post:

http://groups.google.com/group/microsoft.public.sqlserver.olap/msg/fe7f645622c760b0

>>

From: Chris Webb
Date: Tues, Apr 19 2005 3:41 am

Well, I think the best place to start is probably the section on
VisualTotals
in this white paper:
http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/anserd...
What the paper calls 'Visual Totals Mode' is switched on by using the
'Default MDX Visual Mode' connection string property, and this is what
Excel
uses to calculate its subtotals. So once you know that the subtotals are
really just parent members with their values overwritten, it's clear
that
calculated measures are going to be evaluated after this has taken place
and
so aren't going to be affected - and that's why, to get the behaviour
needed
in this case, it was necessary to create a real measure and get the
desired
values into it somehow.
>>

To see the VisualTotals() effect in AS 2000, try this Foodmart query in
the MDX Sample app:

With Member [Measures].[Sales (All Category)] as
'([Measures].[Unit Sales], [Product].[All Products])'

select {[Measures].[Unit Sales], [Measures].[Sales (All Category)]} on 0,
VisualTotals(Except(DrillDownLevel({[Product].[All Products]}),
{[Product].[All Products].[Drink]})) on 1
from Sales

>>

Breaking monthly values down into daily values

Hi all, I'm new to MDX and am getting very confused with a script.

I'm running into problems breaking down monthly measures to daily values. If I have a monthly measure of 50, I would like to divide it by the number of days in the month to come up with daily values.

I believe I have set the proper granularity for the measurement relationship against the time dimension and have added the following script to my MDX script:

[Date].[Date].Members = [Measures].CurrentMember / [Date].[Calendar].CurrentMember.Parent.Children.Count

When I submit a query like this:

SELECT [Measures].[Measurement Objective] ON 0,
MTD([Date].[Calendar].[Date].&[20070207]) ON 1
FROM [Cube]

Everything looks good. The query returns seven rows, each with a properly scaled version of the monthly measurement. However, when I write the following query to return a single MTD value:

SELECT [Measures].[Measurement Objective] ON 0
FROM [Cube]
WHERE MTD([Date].[Calendar].[Date].&[20070207])

It doesn't work. It gives me the error:

The MDX Function CURRENTMEMBER failed because the coordinate for the
'Calendar Year' attribute contains a set

I'm sure this is just a matter of me misunderstanding MDX. Any help would be appreciate.
Thanks,
Richard

This is caused by the fact that you have used the .CurrentMember function in your calculation, but then have used a set (the MTD function returns a set of date members) in the WHERE clause. This means that there is not a single current member.

By introducing the ability to have sets in the WHERE clause, Microsoft have added extra complexity to writing calculated members.

The following variation should do the trick:

Code Snippet

[Date].[Date].Members = GENERATE( EXISTING( [Date].[Date].Members), [Measures].CurrentMember / [Date].[Calendar].CurrentMember.Parent.Children.Count)

The EXISTING() function returns the set of date members in the current context (the month to date members in your example query) and the Generate() function effectively "loops" over these members calculating your original expression. In this case the [Date].[Calendar].CurrentMember is evaluated within the context of each iteration of the "loop".

|||Thanks for the reply Darren.

I tried out the script and it didn't work. I was getting a divide-by-zero error in the results. Then I tried a simpler version without the division by the number of days in the month:

[Date].[Date].Members = GENERATE( EXISTING( [Date].[Date].Members), [Measures].CurrentMember)

which, given what you outlined, should just return the original monthly value (which is 128 for the month I'm using.) However, when I run the query each day has a value of 11891189. When I use the MTD function, it returns a concatenation of 11891189 * the number of days in the MTD set. The underlying measure is an integer.

Everything you outlined seems logical, but I'm just not sure where the 11891189 is coming from. I'm also restricting the scope of the MDX script to the single measure I'm testing.

Cheers,
Richard
|||

Actually I think I am missing a [Date] and the reference needs to be [Date].[Date].[Date].members

eg

[Date].[Date].[Date].Members = GENERATE( EXISTING( [Date].[Date].[Date].Members), [Measures].CurrentMember)

because [Date] is the dimension, [Date].[Date] is the hierarchy (which includes [Date].[Date].[All] which I think where the 11891189 might be coming from) [Date].[Date].[Date] is the actual attribute level.|||Hi Darren, it still didn't work and I've been doing a lot of investigation into the problem. I see what you mean by the problems of sets within the WHERE clause.

Instead of taking my original approach, I decided to use the Time Intelligence wizard within AS2005 and the MDX script that it generated worked like a charm. The extra YTD/MTD/QTD members in the time dimension makes a lot of sense.

Thanks again for your help
Richard