Hi

I have a call center fact table which has half an hour granularity.

If I run the below MDX in the MDX Sample App

WITH MEMBER [Time].[07:00 to 08.00 AM] AS
'[Time].[07:00-07:29]+[Time].[07:30-07:59]'
SELECT {[Time].[07:00 to 08.00 AM]} ON ROWS,
{[Measures].[Calls]} ON COLUMNS
FROM [HVDNCube]

I get the following output

Calls
07:00 to 08.00 AM 12

But if I run the same query (with openquery) in SQL Query analyzer

SELECT * FROM OPENQUERY(OHPSRV01MMSIDataMart, 'WITH MEMBER
[Measures].[Calls] AS ''([Measures].[AcdCalls] +
[Measures].[Connectcalls] + [Measures].[Outflowcalls])''
MEMBER [Time].[07:00 to 08.00 AM] AS
''[Time].[07:00-07:29]+[Time].[07:30-07:59]''
SELECT {[Time].[07:00 to 08.00 AM]} ON ROWS,
{[Measures].[Calls]} ON COLUMNS
FROM [HVDNCube]')

I get the following output

[Measures].[Calls]
12

I dont get the column which has values 07:00 to 08.00 AM.

Please help!!!!

Thanks in advance

Milind

Ok figured this out.

The calculated member needs to be child of the [All Time].

SELECT * FROM OPENQUERY(OHPSRV01MMSIDataMart*, 'WITH MEMBER
[Measures].[Calls] AS ''([Measures].[AcdCalls] +
[Measures].[Connectcalls] + [Measures].[Outflowcalls])''
MEMBER [Time].[All Time].[07:00 to 08.00 AM] AS
''[Time].[07:00-07:29]+[Time].*[07:30-07:59]''
SELECT {[Time].[07:00 to 08.00 AM]} ON ROWS,
{[Measures].[Calls]} ON COLUMNS
FROM [HVDNCube]')

Hope this helps for someone in the future.

thanks

Milind