This project has moved. For the latest updates, please go here.

Roll up advise please

Jun 19, 2013 at 8:32 AM
Hi,
I have a pivot table laid out something like the below:
Team Name
 Resource Name
Basically I require a formula that will count each 'Resource Name' as one, but then roll up at the sub total level.

I have tried the following:
COUNT ([Resource List].[Team Attribute])

But sadly this just counts the Team Name as a '1' also.


Any help and advice gratefully received.


Thanks,


-CL
Coordinator
Jun 19, 2013 at 11:37 AM
You didn't describe the attributes in your dimension and their attribute relationships or hierarchies enough. But one of these may get you closer.

COUNT ([Resource List].[Team Attribute].CurrentMember.Children)

Or


COUNT ([Resource List].[Team Attribute].[Team Attribute].Members)
Jun 19, 2013 at 11:53 AM
Thanks for the response. Sadly neither worked. The first did not count at all, and the second counted every single resource against each item. Thank you anyway.

Hierachy,
hopefully this is the correct detail required:

Each Team name is comprised of resources. Planned and actual effort is within the Cube and works at an individual level and also rolls up to the sub total of each team.

The requirement is essentially to calculate 'FTE'. I can count each person, but that same calculation that works at the lower level does not rollup to the sub total.


Hope this helps further, but appreciate its probably still a bit light.

-CL.
Coordinator
Jun 19, 2013 at 1:28 PM
Can you go to the MDX tab and copy the query here? That will help me see the field names.
Jun 19, 2013 at 1:41 PM
Ok - thanks


