Saturday, February 25, 2012

Mdx Query doubt about years?

Hi,
The below query which has hardcoded year ending values works fine.

What i want to do is if I pass on last 3 years or last 4 years etc as parameter along with current year it should bring back the results accordingly for the required years.

with
member [YearEnding].[CurrYear] as '[YearEnding].[All YearEnding].[2005]'
select
non empty {[Product].Children, Product.[All Product]} on columns,
non empty Filter({ {[Measures].[stock Quantity Ola] } * { [YearEnding].Children} }, (([YearEnding] = [YearEnding].[2005]) or ([YearEnding] = [YearEnding].[2004]) or ([YearEnding] = [YearEnding].[2003])) ) on rows
from Stock
Help much appreciated
Many Thanks

VeeraFirstly, you could run into problems using '=' operator to test for dimension members - this blog entry by Mosha Pasumanskyexplains why:

http://sqljunkies.com/WebLog/mosha/archive/2004/11/4.aspx
>>
Comparing members in MDX
...
>>

Since measures are not on columns, this looks like OLE DB Provider for OLAP, for which the MDX query has to be converted to an expression, to add parameters. Assuming a multi-select parameter like "SelectedYears", with selected string values like "[YearEnding].[2003]", etc, then the filter condition can be built using Join():

+ "Count(Intersect({[YearEnding].CurrentMember}, {"
+ Join(Parameters!SelectedYears.Value, ",")
+ "})) = 1) on rows from Stock"

No comments:

Post a Comment