5

How can I filter data in MDX with current Year & Month?

What I did so far is:

WHERE strtomember{ [Time Dim].[FSCL YEAR].&["+Format(now(), "yyyy")+"], [Time Dim].[FSCL MONTH].&["+Format(now(), "m")+"] }

but it is not working.

Aditya
  • 2,299
  • 5
  • 32
  • 54
  • What is the result you get for the first query? What is the result of the second Query? Is `[Measures].[Measure]` a calculated measure? If so, could you share its definition? – FrankPl Apr 25 '14 at 10:19
  • Nothing is calculated here. All are simple Measures & dimensions. I apologize as I can not share actual MDX here. – Aditya Apr 25 '14 at 10:27
  • @Adita As you are using `[Dimension].[Dim].[Dim].ALLMEMBERS` and not `[Dimension].[Dim].[Dim].MEMBERS`, I would assume that the difference is in the calculated measures of this hierarchy? Otherwise, if no calculated members are involved, then the results should match. – FrankPl Apr 25 '14 at 10:31
  • Wait, i will take another try, thanks for suggestion – Aditya Apr 25 '14 at 10:48
  • Yes, you are right. Its working better now. – Aditya Apr 25 '14 at 10:52
  • Please note that question is edited here, so you may found comments irrelevant of question. – Aditya Apr 25 '14 at 14:01

1 Answers1

5

StrToMember is a function that takes one argument, which is a string. You seem to try to pass it a set, as you are using curly braces. The correct syntax would be:

WHERE ( 
      strtomember("[Time Dim].[FSCL YEAR].&[" + Format(now(), "yyyy") + "]"),
      strtomember("[Time Dim].[FSCL MONTH].&[" + Format(now(), "m") + "]")
      )

The argument to StrToMember in both cases ia a string concatenated from fix strings like "[Time Dim].[FSCL YEAR].&[" and "]", as well as the result of the Format function.

Aditya
  • 2,299
  • 5
  • 32
  • 54
FrankPl
  • 13,205
  • 2
  • 14
  • 40
  • 1
    Tuples or set must use same hierarchy in the function error for where clause. – Aditya Apr 25 '14 at 10:46
  • @Aditya Oh, I see. They are from different hierarchies. hence you must put them into a tuple in the `WHERE` condition, not in a set. I corrected that. – FrankPl Apr 25 '14 at 11:01
  • I do not see where it would make sense. `Generate` loops over a set and either returns a string containing the concatenation of the strings returned from each loop iteration, or the union of the sets returned from each loop iteration. Where d you see the set to loop over? Where do you see a string or set result? I only see a tuple result. – FrankPl Apr 25 '14 at 11:07
  • Your error message states "in the function error for where clause". I see no call to the function "Error" in my code. Probably there is something wrong in your adaptation of my code to your case. – FrankPl Apr 25 '14 at 11:08
  • Hey really sorry, I used old one, its now working but no measures are displayed, null only. Probably Month formatting I have to check. – Aditya Apr 25 '14 at 11:10
  • My months are like 1, 2, 3 so on including Unknown – Aditya Apr 25 '14 at 11:15
  • Format(now(), "m") , its "MM". Working proper now. – Aditya Apr 25 '14 at 13:32