Wednesday, March 28, 2012

parameter modification within mdx

Is it possible to modify a parameter value within a dataset using mdx?

lets say the param values of @.bucket = 6

how would you go about writing @.bucket + 1

so @.bucket now would equal 7

is this possible?

Your options are:

1. Call an SSAS stored procedure from the generated MDX statement, e.g.

SELECT NON EMPTY { [Measures].[Profit] } ON COLUMNS, NON EMPTY { ([Product].[Product].[Product].ALLMEMBERS ) } DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS FROM ( SELECT ( STRTOSET(MyAssemblyName.MyStoredProcedureName(@.ProductProduct), CONSTRAINED) ) ON COLUMNS FROM [RPM]) CELL PROPERTIES VALUE, BACK_COLOR, FORE_COLOR, FORMATTED_VALUE, FORMAT_STRING, FONT_NAME, FONT_SIZE, FONT_FLAGS

2. If you want to use an SSRS code-behind function, you need to use an expression-based query, e.g.:

="SELECT NON EMPTY { [Measures].[Profit] } ON COLUMNS, NON EMPTY { ([Product].[Product].[Product].ALLMEMBERS ) } DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS FROM ( SELECT ( STRTOSET(" & Code.SomeProcedure(Parameters!Product.Value & "), CONSTRAINED) ) ON COLUMNS FROM [RPM]) CELL PROPERTIES VALUE, BACK_COLOR, FORE_COLOR, FORMATTED_VALUE, FORMAT_STRING, FONT_NAME, FONT_SIZE, FONT_FLAGS"

No comments:

Post a Comment