SQL Server Innovations

Problem: Creating SSRS report from Cube, I need to write MDX query to compare current month with previous 2 months figures Solution used as below: /* Using Lag function to calculate before 2 Months */ WITH MEMBER [Measures].[Previous 2 Months] AS ([Date].[Calendar Date]. Lag (2), [Measures].[Total Sales Amount]), Format_String = "Currency" SELECT { [Measures].[Total Sales Amount], [Measures].[Previous 2 Months]} on columns , non empty [Date].[Calendar Date].[Month Name]. Members on rows FROM [Analysis Services Tutorial]                                                                                           

Problem: Compare current Month Sale with Previous Month Sales MDX I used to solve the problem is as below: // Retrieve Number of Prior Periods WITH MEMBER [Measures].[PreviousMonthSale] AS ([Date].[Calendar Date]. PrevMember , [Measures].[Reseller Sales-Sales Amount]), Format_String = 'Currency' SELECT {[Measures].[Reseller Sales-Sales Amount],[Measures].[PreviousMonthSale] }  ON COLUMNS , NON EMPTY [Date].[Calendar Date].[Month Name] ON ROWS FROM [Analysis Services Tutorial]

Problem: Query to get Current Day - Month and Year in MDX /* MDX for retrieving current Year */ WITH MEMBER [Measures].[Current Year] AS     VBAMDX. Format (VBAMDX.Now(), "yyyy" ) SELECT   {[Measures].[Current Year]} ON COLUMNS FROM [Analysis Services Tutorial]; /* Current Month */ WITH MEMBER [Measures].[Current Month] AS     VBAMDX. Format (VBAMDX.Now(), "MM" ) SELECT   {[Measures].[Current Month]} ON COLUMNS FROM [Analysis Services Tutorial]; /* MDX for retrieving current Date */ WITH MEMBER [Measures].[Current Date] AS     VBAMDX. Format (VBAMDX.Now(), "dd" ) SELECT   {[Measures].[Current Date]} ON COLUMNS FROM [Analysis Services Tutorial];

Problem:  I had a requirement to compare current year Profit, Daily Turnover, and other measures with Previous year measures Solution:  I have used Parallel Period function to compare my current year stats with Previous year and MDX statement, finally I produced was as below: /* Final Query to calculate Requested Attributes*/ WITH MEMBER [Measures].[Previous Year Daily Turnover] AS ( ParallelPeriod ([Calendar].[Year Num],1), [Measures].[Total Daily Turnover]), FORMAT_STRING = 'Currency' MEMBER [Measures].[Previous Year Profit] AS ( ParallelPeriod ([Calendar].[Year Num],1), [Measures].[Profit]), FORMAT_STRING = 'Currency' MEMBER [Measures].[Previous Year Avg Daily Turnover] AS ( ParallelPeriod ([Calendar].[Year Num],1), [Measures].[Avg Daily Turnover]), FORMAT_STRING = 'Currency' MEMBER [Measures].[Previous Year Avg Daily Revenue] AS ( ParallelPeriod ([Calendar].[Year Num],1), [Measures].[Avg Daily Revenue]), F...