Generating range of months using multi-level hierarchies

user_8785036 0
Viewed 36

I want to filter the months of [ComptaEcriture Date] by selecting only the months from [ComptaPlanId].[ComptaDateDebut] to [ComptaPlanId].[ComptaDateFin], but since [ComptaDateDebut] and [ComptaDateFin] are not from the same level and bot are not from the same dimension as [ComptaEcriture Date].[ComptaEcriture Date].[Month], I don't know how to achieve that.

If I could generate a range of months that would be great. My dimensions are as follows:

My dimensions

iccube-reporting iccube
1 Answers

Assuming you're testing that PlanId is no the All member, you can use isAll MDX+ function for this.

For the set, we will combine the Filter function with a Declared function, even though we could put it all the code in the filter. It looks as :

WITH
  FUNCTION inRange(Value _date,Value _start, Value _end) AS _start <= _date AND _date <= _end
  SET myDates as  Filter( [Date].[Date].[Month] as t, inRange(t.current.key, DateTime(2015,6,1), DateTime(2017,1,1)  ) )
SELECT
 myDates on 0
FROM [Cube]

And using the compact and faster version :

SELECT
 Filter( [Date].[Date].[Month] as t, DateTime(2015,6,1) <= t.current.key AND t.current.key <= DateTime(2017,1,1) ) on 0
FROM [Cube]

Using the members :

WITH
  FUNCTION inRange(Value _date,Value _start, Value _end) AS _start <= _date AND _date <= _end
  SET myDates as  Filter( [Date].[Date].[Month] as t, 
                    inRange(t.current.key, [ComptaDateDebut].currentmember.key, [ComptaDateFin].currentmember.key  ) 
                  )
SELECT
 myDates on 0
FROM [Cube]

You can use contextMember instead of currentMember that check also in the slicer (FILTER BY or subselect)

user_7637900
Related