[RESOLVED] MDX Query Problem

I'm using this MDX query.

Code:

---

SELECT [Region_State].[Region].Members  ON columns,
{[FY].[2010], [FY].[2011], [FY].[2012]} ON rows
FROM [RegionState]

---

Most of the time I run it, it returns the expected values. The problem is depending upon the data in the database when the cube is processed there are times when you end up without an [FY].[2012] dimension member. Of course when this happens you get hit with the error

Formula error – cannot find dimension member (“[FY].[2012]”)

To get around this I tried this MDX instead

Code:

---

with member [FY].[test] as 'IIF(VBA!ISERROR(STRTOVALUE("[FY].[2012]")),[FY].[],[FY].[2012])' 
SELECT [Region_State].[Region].Members  ON columns,
{[FY].[2010], [FY].[2011], [FY].[test]} ON rows
FROM [RegionState]

---

I was expecting the with member along with the Iff check to handle the problem but it doesn't.

Anyone have any suggestions on how to handle this?

The answer turns out being wrap the true part of the Iff in STRTOVALUE.

Code:

---

with member [FY].[test] as 'IIF(VBA!ISERROR(STRTOVALUE("[FY].[2012]")),[FY].[],STRTOVALUE("[FY].[2012]"))' 
SELECT [Region_State].[Region].Members  ON columns,
{[FY].[2010], [FY].[2011], [FY].[test]} ON rows
FROM [RegionState]

---