param prob

hi ,
I have created 2 datasets. my first dataset quary parameter is BU. query is like following.

WITH

MEMBER [Measures].[Amount] AS 'IIF(ISEMPTY([Measures].[Amount Usd]),0,[Measures].[Amount Usd])'
MEMBER [Measures].[Description] AS '[Ledger—AccountCode].CurrentMember.Name'

SET [FilteredBUList] AS strtoset(@.BU)

SELECT
{[Measures].[Description],[Measures].[Amount]} ON COLUMNS,
[FilteredBUList] on rows

FROM Profitability

my second dataset query is like following,

SET [FilteredBUList] AS strtoset(@.BU)

SET [customtimeset] AS.....

SELECT
{[Measures].[Description],[Measures].[Amount]} ON COLUMNS,
intersect( [FilteredBUList],...) on rows

FROM Profitability

i need to map previous query BU parameter to the second dataset parameter. pls tel m the proper way.
cant access report parameter in this way from different dataset.
WITH

MEMBER [Measures].[Amount] AS 'IIF(ISEMPTY([Measures].[Amount Usd]),0,[Measures].[Amount Usd])'
MEMBER [Measures].[Description] AS '[Ledger—AccountCode].CurrentMember.Name'

SET [FilteredBUList] AS strtotuple(@.BU)

SELECT
{[Measures].[Description],[Measures].[Amount]} ON COLUMNS,
[FilteredBUList] on rows

FROM Profitability

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]