April 28, 2010 at 11:20 am
I am having trouble counting the existing descendants within my time hierarchy when I apply a filter to the dataset in EXCEL 2007 or in the VS cube browser.
with
member [measures].[test2] as
count(existing(descendants([Dim Date Time].[Calendar Year - Calendar Quarter - Calendar Month No - Full Date Alternate Key - Holiday Flag].[Full Date Alternate Key],1))
)
select [measures].[test2] on 0
from [FIELD_Reporting]
where
(
[Dim Date Time].[Calendar Year - Calendar Quarter - Calendar Month No - Full Date Alternate Key - Holiday Flag].[Calendar Year].&[2009].&[1]
)
This query returns 31 days which is correct... but when I create this as a calculated member in the cube and apply a filter on the time hierarchy similar to the where clause the result shows all days in the entire hierarchy.
How can I modify the calculated member to be properly affected by the filters in excel?
May 2, 2010 at 5:18 pm
Hi Jason,
I don't know whether or not you've figured this out, but i think your MDX can and should do without the 'Existing'-piece. If your goal is to calculate the dates within the selected timeframe,just leave the Existing-pice out. Consider this, based on the AW cube:
with member [measures].[date count] as
descendants([Order Date].[Calendar].currentmember,[Order Date].[Calendar].[Date]).count
select {[measures].[date count]} on 0
from [Adventure Works DW]
where [Order Date].[Calendar].[Month].&[2003]&[1]
Works fine also when added to the cube.
Kind regards,
Cees
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply