I am creating a calulated member called FTE which is as follows:

WITH MEMBER [Measures].[Example] AS
'SUM(
NONEMPTY(
CROSSJOIN(
PeriodsToDate(
[As Of Date].[As Of Dates].[As Of Date - Calendar Group],
[As Of Date].[As Of Dates].CurrentMember
),
{{[Transaction Type].[Transactions].[Transaction Group].&[Billable
Expenses].[Billable]},{[Transaction Type].[Transactions].[Transaction
Group].&[Labor Related].[Billable]}},
[Measures].[Available Hours]
)
),
(iif(
([Measures].[Total Hrs],[As Of Date].[As Of Dates]) = 0,
NULL,
[Measures].[Total Hrs])
/
iif(
([Measures].[Avlb Hrs],[As Of Date].[As Of Dates]) = 0,
NULL,
[Measures].[Avlb Hrs]))
)'
SELECT [Measures].[Example] ON 0
FROM [Flash Transaction]
WHERE
(
[As Of Date].[As Of Dates].[As Of Date - Calendar
Group].&[Calendar].&[2006].&[1].&[1].&[3].&[20060120],
[Calendar].[Calendars].[Calendar
Group].&[Calendar].[2006].[1].[January].[2].[January 14, 2006]:
[Calendar].[Calendars].[Calendar
Group].&[Calendar].[2006].[1].[January].[3].[January 20, 2006]
)

When I try to validate the results of this with the corresponding relation
SQL the values do not match up.

The relational SQL is
SELECT
SUM(A.TOTAL_HOURS)/ SUM(AVAILABLE_HOURS)
FROM fact_FLASH_TRANSACTION A
WHERE
EXISTS (SELECT 'X'
FROM dim_TRANSACTION_TYPE B
WHERE A.dim_TRANSACTION_TYPE_key = B.dim_TRANSACTION_TYPE_key
AND B.TRANSACTION_CATEGORY_NAME = 'Billable')AND
A.dim_CALENDAR_key Between '20060115' And '20060120'
AND A.dim_ASOFDATE_view_key <= '20060120'

What am i doing wrong in the MDX? Any help is greatly appreciated.
--
Thank you,
Ashok G

Hi Ashok,

Not sure what your date hierarchies look like, but there are 2 immediate
issues that I can see:

- You would need to divide 2 SUM()'s in MDX as well, since Sum(A/B) may
not be the same as Sum(A)/Sum(B)

- MDX date range is Jan14:Jan20, vs. SQL of Jan15:Jan20


- Deepak

Deepak Puri
Microsoft MVP - SQL Server

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

HI Deepak,

Both the reasons are correct! I changed the dates to match up and changed
the MDX to SUM(a)/SUM(b) and it worked just fine.

Thanks for your help. I appreciate it.
--
Thank you,
Ashok G


"Deepak Puri" wrote: