(sorry for double posting, but it seems to me, the first thread got lost
within newer ones.)
Hello,

the Problem is that OWC is displaying wrong Total results, when setting the
filter (in OCW Pivottable manually) on time dimension (eg. only Quarters Q1,
Q2, Q3 in 2004). This applies to Previous Year Values calculated via:
([MEASURES].[Sales], ParalellPeriode([TIME].[YEAR],1))

Time Dimension in this scenario consists of (Year, Quarter, Month, Day).
Data is loaded always on day level and aggregated up.

(filter) has been set to [2004].[Q1], [2004].[Q2], [2004].[Q3] .

So actual Sales Sum for the total Level (2004) Sums up the first 3 included
Quarters (as shown in the example).

Example assuming sales for Q1=1000, Q2=2000 and so on, in both of the years

SALES S_YTD S_PREV S_PREV_YTD
2004
Q1 1000 1000 1000 1000
Q2 2000 3000 2000 2000
Q3 3000 6000 3000 3000
SUM 2004 6000 6000 10000 10000
==== ====

10000 is wrong in this case, it also should display 6000, summing up Q1-Q3
in the last year. The Previous Year Value should also care about the filter,
displaying the parallel of only the filtered quarters in 2004.

This seems to me to be a OWC Bug which I urgently have to fix.

Deepak and Ohjoo came up with really good solutions, which unfortunately
only work, when Time dimension is in Rows (and furthermore not combined with
any other dimension).

I believe that much more user experience the same problem/bug (as previous
year values should be a common business scenario) and I'd like to ask the
community if there is already a solution or if anyone can think of a solution
for this where it doesn't matter if time dimension is in filter, row or
colums.

Best regards
Monte

Can you try the following approach?
- Delete any calculated members etc you created on your cube for the
previous solution.
- Create a new *real* (ie not calculated) measure and call it S_PREV
- For the Source Column property, enter the value 0.
- Create a new calculated cell and call it anything you like
- For the new calculated cell, enter the Calculation Subcube as
{[Measures].[S_PREV]}, [Time].[Month].MEMBERS
- For the Calculation Value, enter calculationpassvalue(
(measures.[sales], parallelperiod(time.[year], 1)), -1)

Hopefully this will work for you! By creating a real measure rather than a
calculated measure you ensure that the VISUALTOTALS functionality which Excel
and OWC uses to calculate subtotals works as you want; the calculated cell
just inserts the previous period values over the dummy value of 0 in this
measure.

It's not an elegant solution and carries the risk of a) increased cube size,
and b) reduced query performance, as a result of the calculated cell, but if
your cube is fairly simple then with a bit of luck these shouldn't be issues
for you.

HTH,

Chris

"Monte" wrote:

Sorry, if the lowest level on your Time dimension is day, the calculation
subcube should be {[Measures].[S_PREV]}, [Time].[Day].MEMBERS}

"Chris Webb" wrote:

Honestly, I can't type today - that last } shouldn't have crept in there! The
calculation subcube should be
{[Measures].[S_PREV]}, [Time].[Day].MEMBERS

"Chris Webb" wrote:

I tried out your approach and it worked perfectly well on my Dev-Server!

But I checked the license version of my customers SQL-Server and just as I
was afraid of ... it is a just standard edition ... so I cannot use
calculated cells to work this around :-|

Any other clues?

Best regards!

"Chris Webb" wrote:

Hi Chris,

You said your idea is not an elegant solution, but it's very wonderful idea
indeed!

Do you have any more information on visual totals with calculated members?

I have a curiosity to know that this issue is dependent on just client tools
or so-called by-design of analysis services.

Ohjoo Kwon


"Chris Webb" <OnlyForPostingToNewsgroups (AT) hotmail (DOT) com> wrote

Here's an approach that seems to work with the Foodmart Warehouse cube;
but it involves setting up a 2nd cube and a virtual cube:

- Create a view of the fact table where 1 Year is added to the Date key
(for Foodmart, 365 is added to time_id).

- Create a second cube, copied from first, with this view as fact
table. Retain only measures whose previous year values are required,
renaming them (like SalesPrevYear).

- Combine the 2 cubes into a virtual cube, with all original dimensions
and measures + the PrevYear measures.


- Deepak

Deepak Puri
Microsoft MVP - SQL Server

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

No, I don't think there are going to be any other solutions that what Deepak
has suggested, which is to physically put the values into the measure rather
than use calculated cells.

"Monte" wrote:

Well, I think the best place to start is probably the section on VisualTotals
in this white paper:
http://www.microsoft.com/technet/pro.../anserddl.mspx
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.

Chris



"Ohjoo Kwon" wrote:

The approach to double all fact tables with a deferral of one year is the
only working solution in this case I see, and btw. a simple but clever
solution!

I'll have to check how data-volume growth and above all query performance is
affected by this step, but I'm confident that it will even response more
quickly than complex mdx.

Above all I'd like to thank the community especially all those who have
answered my posts, really going into my case, trying hard to find various
very decent solutions!!

Thanks for your time and thoughts!

Best regards!