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"

ParallelPeriod: Same Day Last Month in long months

I am using the following MDX query to return same day last month values:

WITH

MEMBER [Measures].[Same Day Last Month] AS

'(ParallelPeriod([Reporting Date].[Year - Quarter - Month - Date].[Month],1),[Measures].[Active Account Indicator])'

SELECT

{[Measures].[Active Account Indicator],[Measures].[Same Day Last Month]} ON COLUMNS,

NON EMPTY {[Reporting Date].[Date].Members} ON ROWS

FROM [Financial]

This works for most days. However, for the last day of the month this cannot work when the current month has more days than the previous month. For example, on March 29, 30 and 31, the query will return NULL for [Same Day Last Month], since February 29, 30, 31 is an invalid date.

I am sure others have run into the same problem. What is the best way to solve this?

I'm grateful for any insights.

Dan

This could perhaps be more efficiently handled in scoped MDX script; but a recursive approach like this should work (except in the first month):

WITH

MEMBER [Measures].[Same Day Last Month] AS

iif(ParallelPeriod([Reporting Date].[Year - Quarter - Month - Date].[Month]) is null,

([Reporting Date].[Year - Quarter - Month - Date].PrevMember,

[Measures].[Same Day Last Month]),

(ParallelPeriod([Reporting Date].[Year - Quarter - Month - Date].[Month]),

[Measures].[Active Account Indicator]))

|||

Thank you, Deepak. I appreciate your response.

I believe I would have to use nested recursive statements for March 31. One month ago would indicate February 31, previous day would be February 30, and so forth until a valid date is reached.

However, this lead me to another solution which worked and follows our business rules: The last day of any month always contains values. Therefore, I was able to solve my problem using the following approach, employing the IsLeaf(), and the ClosingPeriod() function:

WITH

MEMBER [Measures].[Same Day Last Month] AS

'IIf(

--valtest

IsLeaf(

ParallelPeriod(

[Reporting Date].[Year - Quarter - Month - Date].[Month],

1

)

,

--valtrue

(

ParallelPeriod(

[Reporting Date].[Year - Quarter - Month - Date].[Month],

1

),

[Measures].[Active Account Indicator]

)

,

--valfalse

(

ClosingPeriod(

[Reporting Date].[Year - Quarter - Month - Date].[Date],

ParallelPeriod(

[Reporting Date].[Year - Quarter - Month - Date].[Month],

1

[Reporting Date].[Year - Quarter - Month - Date].CurrentMember.Parent

)

),

[Measures].[Active Account Indicator]

)

) --End IIf

'

SELECT

{[Measures].[Active Account Indicator],[Measures].[Same Day Last Month]} ON COLUMNS,

NON EMPTY {[Reporting Date].[Date].Members} ON ROWS

FROM [Financial]

ParallelPeriod MDX function not working

I created a calculated measure called [Previous Year Percent Rejected]. I used the following expression:

(ParallelPeriod([Year],1,[Time].[Time Hierarchy].CurrentMember),[Measures].[Percent Rejected])

The syntax checks out fine. However, when I try to use the calculation, I get an error message -- with a #VALUE! in the column.

The error message reads " The parallel period expression expects a level expression for the argument. A hierarchy expression was used."

I tried many different variations but I'm always receiving the same message. Any suggestions?

David

If you're using AS 2005, the problem could be that there is also an attribute hierarchy associated with [Year]. So, you could try fully specifying the [Year] level, like:

(ParallelPeriod([Time].[Time Hierarchy].[Year],1,[Time].[Time Hierarchy].CurrentMember),[Measures].[Percent Rejected])

|||

Thank you Deepak. Your solution fixed the problem.

David

Paging in MDX

Hi

I am new to mdx.i want to know can we implement paging in mdx query? is there any function in mdx so that i can return resultset based on count.i am using topcount function to return 10 rows.but i want to return result for next top 10 and so on.. in same query. like we do in paging.

Thanks in advance

There are probably ways of doing this, one that comes to mind would be to use a combination of topcount and tail

eg. page1... TopCount( <set>, <expression>, 10)

page2... TopCount( <set>, <expression>, 20).tail(10)