Dynamic ParallelPeriode

Hi,

with this MDX expression as a calculated member in my cube

100 / [Measures].[Sales Volume KG] * (parallelperiod([Date].[Month], 12), [Measures].[Sales Volume KG])

I get the difference of the Sales Volume from this month and this month last year. But how can I make it dynamic for drilldown in the Time Dimension, so that also year, semester, Quarters and so on are supported? I will compare this year - last year, this semester - same semester last year, this quarter - same quarter last year and so on.

Thanks

Hans

Hi Hans,

Why doesn't ParallelPeriod(Year ..) meet your needs - could you explain in the context of this Adventure Works query?

>>

select

{[Measures].[Sales Amount]} on 0,

Generate(Ascendants([Date].[Calendar].[Date].&[800]),

{[Date].[Calendar].CurrentMember,

ParallelPeriod([Date].[Calendar].[Calendar Year],

1, [Date].[Calendar].CurrentMember)}) on 1

from [Adventure Works]

-

Sales Amount
September 8, 2003 $36,027.71
September 8, 2002 $18,755.92
September 2003 $5,057,832.17
September 2002 $3,235,826.19
Q3 CY 2003 $13,670,536.57
Q3 CY 2002 $10,277,073.06
H2 CY 2003 $26,955,981.04
H2 CY 2002 $18,646,056.13
CY 2003 $41,993,729.72
CY 2002 $30,674,773.18
All Periods $109,809,274.20

>>

|||

Hi Deepak,

Thanks for your suggestions. Your code gave me the idea to specify the Dimension in full, so I name it [Date].[Periode - Year].[Year] and not only [Date].[Year] and now it works. But it works only with the [Periode - Year] Hierarchy. How can I change it, to work with all my Time hierarchies ([Date].[Periode - Year], [Date].[Periode - Week], [Date].[Periode - Reporting])? I tried

IIF( [Measures].[Sales Volume KG] = 0, 0, 100 - (100 / [Measures].[Sales Volume KG] * (parallelperiod([Date].[Periode - Year].[Year], 1) * parallelperiod([Date].[Periode - Week].[Year], 1) * parallelperiod([Date].[Periode - Reporting].[Year], 1), [Measures].[Sales Volume KG])) )

but that doesn't work. You gave me this approach just a year ago for builing YTD sums just like

IIF( sum(YTD([Date].[Periode - Year].CurrentMember) * YTD([Date].[Periode - Week].CurrentMember) * YTD([Date].[Periode - Reporting].CurrentMember),[Measures].[Sales Volume M2]) = 0, 0, sum(YTD([Date].[Periode - Year].CurrentMember) * YTD([Date].[Periode - Week].CurrentMember) * YTD([Date].[Periode - Reporting].CurrentMember),[Measures].[Commission To Market]) / sum(YTD([Date].[Periode - Year].CurrentMember) * YTD([Date].[Periode - Week].CurrentMember) * YTD([Date].[Periode - Reporting].CurrentMember),[Measures].[Sales Volume M2]))

which work fine, but this doesn't work here. How can I do it here?

Thanks

Hans

|||

Hi Hans,

With ParallelPeriod() a different approach is needed, which would depend on the attributes in the 3 hierarchies and the attribute relations (an entry in Mosha's blog shows some examples). If all the hierarchies align with the beginning of the time dimension (which isn't true for Adventure Works Calendar hierarchy), then a cube MDX script assignment like this might work - otherwise, you could describe the attributes and hierarchies in more detail:

([Measure].[SalesGrowth], [Date].[Date].Members) =

IIF( [Measures].[Sales Volume KG] = 0, 0, 100 - (100 / [Measures].[Sales Volume KG]

* (parallelperiod([Date].[Periode - Year].[Year]), [Measures].[Sales Volume KG])) );

Dynamic measures

Hi,

I am new to mdx. Based on the requirement, I need to dynamically loaded up a column of measures depending on the selection of a parameter.

The parameter values has, Actual, Budget, Target

For the one field, base on the above parameter, will select,

if the value for the parameter is Actual, then we will have only a column of Actual values

if it's Budget, then we will have only a column of Budget values

if it's Target, then we will have only a column of Target values

How should I write the mdx query for this?

I'm really desperate for the answer.

Thanks a lot for your help.

The recommended way to do this is to create a seperate scenario dimension that jsut contains the members Actual, Budget, and Target. Then depending on which of theses members you have selected in your query, you will see the appropriate values in your measures.sql

Dynamic measures

Hi,

I am new to mdx. Based on the requirement, I need to dynamically loaded up a column of measures depending on the selection of a parameter.

The parameter values has, Actual, Budget, Target

For the one field, base on the above parameter, will select,

if the value for the parameter is Actual, then we will have only a column of Actual values

if it's Budget, then we will have only a column of Budget values

if it's Target, then we will have only a column of Target values

How should I write the mdx query for this?

