Hello everyone,

Here my problem, I have a customer who want to have YTD on columns, and
measures on row..

Here an example of want I would like to achieve

WITH MEMBER [TIME].[1998 YTD] AS 'ClosingPeriod(Month, [Time].[1998])'
MEMBER [TIME].[1997 YTD] AS
'ParallelPeriod(Year,1,ClosingPeriod(Month, [Time].[1998]))'

SELECT {[TIME].[1998 YTD],[TIME].[1997 YTD]} ON ROWS, Measures.members on
columns from sales


Currently the query return only the value for the last month of 1998, and
the same month in 1997. But I would like to have the same grid but with the
1998 to 'ClosingPeriod(Month, [Time].[1998]) and the same period last year.

If I try with

WITH MEMBER [TIME].[1998 YTD] AS 'YTD(ClosingPeriod(Month, [Time].[1998]))'
MEMBER [TIME].[1997 YTD] AS
'YTD(ParallelPeriod(Year,1,ClosingPeriod(Month, [Time].[1998])))'

SELECT {[TIME].[1998 YTD],[TIME].[1997 YTD]} ON ROWS, Measures.members on
columns from sales

i got the error... (hint you can copy paste messagebox since windows 2000)

---------------------------
MDX Sample Application
---------------------------
Unable to open cellset

Formula error - cannot convert set to Expression - in the PeriodsToDate
function


---------------------------
OK
---------------------------

Thanks

Best regards,

Patrice

YTD returns a set of Time members
You need to sum over that set to return a value

'Sum(YTD(ClosingPeriod(Month, [Time].[1998])))

HTH
Bria
www.geocities.com/brianaltmann/olap.htm

Geez ... I was sure that i try it.

Thanks !
"Brian Altmann" <anonymous (AT) discussions (DOT) microsoft.com> wrote

If I understand your question, you would like to have YTD as a member of one
dimensions
and as you select the measure the YTD column would give you the YTD value
for that measure?

If so, then you can create so called SHELL dimension.

If yes I can send you some instructions how to do that.


Peace,
Andrej



"Patrice Lamarche" <Patrice_lamarche (AT) gco (DOT) ca.nospam> wrote

Thanks, yes i would like to have the way to create a SHELL dimension

Patrice
"Andrej Hudoklin" <andrej.hudoklin (AT) add (DOT) si> wrote