Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

how to show data one level above the filter hierarchy Expand / Collapse
Author
Message
Posted Wednesday, January 20, 2010 3:26 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, December 03, 2013 7:12 AM
Points: 5, Visits: 95
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



Post #850345
Posted Friday, January 22, 2010 6:09 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Tuesday, December 10, 2013 10:06 PM
Points: 374, Visits: 428
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
Post #851959
Posted Saturday, January 23, 2010 7:21 PM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Thursday, August 22, 2013 7:25 AM
Points: 347, Visits: 1,344
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.

Ancestor (MDX)


----------------------------------------------------------------------------------------
Dan English - http://denglishbi.wordpress.com
Post #852657
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse