Hi,

Iam new to the PPS world. Iam using MDX query filter and I need to create a filter field using the [BusinessLine].[Department] dimension. I need to sort the display value of Department in descending order in the filter and Iam using the following

Order([BusinessLine].[Department],[BusinessLine].[Department],DESC) in the query window while creating the filter. But Iam getting 'No value found'! result.

Please let me know Iam doing any mistake in this

The second argument in ORDER should be either numeric or string expression.

Try using:

Order ( 
   [BuisnessLine].[Department].members,
   [BusinessLine].CurrentMember.Properties("Name"),
   DESC
)

Hi Umair,

Thanks for the reply

The department here is Attribute and BusinessLine is dimension in my cube.For other requirement(the department has to be sorted based on the creation date attribute) purpose I have kept department as composite key with other attribute called CREATE_DATE with named column as department

Order ( 
   [BuisnessLine].[Department].[DEPARTMENT],
   [BusinessLine].[CREATE_DATE].Properties("Name"),
   DESC
)

Iam just replacing the above query as it is in the Enter MDX formul? page

Is it correct.

Not correct.

If you need to sort by CREATE_DATE, you would need to add it as a property. Simple steps: User-Defined Member Properties (MDX)

Once the property is added, use:

Order ( 
   [BuisnessLine].[Department].members,
   [BusinessLine].currentmember.Properties("CREATE_DATE"),
   DESC
)

• Proposed as answer by 22_vl 18 hours 9 minutes ago

• Proposed as answer by 22_vl Friday, April 10, 2015 1:20 PM

• Proposed as answer by 22_vl Friday, April 10, 2015 1:20 PM

• Proposed as answer by 22_vl Friday, April 10, 2015 1:20 PM

• Proposed as answer by 22_vl Friday, April 10, 2015 1:20 PM

• Proposed as answer by 22_vl Friday, April 10, 2015 1:20 PM

• Proposed as answer by 22_vl Friday, April 10, 2015 1:20 PM

• Proposed as answer by 22_vl Friday, April 10, 2015 1:20 PM

• Proposed as answer by 22_vl Friday, April 10, 2015 1:20 PM

• Proposed as answer by 22_vl Friday, April 10, 2015 1:20 PM

• Proposed as answer by 22_vl Friday, April 10, 2015 1:20 PM

• Proposed as answer by 22_vl Friday, April 10, 2015 1:20 PM

• Proposed as answer by 22_vl Friday, April 10, 2015 1:20 PM

• Proposed as answer by 22_vl Friday, April 10, 2015 1:20 PM

• Proposed as answer by 22_vl Friday, April 10, 2015 1:20 PM

• Proposed as answer by 22_vl Friday, April 10, 2015 1:20 PM

• Proposed as answer by 22_vl Friday, April 10, 2015 1:20 PM

Hi,

It is working this way:

 
 Order
  (
  filter( descendants([Date].[Calendar].[Year].members ) ,[Measures].[Hectolitres]  )
   ,[Date].[Calendar].CurrentMember.Member_Key
   ,  DESC
  )
 

The filter is used to get only existing values.

Regards,

V.K.

• Edited by 22_vl 18 hours 2 minutes ago

Hi,

It is working this way:

 
 Order
  (
  filter( descendants([Date].[Calendar].[Year].members ) ,[Measures].[Hectolitres]  )
   ,[Date].[Calendar].CurrentMember.Member_Key
   ,  DESC
  )
 

The filter is used to get only existing values.

Regards,

V.K.

• Edited by 22_vl Friday, April 10, 2015 1:27 PM

Hi,

It is working this way:

 
 Order
  (
  filter( descendants([Date].[Calendar].[Year].members ) ,[Measures].[Hectolitres]  )
   ,[Date].[Calendar].CurrentMember.Member_Key
   ,  DESC
  )
 

The filter is used to get only existing values.

Regards,

V.K.

• Edited by 22_vl Friday, April 10, 2015 1:27 PM

Hi,

It is working this way:

 
 Order
  (
  filter( descendants([Date].[Calendar].[Year].members ) ,[Measures].[Hectolitres]  )
   ,[Date].[Calendar].CurrentMember.Member_Key
   ,  DESC
  )
 

The filter is used to get only existing values.

Regards,

V.K.

• Edited by 22_vl Friday, April 10, 2015 1:27 PM

Hi,

It is working this way:

 
 Order
  (
  filter( descendants([Date].[Calendar].[Year].members ) ,[Measures].[Hectolitres]  )
   ,[Date].[Calendar].CurrentMember.Member_Key
   ,  DESC
  )
 

The filter is used to get only existing values.

Regards,

V.K.

• Edited by 22_vl Friday, April 10, 2015 1:27 PM