MEMBER [Measures].[Count of Resource] as COUNT ([Resource List].[Team Attribute])
SELECT NON EMPTY CrossJoin(CrossJoin(Hierarchize({DrilldownLevel({[Date].[Year Attribute].[All Time]})}), Hierarchize({DrilldownLevel({[Date].[Month Attribute].[All Time]})})), {[Measures].[Planned],[Measures].[Actual],[Measures].[% Utilisation],[Measures].[Capacity],[Measures].[Base Capacity],[Measures].[FTE],[Measures].[Count of Resource]}) DIMENSION PROPERTIES PARENT_UNIQUE_NAME ON COLUMNS , NON EMPTY Hierarchize(DrilldownMember(CrossJoin({[Resource List].[Team Attribute].[All],[Resource List].[Team Attribute].[Team Attribute].Members}, {([Resource List].[Resource Name].[All])}), {[Resource List].[Team Attribute].&[Change Management - UAT], [Resource List].[Team Attribute].&[IT Architecture & Analysis]}, [Resource List].[Resource Name])) DIMENSION PROPERTIES PARENT_UNIQUE_NAME,[Resource List].[Resource Name].[Resource Name].[RBS],[Resource List].[Resource Name].[Resource Name].[Resource Base Calendar],[Resource List].[Resource Name].[Resource Name].[Resource Is Generic],[Resource List].[Resource Name].[Resource Name].[Team Attribute] ON ROWS FROM (SELECT ({[Date].[Year Attribute].&[2013]}) ON COLUMNS , ({[Resource List].[Team Attribute].&[Technical Architecture], [Resource List].[Team Attribute].&[IT Management], [Resource List].[Team Attribute].&[IT Dev - System Test], [Resource List].[Team Attribute].&[IT Dev - Functional Design], [Resource List].[Team Attribute].&[IT Dev - Build], [Resource List].[Team Attribute].&[IT Dev - Application Support], [Resource List].[Team Attribute].&[IT Dev - AEG], [Resource List].[Team Attribute].&[IT Delivery], [Resource List].[Team Attribute].&[IT Architecture & Analysis], [Resource List].[Team Attribute].&[Change Management - UAT], [Resource List].[Team Attribute].&[Change Management]},Intersect(Except({[Resource List].[Resource Name].[Resource Name].Members}, {[Resource List].[Resource Name].&[{BACF80EB-651F-4018-99BD-58E53AC691DD}],[Resource List].[Resource Name].&[{1D9A3154-4D92-4799-A23C-3C2E374899EF}],[Resource List].[Resource Name].&[{26A0D3DB-E7D0-4BCC-AAD3-B222B6F3C648}],[Resource List].[Resource Name].&[{9C533C9B-F6E9-402E-9ACF-B72498F7EA66}],[Resource List].[Resource Name].&[{9157B7C3-14AC-4A22-AEC2-BF678DED360D}],[Resource List].[Resource Name].&[{1A163803-712A-4B12-96F0-76BD94192C3F}],[Resource List].[Resource Name].&[{B746DC78-31D0-415F-B88C-6FEC261BD71E}],[Resource List].[Resource Name].&[{1F6CCD7B-AAFD-4924-8240-EA069B9D7E6E}],[Resource List].[Resource Name].&[{CF707CF1-367F-4E2B-8BF0-B22E79FFC46A}],[Resource List].[Resource Name].&[{BB52DF73-A188-4EB6-9C62-B63D5BFB87CD}],[Resource List].[Resource Name].&[{1AA3EF51-007D-4EB7-9F3D-C7588EF599F9}],[Resource List].[Resource Name].&[{6ACC9F5C-69C4-4A3E-843D-8A4E4785C624}],[Resource List].[Resource Name].&[{1F585144-4DC2-4E74-AB84-EF5059A740BC}],[Resource List].[Resource Name].&[{EC47D82B-37AB-4442-9298-528538D2006C}],[Resource List].[Resource Name].&[{668A4267-8D58-4CEB-B796-4B5B42A6B642}],[Resource List].[Resource Name].&[{D526D759-1833-4DC8-8741-4C4702816A54}],[Resource List].[Resource Name].&[{AC85D633-C152-4776-9F18-4249B3CCA166}],[Resource List].[Resource Name].&[{DC1C04BB-F42E-49ED-BE6C-0AB5C9627CF0}],[Resource List].[Resource Name].&[{4515E189-C704-4412-ADBA-68E82E4830BA}],[Resource List].[Resource Name].&[{9D3FC21E-C390-4D34-9438-F3D3E04E635D}]}), [Resource List].[Resource Name].[Resource Name].Members)) ON ROWS FROM [Actual And Planned]) WHERE ([Project List].[Project State].[All],[Project List].[Project Type].[All],[Project List].[Project Priority].[All],[Project List].[IT Delivery Manager].[All],[Resource List].[Resource Base Calendar].[All],[Project List].[Capitalisation Flag].[All],[Project List].[Project Name].[All],[Task List].[Task].[All],[Project List].[Programme].[All],[Resource List].[Resource Is Generic].&[No]) CELL PROPERTIES VALUE, FORMAT_STRING, LANGUAGE, BACK_COLOR, FORE_COLOR, FONT_FLAGS
Coordinator
Jun 19, 2013 at 1:54 PM
Count(existing [Resource List].[Resource Name].[Reaource Name].members)
Jun 19, 2013 at 2:01 PM
Thank you - worked a treat!!! Brilliant - I am very grateful!

Kinds regards,


-CL
Jun 19, 2013 at 2:18 PM
Just noticed a small flaw. It counts the whole Team regardless of whether 1 or 10 are selected in the Pivot table. For Instance I filter out one name from a team of 10 - 10 is still displayed despite only 9 being visible.

Any thoughts again appreciated..

-CL.
Coordinator
Jun 19, 2013 at 5:26 PM
How about...
Sum(
existing [Resource List].[Resource Name].[Reaource Name].members
,iif(isempty([measures].[Capacity]),null,1)
)
Marked as answer by furmangg on 6/8/2014 at 4:33 PM
Jun 19, 2013 at 5:34 PM
Thanks, but same result I am afraid.
Coordinator
Jun 19, 2013 at 5:41 PM
I would suggest you ask about it here:
http://social.msdn.microsoft.com/Forums/sqlserver/en-US/home?forum=sqlanalysisservices

Provide your current MDX query.

Sorry. I just support the add-in and can't spend time on MDX support.
Marked as answer by furmangg on 6/8/2014 at 4:33 PM
Jun 19, 2013 at 5:49 PM
Understood. Thanks for your help. I am much further on with this advice.

Kind regards,


-CL