MS SQL Server OLAP - ClosingPeriod to slice more than one dimension

Author

ClosingPeriod to slice more than one dimension

Mangorind

2005-11-21, 1:23 pm

Hi,

I am having a difficulty to view the Measures: OS End Month, for
dimensions more than one.

Single Dimension: Ok no problem on this
([Measures].[Os End Month], ClosingPeriod (
& #91;DIM_Account_Date
_Year].[Account The Month],
& #91;DIM_Account_Date
_Year].CurrentMember)

Mutliple Dimension:
([Measures].[Os End Month], ClosingPeriod (
& #91;DIM_Account_Date
_Year].[Account The Month],
& #91;DIM_Account_Date
_Year]. CurrentMember),Closi
ngPeriod (
[DIM_UW_Year].[Uw Year], [DIM_UW_Year].CurrentMember))

By using the above statement, I can only view the amount if both
dimensions are selected together.

My intention: Assuming I have THREE Dimensions, I would like have the
flexibility on this measure ([Measures].[Os End Month]) to view in one
or both or three of the dimensions.

Kindly need advice for the issue.

Thanks

regards,
JW

Darren Gosbell

2005-11-21, 8:24 pm

If you are using AS2k5, then you could use code like the following to
see if the current member for each dimension is the at the all level. If
you don't have an all level you can alter the check to test if it is at
the top level using something like
& #91;DIM_Account_Date
_Year].CurrentMember.Level.Ordinal = 0

([Measures].[Os End Month], IIF(& #91;DIM_Account_Date
_Year].CurrentMember IS
& #91;DIM_Account_Date
_Year].[All DIM_Account_Date_Yea
r] ,
& #91;DIM_Account_Date
_Year].[All DIM_Account_Date_Yea
r] ,ClosingPeriod (
& #91;DIM_Account_Date
_Year].[Account The Month],
& #91;DIM_Account_Date
_Year].CurrentMember),
IIF([DIM_UW_Year].CurrentMember IS [DIM_UW_Year].[All DIM_UW_YEAR] ,
[DIM_UW_Year].[All DIM_UW_YEAR], ClosingPeriod (
[DIM_UW_Year].[Uw Year], [DIM_UW_Year].CurrentMember))

If you are using AS2k it gets a bit messier as the iif function in AS2k
can only return numbers or strings (in AS2k5 iif can return any valid
MDX expression) so you would probably have to use member names as a bit
of a hack.

([Measures].[Os End Month], StrToMember(IIF
(& #91;DIM_Account_Date
_Year].CurrentMember IS & #91;DIM_Account_Date
_Year].[All
DIM_Account_Date_Yea
r] , & #91;DIM_Account_Date
_Year].[All
DIM_Account_Date_Yea
r].UniqueName ,ClosingPeriod (
& #91;DIM_Account_Date
_Year].[Account The Month],
& #91;DIM_Account_Date
_Year].CurrentMember).UniqueName)), StrToMember(
IIF([DIM_UW_Year].CurrentMember IS [DIM_UW_Year].[All DIM_UW_YEAR] ,
[DIM_UW_Year].[All DIM_UW_YEAR].UniqueName, ClosingPeriod (
[DIM_UW_Year].[Uw Year], [DIM_UW_Year].CurrentMember).UniqueName))

HTH

--
Regards
Darren Gosbell [MCSD]
Blog: http://www.geekswithblogs.net/darrengosbell

In article <1132598107.329812.7840@g47g2000cwa.googlegroups.com>,
junwinlestari@gmail.com says...
> Hi,
>
> I am having a difficulty to view the Measures: OS End Month, for
> dimensions more than one.
>
> Single Dimension: Ok no problem on this
> ([Measures].[Os End Month], ClosingPeriod (
> & #91;DIM_Account_Date
_Year].[Account The Month],
> & #91;DIM_Account_Date
_Year].CurrentMember)
>
> Mutliple Dimension:
> ([Measures].[Os End Month], ClosingPeriod (
> & #91;DIM_Account_Date
_Year].[Account The Month],
> & #91;DIM_Account_Date
_Year]. CurrentMember),Closi
ngPeriod (
> [DIM_UW_Year].[Uw Year], [DIM_UW_Year].CurrentMember))
>
> By using the above statement, I can only view the amount if both
> dimensions are selected together.
>
> My intention: Assuming I have THREE Dimensions, I would like have the
> flexibility on this measure ([Measures].[Os End Month]) to view in one
> or both or three of the dimensions.
>
> Kindly need advice for the issue.
>
> Thanks
>
> regards,
> JW
>
>

Mangorind

2005-11-24, 1:23 pm

Hi,

Thanks for the replied for the above issue, I am using Analysis Service
2000, for the past few days, I had tried the method you mentioned, but
still i have some problem with the syntax. Appreciate for your
direction to correct the logic below:

iif(& #91;DIM_Account_Date
_Year].CurrentMember.Level.Ordinal > 0,
([Measures].[Os End Month], StrToMember(IIF
(& #91;DIM_Account_Date
_Year].CurrentMember IS & #91;DIM_Account_Date
_Year].[All
DIM_Account_Date_Yea
r] , & #91;DIM_Account_Date
_Year].[All
DIM_Account_Date_Yea
r].UniqueName ,ClosingPeriod (
& #91;DIM_Account_Date
_Year].[Account The Month],
& #91;DIM_Account_Date
_Year].CurrentMember).UniqueName)), StrToMember(
IIF([DIM_UW_Year].CurrentMember IS [DIM_UW_Year].[All DIM_UW_YEAR] ,
[DIM_UW_Year].[All DIM_UW_YEAR].UniqueName, ClosingPeriod (
[DIM_UW_Year].[Uw Year], [DIM_UW_Year].CurrentMember).UniqueName))
)

regards,
Junwin

Mangorind wrote:
> Hi,
>
> I am having a difficulty to view the Measures: OS End Month, for
> dimensions more than one.
>
> Single Dimension: Ok no problem on this
> ([Measures].[Os End Month], ClosingPeriod (
> & #91;DIM_Account_Date
_Year].[Account The Month],
> & #91;DIM_Account_Date
_Year].CurrentMember)
>
> Mutliple Dimension:
> ([Measures].[Os End Month], ClosingPeriod (
> & #91;DIM_Account_Date
_Year].[Account The Month],
> & #91;DIM_Account_Date
_Year]. CurrentMember),Closi
ngPeriod (
> [DIM_UW_Year].[Uw Year], [DIM_UW_Year].CurrentMember))
>
> By using the above statement, I can only view the amount if both
> dimensions are selected together.
>
> My intention: Assuming I have THREE Dimensions, I would like have the
> flexibility on this measure ([Measures].[Os End Month]) to view in one
> or both or three of the dimensions.
>
> Kindly need advice for the issue.
>
> Thanks
>
> regards,
> JW

Sponsored Links