hi everyone,
one of my dimensions is date dimension that contains 3 level:
year
month
day

i want to run mdx query that gives me all the measures on the cols axe
and the year (only the year) level and one more dimension on the row axe. i
also want the data to be from year 2005 and 2006 so i craeted member that
contains thes two years:
Set [DateSet] as
'{[Date].[All Date].[2005].[1].[1].[1]:[Date].[All Date].[2005].[4].[12].[1]}'
MEMBER [Date].DateRange as 'Sum([DateSet])'

this is my query:

with
Set [DateSet] as
'{[Date].[All Date].[2005].[1].[1].[1]:[Date].[All Date].[2005].[4].[12].[1]}'
MEMBER [Date].DateRange as 'Sum([DateSet])'

SELECT Non Empty [Measures].allmembers ON COLUMNS,
nonemptycrossjoin ( [Program].[OperatorName].members, [DateSet]) on rows
from adv_small
where ([Date].DateRange)
here i get the duplicate dimensions across axes becuase of the date. so how
do i do that?
thanks

Hi

Is this what you need?

SELECT Non Empty [Measures].allmembers ON COLUMNS,
nonemptycrossjoin ( [Program].[OperatorName].members, {[Date].[All
Date].[2005], [Date].[All Date].[2006]) on rows
FROM adv_small

You get an error because of unsing Time Dimensins both on the rows and in
WHERE.

Vladimir Chtepa


"Ruby Nadler" <RubyNadler (AT) discussions (DOT) microsoft.com> schrieb im Newsbeitrag
news6495021-213D-4D13-865B-D9EDBB740B2F (AT) microsoft (DOT) com...

The problem you are running into is that you are defining members for a
dimension on 2 separate axes. The where clause is the background axes
and thus, should contain members from dimensions that are not on your
rows and columns. If you change your query to something like this

SELECT Non Empty [Measures].allmembers ON COLUMNS,
nonemptycrossjoin ( [Program].[OperatorName].members, {[DateSet],
[Date].DateRange}) on rows
from adv_small
where (Measures.DefaultMember)

You will get your set and your calculated member being displayed on the
rows correctly. The "{ }" notation is crucial because you need to
create a set in the NECJ.

HTH

V