Dear all

I'm new to mdx and trying to create a querry to use in ssrs. I use this
querry to populate a dataset in SSRS that is used for a report which contains
a Table (Two descriptive columns, 3 measures columns)

My problem now is, that the requirement says that the measure columns should
have different time spans. I know how to apply one timespan to all of the
measures, but I do not know how to apply 2 timespans.

The querry looks something like this:

with
Member Trans as
'IIF(ISEMPTY([Measures].[T]),0,[Measures].[T])'

MEMBER Budget as
'IIF(ISEMPTY([Measures].[E]),0,[Measures].[E])'

MEMBER WaAu as
IIF(ISEMPTY([Measures].[W]),0,[Measures].[W])

SELECT

NON EMPTY { Trans, Budget, WaAu } ON COLUMNS,

NON EMPTY { ([Product].[ProductCategoryName].ALLMEMBERS *
[Product].[ProductGroupName].ALLMEMBERS
) } ON ROWS

FROM ( SELECT ( STRTOMEMBER(@From,constrained) :
STRTOMEMBER(@To,constrained)) ON COLUMNS
FROM [OdyFirstCube]))

Can you help me with this issue? Thanks in advance

Regards,

Marc

Hello Marc,

Thank you for using Microsoft MSDN newsgroup.

If I understand issue correctly, you'd like to display measures aggregated
between 2 timespan. If I'm off-base, please let me know.

You may want check if the following MDX meets your requirement. You could
use calculated member on Time demension and uses it where clause.


WITH MEMBER [Time].[Total] AS 'Sum({[Time].[2006].[1]: [Time].[2006].[6]})'


SELECT {....} ON COLUMNS, NON EMPTY {....} ON ROWS FROM TestCube where (
[Time].[Total])

Please let me know if this helps and I look forward to your reply.

Thanks & Regards,

Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support

When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.

================================================== ===



This posting is provided "AS IS" with no warranties, and confers no rights.

Hi Peter

Thanks for your reply. I did not make myself clear enough - sorry for that:

I want to display measures aggregated between two different timespans:
the measures [Trans] and [Budget] with timespan 1 e.g. [Time].[2006].[1]:
[Time].[2006].[6]

and the measure [WaAu] with timespan 2 e.g. [Time].[2005].[1]:
[Time].[2005].[6]

I started developing something like this:

MEMBER Trans AS
{[Measures].[T],sum(STRTOMEMBER(@FromDatumGeschaeftsjahr, CONSTRAINED) :
STRTOMEMBER(@ToDatumGeschaeftsjahr, CONSTRAINED))}

and wanted to apply the ParallelPeriod function for the other measure.
Unfortunately this way does not work and I receive NULL values for the mdx
expression written above.

What I'm confused about is that this measure definition works well:

MEMBER Trans AS
([Measures].[T], STRTOMEMBER(@FromDatumGeschaeftsjahr, CONSTRAINED))

Thanks for your help,

Regards,

Marc

Hello Marc,

Thank you for your update. I agree with you that you shall use a cacluated
measure directly under the sitaution.

Sum function returns a numeric value and it cannto be used as member
grequired in your expression.

You may want to try the something like following expression to see if it's
you want:

MEMBER Trans AS

SUM (STRTOMEMBER(@FromDatumGeschaeftsjahr, CONSTRAINED) :
STRTOMEMBER(@ToDatumGeschaeftsjahr, CONSTRAINED), [Measures].[T])

If you have further questions, please feel free to let us know. I look
forward to your update.

Best Regards,

Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support

When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.

================================================== ===



This posting is provided "AS IS" with no warranties, and confers no rights.

Hello Peter

Thank you! Your expression worked very well.

Best Regards, Marc

Hello Roger,

Welcome!

Best Regards,

Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support

When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.

================================================== ===

This posting is provided "AS IS" with no warranties, and confers no rights.