I'm really desperate for the answer.

Thanks a lot for your help.

The recommended way to do this is to create a seperate scenario dimension that jsut contains the members Actual, Budget, and Target. Then depending on which of theses members you have selected in your query, you will see the appropriate values in your measures.

Dynamic MDX Query

I am working on SRS 2005 report and need to calculate measures based on parameters. The new great feature of SRS2005 which allows to use parameters in MDX query unfortunately does not work in inner query. For example I am building 4 time sets based on specified date ranges and then calculate summary for each time set. So query looks like this:

With

Set [Time1] AS '@.ParameterTime1'

...

Set [Time4] AS '@.ParameterTime4'

member [Measures].[Count1] as 'Sum ( { [Time1] } ,[Measures].[Count]) '

...

member [Measures].[Count4] as 'Sum ( { [Time4] } ,[Measures].[Count]) '

Select

{

[Measures].[Count1, ... , [Measures].[Count4]

} on Columns,

{

...

} on Rows

From [Cube]

It would be great if we still had dynamic queries supported with MDX which was available in SRS 2000. In that case I could build a query string and incorporate paramters in SET section. For example this type of query would not work in SRS 2005. I'm getting an error.

="With Set [Time1] AS ' " & Parameter!ParameterTime1 &"' ....."

If someone found any solution of using complex MDX queries with parameters, please respond. I am searching the web for a few days and no luck.

Thanks

Olga

Hi,

You can use parameters in the calculated members or sets, but you need to remove the character '.

With
Set [Time1] AS @.ParameterTime1
...
Set [Time4] AS @.ParameterTime4
member [Measures].[Count1] as 'Sum ( { [Time1] } ,[Measures].[Count]) '
...
member [Measures].[Count4] as 'Sum ( { [Time4] } ,[Measures].[Count]) '
Select
{
[Measures].[Count1, ... , [Measures].[Count4]
} on Columns,
{
...
} on Rows
From [Cube]

Regards,
Telmo Moreira|||

Telmo,

Thanks a lot ! You saved my day.

The query with parameters works fine.

Olga

|||

Hi,

I have a similar requirement but the number of sets i need to create is not fixed and needs to be dynamic. I will know the number of date ranges i need to filter on only at run time and i dont want to build an MDX query at runtime. Is there a simpler way of achieveing this by passing all the date ranges I require as a single parameter?

Thanks in Advance,

Arun

sql

Dynamic MDX Query

I am working on SRS 2005 report and need to calculate measures based on parameters. The new great feature of SRS2005 which allows to use parameters in MDX query unfortunately does not work in inner query. For example I am building 4 time sets based on specified date ranges and then calculate summary for each time set. So query looks like this:

With

Set [Time1] AS '@.ParameterTime1'

...

Set [Time4] AS '@.ParameterTime4'

member [Measures].[Count1] as 'Sum ( { [Time1] } ,[Measures].[Count]) '

...

member [Measures].[Count4] as 'Sum ( { [Time4] } ,[Measures].[Count]) '

Select

{

[Measures].[Count1, ... , [Measures].[Count4]

} on Columns,

{

...

} on Rows

From [Cube]

It would be great if we still had dynamic queries supported with MDX which was available in SRS 2000. In that case I could build a query string and incorporate paramters in SET section. For example this type of query would not work in SRS 2005. I'm getting an error.

="With Set [Time1] AS ' " & Parameter!ParameterTime1 &"' ....."

If someone found any solution of using complex MDX queries with parameters, please respond. I am searching the web for a few days and no luck.

Thanks

Olga

Hi,

You can use parameters in the calculated members or sets, but you need to remove the character '.

With
Set [Time1] AS @.ParameterTime1
...
Set [Time4] AS @.ParameterTime4
member [Measures].[Count1] as 'Sum ( { [Time1] } ,[Measures].[Count]) '
...
member [Measures].[Count4] as 'Sum ( { [Time4] } ,[Measures].[Count]) '
Select
{
[Measures].[Count1, ... , [Measures].[Count4]
} on Columns,
{
...
} on Rows
From [Cube]

Regards,
Telmo Moreira

|||

Telmo,

Thanks a lot ! You saved my day.

The query with parameters works fine.

Olga

|||

Hi,

I have a similar requirement but the number of sets i need to create is not fixed and needs to be dynamic. I will know the number of date ranges i need to filter on only at run time and i dont want to build an MDX query at runtime. Is there a simpler way of achieveing this by passing all the date ranges I require as a single parameter?

Thanks in Advance,

Arun

|||Hi,
is it possible to use a parameter as mentioned below?

select [Measures].[Internet Sales Amount] on 0,
@.ProductCategories on 1
from [Adventure Works]
And the MDX it would actually run against Analysis Services would be:
select [Measures].[Internet Sales Amount] on 0,
{[Product].[Category].&[1],[Product].[Category].&[2]} on 1
from [Adventure Works]