January 20, 2010 at 3:26 am
Hi,
I am new to ssas and I have a specific requirement, In cube I have
Measures:
1. "% Tasks Completed" (Calculated Measure)
Dimensions:
1. Date, with (Year - Quarter - Month) hierarchy.
2. Organisation, with (Unit - Team - Employee) hierarchy.
user want slice the measure "% Tasks Completed" by date and want to filter data by "Employee" level in "Organisation" dimension, BUT....
Also at the same time want to see "% Tasks Completed" related to the "Team" employee belongs to (the level above "Employee" as you cans see in dimension 2)
how to accomplish this?
any help would be very much appreciated
Jad
January 22, 2010 at 6:09 am
It looks like you should create another calculated measure on the cube, which would go one level up (.parent) and calculate the currentmember against its parent.
What are the facts on the cube?
What soes the Task Completed % calculated measure do (code)?
M.Sc.IT, M.B.A, MCTS BI 2008, MCITP BI 2008, MCTS SQL Dev, CSM, CDVDM
January 23, 2010 at 7:21 pm
Not sure how you have this setup or what tool you are using to return these results, but if you are just querying the database you could do something like this:
SELECT Measures.[% Tasks Completed] on 0,
{Ancestor([Organisation].[Hierarchy Name].[Employee Name],[Organisation].[Hierarchy Name].[Team]),
[Organisation].[Hierarchy Name].[Employee Name]} on 1
FROM [Cube Name]
You would need to replace the hierarchy name, the employee name, and the cube name. Also, this is referencing the name of the member and not the unique name, so it might look like this with the unique member (or key) reference:
SELECT Measures.[% Tasks Completed] on 0,
{Ancestor([Organisation].[Hierarchy Name].&[Employee Number],[Organisation].[Hierarchy Name].[Team]),
[Organisation].[Hierarchy Name].&[Employee Number]} on 1
FROM [Cube Name]
By using the Ancestor function you can grab a related dimension member at a specified level in the hierarchy.
----------------------------------------------------------------------------------------
Dan English - http://denglishbi.wordpress.com
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply