I have a member set with the 12 months of the current year. I want to derive the current reporting month to determine actuals vs forecasted figures. I am using extract(month,current_date)-2 to give me an index to use in my data item defined as item([Months in Current Year],[Current Month Number]). The [Current Month Number] is calculating correctly to 10 but the data item is coming out as Jan 08 (item 0 in the set). If I replace the [Current Month] with a hardcoded number it correctly returns the month in question. Can anybody explain what is goign wrong?
What number are you using for hard coding and what number your current month expression returns?
[Curent Month Number] = extract(month,current_date)-2 = 10
[Months in Current Year] = Jan 08, Feb 08, Mar 08, Apr 08 etc
item([Months in Current Year],[Current Month Number]) = Jan 08
item([Months in Current Year],10) = Nov 08
It's a Powercube OLAP source.
Usually in cube source, you have to use caption() function around the data item you want to compare against a static number.
That results in an invalid coercion which at least confirms that the [Current Month Number] data item is being resolved to a value.
It's the memberset of months that is coming from the cube and they seem to be building a set fine. If I output the set I get all 12 months ok and if I hardcode an index to the item function it picks up the right month. Just doesn't want to work correctly with the extracted month number for some reason despite it showing the right value when output itself. ???