Wednesday, October 6, 2010

MDX Date dimension in Descending Order

Scenario:
The client is refusing to make the change to the filters to allow more than 500 entries due to concern that the performance will be degraded for all of their performancepoint dashboards.  Would it be possible to change the date filters so that the most recent dates appear first in the list?  This would cause the older items to be truncated rather than the most recent.

select
[Measures].[Some Units] on 0,
ORDER ( 
nonempty(
[time].[year-quarter-month-week-date].members,[Measures].[Some Units])
,[time].[year-quarter-month-week-date].CurrentMember.MemberValue , desc) on 1
from [MyCube]

This would retain the hierarchies and sort the date filters with most recent date first. So that the past values would be automatically filtered out (more than 500 ones).

No need to change the web.config!

The date dimension is SSAS business intelligence time dimension (auto generated).  

Reference:

Changing the limit on the number of items returned in a filter

No comments:

Post a Comment