My time dimension has two hierarchies, year-month-day and year-week-day. I
want to sum the sales ([measures].[sales]) from 1.1.<current year> to
<today>.

YTD() is dependent on a time dimension and would limit/extend the sum to
last day in selected member. I want my sum to be independent of time
dimension usage and base calculations on actual date the day the query is
issued.

Anyone who has the MDX syntax/solution for this?

Best regards
Einar N.

Einar,

Do you have access to the date passed in as the "to date"? If so, you could
write a custom date set and use that as your time slicer.

Assuming your date has the format YYYY-MM-DD:

/*
* Create a set of dates from current year + '-01-01' (Jan 01, cy) to
* date passed in (assuming current year).
*
*/
WITH SET [TimeSet] as
'{
StrToMember
(
"[Time].[HierarchyName].&[" &
CStr( DatePart("y", VBA!Now)) & "-" & "01-01]"
):
StrToMember
(
toDateParam
)
}'
SELECT
{ [Dimension].[Hierarchy].[Members] } ON COLUMNS,
{ [TimeSet], [Measures].[sales] } ON ROWS
FROM [yourCube]

I've used this method in reporting services with success. I'm sure Mosha or
one of the other gurus may have a better method for you. I'm a little weak
on the basic stuff (head, tail, descendants, etc)...


"Einar Næss" <einar.naess.remove (AT) this (DOT) hands.no> wrote