mduff
08-01-2012, 09:22 AM
Hi,
I have two pivot tables that I would like that the report filters update on both when I select them on one
I have read this
http://blog.contextures.com/archives/2012/01/03/change-all-pivot-tables-with-one-selection/
But unfortunately it does not work for me because this data source is an OLAP Cube :(
Does any one know of any code that will do this (please note this needs to be used in 2007 so I can not use slicers I wish I could)
here is the MDX code of Pivot one
SELECT NON EMPTY CrossJoin(Hierarchize(AddCalculatedMembers({DrilldownLevel({[Dim Local Time].[Weeks].[All]})})), {[Measures].[Total Day Of Absence],[Measures].[Scheduled Shrinkage],[Measures].[Day of Absenteeism]}) DIMENSION PROPERTIES PARENT_UNIQUE_NAME,[Dim Local Time].[Weeks].[Week Name].[Ordering Week Desc on dim_Monday_Weeks week_of_year] ON COLUMNS , NON EMPTY Hierarchize(AddCalculatedMembers({DrilldownLevel({[Dim Employee Teams].[Sykes Staff].[All]})})) DIMENSION PROPERTIES PARENT_UNIQUE_NAME ON ROWS FROM (SELECT Filter(Hierarchize([Dim Employee Teams].[Sykes Staff].[Staff Name].Members), ([Measures].[Avaya Agent - Acdcalls]>0)) ON COLUMNS FROM (SELECT ({[Dim Location].[Countries].[Country].&[1],[Dim Location].[Countries].[Country].&[2]},{[Dim Local Time].[Week and Day].[Year].&[6].&[Week 24],[Dim Local Time].[Week and Day].[Year].&[6].&[Week 25],[Dim Local Time].[Week and Day].[Year].&[6].&[Week 26],[Dim Local Time].[Week and Day].[Year].&[6].&[Week 27],[Dim Local Time].[Week and Day].[Year].&[6].&[Week 28],[Dim Local Time].[Week and Day].[Year].&[6].&[Week 29]}) ON COLUMNS FROM [Management Portal]) WHERE ([Dim Location].[Agent Site].[Site Nickname].&[Milton-Freewater],[Dim Business].[Accounts].[All])) WHERE ([Dim Location].[Agent Site].[Site Nickname].&[Milton-Freewater],[Dim Business].[Accounts].[All]) CELL PROPERTIES VALUE, FORMAT_STRING, LANGUAGE, BACK_COLOR, FORE_COLOR, FONT_FLAGS
and MDX of Pivot 2
SELECT NON EMPTY Hierarchize(AddCalculatedMembers({DrilldownLevel({[Dim Business].[Business].[All]})})) DIMENSION PROPERTIES PARENT_UNIQUE_NAME ON COLUMNS FROM (SELECT ({[Dim Location].[Countries].[Country].&[1],[Dim Location].[Countries].[Country].&[2]},{[Dim Local Time].[Week and Day].[Year].&[6].&[Week 24],[Dim Local Time].[Week and Day].[Year].&[6].&[Week 25],[Dim Local Time].[Week and Day].[Year].&[6].&[Week 26],[Dim Local Time].[Week and Day].[Year].&[6].&[Week 27],[Dim Local Time].[Week and Day].[Year].&[6].&[Week 28],[Dim Local Time].[Week and Day].[Year].&[6].&[Week 29]}) ON COLUMNS FROM [Management Portal]) WHERE ([Dim Location].[Agent Site].[All],[Dim Business].[Accounts].[All],[Measures].[Day of Absenteeism]) CELL PROPERTIES VALUE, FORMAT_STRING, LANGUAGE, BACK_COLOR, FORE_COLOR, FONT_FLAGS
I have looked on the internet but could not find anything (other than this hard to do)
any help would be much appreciated
I have two pivot tables that I would like that the report filters update on both when I select them on one
I have read this
http://blog.contextures.com/archives/2012/01/03/change-all-pivot-tables-with-one-selection/
But unfortunately it does not work for me because this data source is an OLAP Cube :(
Does any one know of any code that will do this (please note this needs to be used in 2007 so I can not use slicers I wish I could)
here is the MDX code of Pivot one
SELECT NON EMPTY CrossJoin(Hierarchize(AddCalculatedMembers({DrilldownLevel({[Dim Local Time].[Weeks].[All]})})), {[Measures].[Total Day Of Absence],[Measures].[Scheduled Shrinkage],[Measures].[Day of Absenteeism]}) DIMENSION PROPERTIES PARENT_UNIQUE_NAME,[Dim Local Time].[Weeks].[Week Name].[Ordering Week Desc on dim_Monday_Weeks week_of_year] ON COLUMNS , NON EMPTY Hierarchize(AddCalculatedMembers({DrilldownLevel({[Dim Employee Teams].[Sykes Staff].[All]})})) DIMENSION PROPERTIES PARENT_UNIQUE_NAME ON ROWS FROM (SELECT Filter(Hierarchize([Dim Employee Teams].[Sykes Staff].[Staff Name].Members), ([Measures].[Avaya Agent - Acdcalls]>0)) ON COLUMNS FROM (SELECT ({[Dim Location].[Countries].[Country].&[1],[Dim Location].[Countries].[Country].&[2]},{[Dim Local Time].[Week and Day].[Year].&[6].&[Week 24],[Dim Local Time].[Week and Day].[Year].&[6].&[Week 25],[Dim Local Time].[Week and Day].[Year].&[6].&[Week 26],[Dim Local Time].[Week and Day].[Year].&[6].&[Week 27],[Dim Local Time].[Week and Day].[Year].&[6].&[Week 28],[Dim Local Time].[Week and Day].[Year].&[6].&[Week 29]}) ON COLUMNS FROM [Management Portal]) WHERE ([Dim Location].[Agent Site].[Site Nickname].&[Milton-Freewater],[Dim Business].[Accounts].[All])) WHERE ([Dim Location].[Agent Site].[Site Nickname].&[Milton-Freewater],[Dim Business].[Accounts].[All]) CELL PROPERTIES VALUE, FORMAT_STRING, LANGUAGE, BACK_COLOR, FORE_COLOR, FONT_FLAGS
and MDX of Pivot 2
SELECT NON EMPTY Hierarchize(AddCalculatedMembers({DrilldownLevel({[Dim Business].[Business].[All]})})) DIMENSION PROPERTIES PARENT_UNIQUE_NAME ON COLUMNS FROM (SELECT ({[Dim Location].[Countries].[Country].&[1],[Dim Location].[Countries].[Country].&[2]},{[Dim Local Time].[Week and Day].[Year].&[6].&[Week 24],[Dim Local Time].[Week and Day].[Year].&[6].&[Week 25],[Dim Local Time].[Week and Day].[Year].&[6].&[Week 26],[Dim Local Time].[Week and Day].[Year].&[6].&[Week 27],[Dim Local Time].[Week and Day].[Year].&[6].&[Week 28],[Dim Local Time].[Week and Day].[Year].&[6].&[Week 29]}) ON COLUMNS FROM [Management Portal]) WHERE ([Dim Location].[Agent Site].[All],[Dim Business].[Accounts].[All],[Measures].[Day of Absenteeism]) CELL PROPERTIES VALUE, FORMAT_STRING, LANGUAGE, BACK_COLOR, FORE_COLOR, FONT_FLAGS
I have looked on the internet but could not find anything (other than this hard to do)
any help would be much appreciated