Hi,

It is working this way:

 
 Order
  (
  filter( descendants([Date].[Calendar].[Year].members ) ,[Measures].[Hectolitres]  )
   ,[Date].[Calendar].CurrentMember.Member_Key
   ,  DESC
  )
 

The filter is used to get only existing values.

Regards,

V.K.

• Edited by 22_vl Friday, April 10, 2015 1:27 PM

Hi,

It is working this way:

 
 Order
  (
  filter( descendants([Date].[Calendar].[Year].members ) ,[Measures].[Hectolitres]  )
   ,[Date].[Calendar].CurrentMember.Member_Key
   ,  DESC
  )
 

The filter is used to get only existing values.

Regards,

V.K.

• Edited by 22_vl Friday, April 10, 2015 1:27 PM

Hi,

It is working this way:

 
 Order
  (
  filter( descendants([Date].[Calendar].[Year].members ) ,[Measures].[Hectolitres]  )
   ,[Date].[Calendar].CurrentMember.Member_Key
   ,  DESC
  )
 

The filter is used to get only existing values.

Regards,

V.K.

• Edited by 22_vl Friday, April 10, 2015 1:27 PM

Hi,

It is working this way:

 
 Order
  (
  filter( descendants([Date].[Calendar].[Year].members ) ,[Measures].[Hectolitres]  )
   ,[Date].[Calendar].CurrentMember.Member_Key
   ,  DESC
  )
 

The filter is used to get only existing values.

Regards,

V.K.

• Edited by 22_vl Friday, April 10, 2015 1:27 PM

Hi,

It is working this way:

 
 Order
  (
  filter( descendants([Date].[Calendar].[Year].members ) ,[Measures].[Hectolitres]  )
   ,[Date].[Calendar].CurrentMember.Member_Key
   ,  DESC
  )
 

The filter is used to get only existing values.

Regards,

V.K.

• Edited by 22_vl Friday, April 10, 2015 1:27 PM

Hi,

It is working this way:

 
 Order
  (
  filter( descendants([Date].[Calendar].[Year].members ) ,[Measures].[Hectolitres]  )
   ,[Date].[Calendar].CurrentMember.Member_Key
   ,  DESC
  )
 

The filter is used to get only existing values.

Regards,

V.K.

• Edited by 22_vl Friday, April 10, 2015 1:27 PM

Hi,

It is working this way:

 
 Order
  (
  filter( descendants([Date].[Calendar].[Year].members ) ,[Measures].[Hectolitres]  )
   ,[Date].[Calendar].CurrentMember.Member_Key
   ,  DESC
  )
 

The filter is used to get only existing values.

Regards,

V.K.

• Edited by 22_vl Friday, April 10, 2015 1:27 PM

Hi,

It is working this way:

 
 Order
  (
  filter( descendants([Date].[Calendar].[Year].members ) ,[Measures].[Hectolitres]  )
   ,[Date].[Calendar].CurrentMember.Member_Key
   ,  DESC
  )
 

The filter is used to get only existing values.

Regards,

V.K.

• Edited by 22_vl Friday, April 10, 2015 1:27 PM

Hi,

It is working this way:

 
 Order
  (
  filter( descendants([Date].[Calendar].[Year].members ) ,[Measures].[Hectolitres]  )
   ,[Date].[Calendar].CurrentMember.Member_Key
   ,  DESC
  )
 

The filter is used to get only existing values.

Regards,

V.K.

• Edited by 22_vl Friday, April 10, 2015 1:27 PM

Hi,

It is working this way:

 
 Order
  (
  filter( descendants([Date].[Calendar].[Year].members ) ,[Measures].[Hectolitres]  )
   ,[Date].[Calendar].CurrentMember.Member_Key
   ,  DESC
  )
 

The filter is used to get only existing values.

Regards,

V.K.

• Edited by 22_vl Friday, April 10, 2015 1:27 PM

Hi,

It is working this way:

 
 Order
  (
  filter( descendants([Date].[Calendar].[Year].members ) ,[Measures].[Hectolitres]  )
   ,[Date].[Calendar].CurrentMember.Member_Key
   ,  DESC
  )
 

The filter is used to get only existing values.

Regards,

V.K.

• Edited by 22_vl Friday, April 10, 2015 1:27 PM

Hi,

It is working this way:

 
 Order
  (
  filter( descendants([Date].[Calendar].[Year].members ) ,[Measures].[Hectolitres]  )
   ,[Date].[Calendar].CurrentMember.Member_Key
   ,  DESC
  )
 

The filter is used to get only existing values.

Regards,

V.K.

• Edited by 22_vl Friday, April 10, 2015 1:27 PM