I'm new to MDX , and have big problem
I need to give the user ability to interactive filter [Time] dimension
to 'between' dates, but I also need to keep hierarchy of time and
aggregation in the selected period.
I'm using Mondrian and OpenI (JPivot).

I have cube with 2 dimensions
-[Time]
* all
** year
*** quarter,
**** month
***** day
and
-[Measure].[document_value]

I have document values from last 10 years for every day.

I tried to use Filter function or enumerate [Time] members , but when I
for example filtered-out all time members and left only:
Quarter1 and Quarter4 from 1989
still had 'year' level aggregation equal sum of four quarters , and
'all years' equal sum of all (for example 10) years ((((

How to cope with it?? Is there a nice and efficient way to solve my
problem???

P.S. User can select for example dates between 2006.05.12 - 18.09.2006
so (as I understand )every middle aggregation needs to be 're-defined'
How to do it

Does this thread from the newsgroup apply to your scenario?

http://groups.google.com/group/micro...olap/msg/66361
802c533e52a?hl=en&
{MemberI:MemberN}?

From: Vladimir Chtepa
Date: Sat, Jul 1 2006 5:18 pm

Hi Deepak,thank your for the link to the Chris bolg.I have find another
a
little optimised formula without descendants(...,,LEAVES).with
-- initial set.
set set1 as
{[Date].[Calendar].[Month].&[2001]&[8]:[Date].[Calendar].[Month].&[2003]
&[5*]}
set set2 as generate(set1,
{ascendants([Date].[Calendar].currentmember)})
set set3 as generate({set1.Item(0).Item(0).PrevMember, tail(set1,
1).Item(0).Item(0).NextMember},
{ascendants([Date].[Calendar].currentmember)})
set set4 as set2 - set3
set set5 as filter(set4, ({[Date].[Calendar].currentmember.parent} -
set4).count > 0)

select {} on 0,
set5 on 1
from [Adventure Works]It works also for for abrbitrary level.


"Deepak Puri" <deepak_p... (AT) progressive (DOT) com> wrote





- Deepak

Deepak Puri
Microsoft MVP - SQL Server

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

Hi Deepak
Thanks for reply

I don't think that thread you proposed can help to solve my problem.
Maybe I just don't know how to use it properly (which is quite
probable)(if so - tell me)

or
I poorly explained what the problem is.

To better explain what's the point, I draw a kind of pivot table to
show what I finally want to get

http://213.25.33.253/~tomeks/mdx/MDX_problem.htm

Any suggestions are very appreciated

Tomek

Hi Tomek,

Thanks for the pivot table illustration - it seems to show a typical
VisualTotals() usage scenario (if that MDX function is available in your
environment):

http://msdn2.microsoft.com/en-us/library/ms145527.aspx

VisualTotals (MDX)
Updated: 17 July 2006

Returns a set generated by dynamically totaling child members in a
specified set, optionally using a pattern for the name of the parent
member in the result set.
...
Examples

The following example returns the visual total for the third quarter of
the 2001 calendar year based on the single descendant specified - the
month of July.

SELECT VisualTotals
({[Date].[Calendar].[Calendar Quarter].&[2001]&[3]
,[Date].[Calendar].[Month].&[2001]&[7]}) ON 0
FROM [Adventure Works]
...

- Deepak

Deepak Puri
Microsoft MVP - SQL Server

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

Hi Deepak

That's what I've been looking for!!!
There is VisualTotals() function in Mondrian.

Thank you very much.

Tomek