I have Time Dimension in the cube and we have 15 measures.

The way we calculate the FY 2005 is Average of 1st and 2nd Half 05 but
we need to round the 1st and 2nd half before calculating the FY 2005.

Right now I have created a calculate member i.e. FY 2005 which has
Parent Dimension is [Time] and parent member of [Year].[2005]

In FY 2005 I have below code:
Avg({[Time].[Year].[2005].[1st Half 05],[Time].[Year].[2005].[2nd Half
05]})

But I need to Round the 1st and 2nd Half 05 and then I need the
average.

How can I do that in a calculated member?

Thanks in advance
Sam

Here is a sample Adventure Works query which uses CInt():

Avg([Date].[Calendar].[Calendar Year].&[2003].Children,
CInt(Measures.CurrentMember))

Select {[Measures].[Average Sales Amount]} on 0,
{DrillDownLevel([Date].[Calendar].[Calendar Year].&[2003]),
[Date].[Calendar].[Calendar Year].&[2003].[Round2003]} on 1
from [Adventure Works]
------------------------------------------------------------
Average Sales Amount
CY 2003 $3,375.70
H1 CY 2003 $6,396.32
H2 CY 2003 $2,671.82
Round2003 $4,534.00

- Deepak

Deepak Puri
Microsoft MVP - SQL Server

*** Sent via Developersdex http://www.developersdex.com ***

Thanks a lot!!! You made my day.

But I have a question:

Measures.CurrentMember gives me a ERR# Do you know why?

All the my Measures are Calculated members. I am using AS 2000.

I was trying to use below code:
iif(Measures.currentmember is
[Measures].[Stock_FS],Avg([Time].[Year].[2005].Children,
CInt([Measures].[Stock_FS]*100)),-1) and just keep doing for each
measures.

But it is not working. I have built a cube so I have to add this code
in a Calculated member for Parent Dimension [Time] and it will be
slice aganist 20 measures.

Thanks once more!!!



Deepak Puri wrote:

The error might be because the number exceeds the integer range - so you
could try CLng() or Round() instead, as in this AS 2000 Foodmart query:

'Avg([Time].[1997].Children,
Round(Measures.CurrentMember))'

Select {[Measures].[Store Sales]} on 0,
{DrillDownLevel({[Time].[1997]}),
[Time].[1997].[Round1997]} on 1
from Sales

- Deepak

Deepak Puri
Microsoft MVP - SQL Server

*** Sent via Developersdex http://www.developersdex.com ***