SELECT StrToSet ('{[Geography].[Geography].[Country].[Germany],[Geography].[Geography].[Country].[Canada]}', CONSTRAINED)
ON 0
FROM [Adventure Works]
http://msdn.microsoft.com/en-us/library/ms144782.aspx
STRTOSET WITH LIST OF QUALIFIED MEMBER NAMES
SELECT StrToSet ('{[Geography].[Geography].[Country].[Germany],[Geography].[Geography].[Country].[Canada]}', CONSTRAINED) ON 0 FROM [Adventure Works] http://msdn.microsoft.com/en-us/library/ms144782.aspx
0 Comments
This must be added inside the cube, need to change hardcoded hierarchies.
--get last sunday CREATE MEMBER CURRENTCUBE.[Measures].[CurrentWeekT] AS "[Date].[Year - Season - Quarter - Period - Week - Date].[Week].&[" + FORMAT(VBA!DateAdd("d", -VBA!WeekDay(VBA!Now()) + 1, VBA!Now()),"yyyy") + "-" + FORMAT(VBA!DateAdd("d", -VBA!WeekDay(VBA!Now()) + 1, VBA!Now()),"MM") + "-" + FORMAT(VBA!DateAdd("d", -VBA!WeekDay(VBA!Now()) + 1, VBA!Now()),"dd") + "T00:00:00]", VISIBLE = 0; --dynamic set to get current date CREATE HIDDEN DYNAMIC SET CURRENTCUBE.[SET_CurrentDateT] AS STRTOSET("[Date].[Date].&[" + FORMAT(VBA!Now(),"yyyy") + "-" + FORMAT(VBA!Now(),"MM") + "-" + FORMAT(VBA!Now(),"dd") + "T00:00:00]"); --dynamic set to get current week last CREATE HIDDEN DYNAMIC SET CURRENTCUBE.[SET_CurrentWeekT] AS STRTOSET("[Date].[Year - Week].[Year Week].&[" + FORMAT(VBA!DateAdd("d", -VBA!WeekDay(VBA!Now()) + 1, VBA!Now()),"yyyy") + "-" + FORMAT(VBA!DateAdd("d", -VBA!WeekDay(VBA!Now()) + 1, VBA!Now()),"MM") + "-" + FORMAT(VBA!DateAdd("d", -VBA!WeekDay(VBA!Now()) + 1, VBA!Now()),"dd") + "T00:00:00]"); -- //Current Week -1 ( [Date Calcs].[Comparison].&[6], [Date Calcs].[Aggregation].Members ) = ( [Date Calcs].[Comparison].DefaultMember, ParallelPeriod([Date].[Year - Season - Quarter - Period - Week - Date].[Week], 0, STRTOMEMBER([CurrentWeekT]) ) ); //Current Week -2 ( [Date Calcs].[Comparison].&[7], [Date Calcs].[Aggregation].Members ) = ( [Date Calcs].[Comparison].DefaultMember, ParallelPeriod([Date].[Year - Season - Quarter - Period - Week - Date].[Week], 1, STRTOMEMBER([CurrentWeekT]) ) ); //Current Week -3 ( [Date Calcs].[Comparison].&[8], [Date Calcs].[Aggregation].Members ) = ( [Date Calcs].[Comparison].DefaultMember, ParallelPeriod([Date].[Year - Season - Quarter - Period - Week - Date].[Week], 2, STRTOMEMBER([CurrentWeekT]) ) ); //Current Week -4 ( [Date Calcs].[Comparison].&[8], [Date Calcs].[Aggregation].Members ) = ( [Date Calcs].[Comparison].DefaultMember, ParallelPeriod([Date].[Year - Season - Quarter - Period - Week - Date].[Week], 3, STRTOMEMBER([CurrentWeekT]) ) ); //Current Week -5 ( [Date Calcs].[Comparison].&[8], [Date Calcs].[Aggregation].Members ) = ( [Date Calcs].[Comparison].DefaultMember, ParallelPeriod([Date].[Year - Season - Quarter - Period - Week - Date].[Week], 4, STRTOMEMBER([CurrentWeekT]) ) ); This script can be used in Excel > Data > Connections > Manage Sets.
'FILTER( [Customer].[Customer].MEMBERS,(InStr( 1, [Customer].[Customer].CURRENTMEMBER.NAME, "AbcCustomer" ) > 0) )' MDX Code
WITH MEMBER [MEASURES].[ParameterCaption] AS [Date].[Retail Week].CURRENTMEMBER.MEMBER_CAPTION MEMBER [MEASURES].[ParameterValue] AS [Date].[Retail Week].CURRENTMEMBER.UNIQUENAME MEMBER [MEASURES].[ParameterLevel] AS [Date].[Retail Week].CURRENTMEMBER.LEVEL.ORDINAL MEMBER [Measures].[Sorter] AS VBAMDX!CINT(VBAMDX!MID([MEASURES].[ParameterValue],24,LEN([MEASURES].[ParameterValue])-24)) SELECT { [MEASURES].[ParameterCaption], [MEASURES].[ParameterValue], [MEASURES].[ParameterLevel], [MEASURES].[Sorter] } ON COLUMNS, ORDER( { [Date].[Retail Week].[Retail Week].MEMBERS - [Date].[Retail Week].[All].UNKNOWNMEMBER } ,[Sorter] ,BDESC) ON ROWS FROM [Cube Name] RESULT ParameterCaption Label 2008 W10 2008 W9 2008 W8 2008 W7 2008 W6 2008 W5 2008 W4 2008 W2 2008 W2 2008 W1 ParameterCaption Value [Date].[Retail Week].&[60] [Date].[Retail Week].&[59] [Date].[Retail Week].&[58] [Date].[Retail Week].&[57] [Date].[Retail Week].&[56] [Date].[Retail Week].&[55] [Date].[Retail Week].&[54] [Date].[Retail Week].&[53] [Date].[Retail Week].&[52] [Date].[Retail Week].&[51] [Date].[Retail Week].&[50] [Date].[Retail Week].&[49] [Date].[Retail Week].&[48] [Date].[Retail Week].&[47] |
Welcome!Welcome to our edition of eblog! This section will help you find various post and articles that can help you in some of your IT work especially implementing MS BI Solutions. We hope you find this section useful. If you will need further information, please send us your questions and inquiries by going to the "Contact Us" section. Enjoy reading. [Jhon S] Categories
All
|