Problem when using DateDiff and slicing
- From: "Henrik Skak Pedersen" <skak@xxxxxxxxxxxxxxxx>
- Date: Mon, 7 Apr 2008 23:05:06 +0200
Hello,
I am trying to calculate the length of service for an employee and I am partly successfull.
This works perfectly:
WITH
MEMBER [Measures].[Length of Service] AS
' DateDiff("yyyy",CDate([Employee].[Employee].CurrentMember.Properties("Hire Date")),Now()) '
SELECT
{[Measures].[Length of Service], [Measures].[Employee Count]} ON COLUMNS,
{[Employee].[Employee].Children} ON ROWS
FROM
[Employee]
And this also works perfectly:
WITH
MEMBER [Measures].[Length of Service] AS
' DateDiff("yyyy",CDate([Employee].[Employee].CurrentMember.Properties("Hire Date")),Now()) '
SELECT
{[Measures].[Length of Service], [Measures].[Employee Count]} ON COLUMNS,
{[Employee].[Employee]} ON ROWS
FROM
[Employee]
But in this example only the measure "Employee Count" is correct:
WITH
MEMBER [Measures].[Length of Service] AS
' DateDiff("yyyy",CDate([Employee].[Employee].CurrentMember.Properties("Hire Date")),Now()) '
SELECT
{[Measures].[Length of Service], [Measures].[Employee Count]} ON COLUMNS,
{[Organization].[Organization].Children} ON ROWS
FROM
[Employee]
I was expecting it to show the total of length of service for all employees employed in the organizational unit but instead it shows the grand total beside each unit, This number is the same as example 2 where I am returning the total.
Any ideas?
Best regards
Henrik.
.
- Follow-Ups:
- RE: Problem when using DateDiff and slicing
- From: Charles Wang[MSFT]
- RE: Problem when using DateDiff and slicing
- Prev by Date: Measures.XXXX.dim folder
- Next by Date: Alternate Count method
- Previous by thread: Measures.XXXX.dim folder
- Next by thread: RE: Problem when using DateDiff and slicing
- Index(es):