2

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

Joker
  • 67
  • 4

1 Answers1

2

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)

ic3
  • 7,917
  • 14
  • 67
  • 115
  • Thanks, it's working except when I want to use [ComptaDateDebut].currentmember.key. When I select a specific comptaPlanId (through a MDX fiilter in the report) I can't retrieve correctly the corresponding ComptaDateDebut and ComptaDateFin members in another MDX filter of the report. – Joker Nov 24 '17 at 09:28
  • Try using ContextMember instead of currentMember (check documentation) – ic3 Nov 24 '17 at 13:23