September 16, 2003 at 12:15 am
i have 4 date measures in my fact table, lets call it date1, date2, date3 and date4 due to sensitivity of data reasons.
I've created a time dimension as steve suggested and connected these date measures to it. It doesn't work, so I've created 4 time dimensions now, one for each time measure and this kinda works.
when i do a MDX query for each date dimension e.g. the following.
WITH
MEMBER [Measures].[Total Calculated]
AS 'Sum([date1].[1900].[July]:[date2].[2040].[June], [Measures].[Total Enrolments])'
SELECT
[Measures].AllMembers
ON COLUMNS
FROM [New Test]
the results are the same being 12500 for each date measure (date1,date2,date3,date4) dimension when this shouldn't be the case because date 3 should have 12500 results and date 1 should have like 635000 results. what have I done wrong, do fact tables act like joins in tables and have restricted my date.
Hope i explained that clearly.
Alan
September 19, 2003 at 8:00 am
This was removed by the editor as SPAM
September 19, 2003 at 1:53 pm
Try to do separated sums, like this:
WITH
MEMBER [Measures].[Total Date1]
AS 'Sum([date1].[1900].[July], [Measures].[Total Enrolments])', SOLVE_ORDER = 1
MEMBER [Measures].[Total Date2]
AS 'Sum([date2].[2040].[June], [Measures].[Total Enrolments])', SOLVE_ORDER = 1
MEMBER [Measures].[Total Calculated]
AS '[Measures].[Total Date1]+[Measures].[Total Date2]'
SELECT
[Measures].AllMembers
ON COLUMNS
FROM [New Test]
September 21, 2003 at 6:30 am
Alan,
Not sure if this helps but you could try something along the lines of
WITH
SET
[Good AND Pearl Stores]
AS
'FILTER(
Store.Members,
([Product].[Good], Measures.[Unit Sales]) > 0 AND
([Product].[Pearl], Measures.[Unit Sales]) > 0
)'
SELECT
DESCENDANTS([Time].[1997], [Quarter], SELF_AND_BEFORE)
ON COLUMNS,
[Good AND Pearl Stores]
ON ROWS
FROM
Sales
This was tekn stright from SQL Resource Kit. It has some useful hints on "real-world" uses of MDX. If you can you should grab copy.
HTH,
Steve.
Steve.
September 21, 2003 at 7:22 pm
Wow thanks guys!
i'll give them a go and get back to you to see how it goes.
yeah i'll check out the resource kit as well steve.
Thanks
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply