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 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy