I have a MDX query

WITH
MEMBER [Measures].[Calls] AS
''[Measures].[Acdcalls]+[Measures].[connectcalls]''
SET [YearRange] AS
''({[YearMonthDay].[Year].[2006],[YearMonthDay].[Year].[2005]})''
SELECT {[Month].children} ON ROWS,
{[YearRange]} ON COLUMNS
FROM [HVDNCube]

The output is as follows

Months Year.2006 Year.2005
__________________________
Jan 62 15
Feb 56 93
....
....


Now I want to calculate the Average Daily calls

The output should be

Months Year.2006 Year.2005
__________________________
Jan 2 (62/31) ...
Feb 2(56/28) ...
....
....

Any help would be appreciated

Milind

This would be cleaner in AS 2005, with [Month] and [YearMonthDay] as
hierarchies of the same Time dimension. But if you're using AS 2000, try
something like:

MEMBER [Measures].[Calls] AS
'[Measures].[Acdcalls]+[Measures].[connectcalls]'
Member [Measures].[DayCount] as
'NonEmptyCrossJoin(Descendants([YearMonthDay].CurrentMember,
[YearMonthDay].[Day]), {[Month].CurrentMember}).Count'
Member [Measures].[AvgDailyCalls] as
'CInt([Measures].[Calls]/[Measures].[DayCount])'
SET [YearRange] AS
'({[YearMonthDay].[Year].[2006],
[YearMonthDay].[Year].[2005]})'
SELECT {[Month].children} ON ROWS,
{[YearRange]} ON COLUMNS
FROM [HVDNCube]
Where ([Measures].[AvgDailyCalls])

- Deepak

Deepak Puri
Microsoft MVP - SQL Server

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

It works! I dont have to use temporary tables now.

Thanks Deepak!