Sunday, November 13, 2016

Parsing MDX by Dynamic Query



The following script is an example of parsing MDX by Dynamic query and storing the result in a temporary table.


USE MyDataBase





GO


SET ANSI_NULLS ON






GO


SET QUOTED_IDENTIFIER ON






GO


SET FMTONLY OFF






GO


SET NOCOUNT ON






GO


PRINT 'Date: ' + CONVERT(VARCHAR(24),GETDATE(),13) + ' Server Name: ' + @@servername + ' Service Name: ' + @@servicename;





GO


IF EXISTS


(SELECT *

FROM sys.objects


WHERE name = 'Last_Period_Revenue_MA')


DROP PROC dbo.Last_Period_Revenue_MA;


PRINT 'DROP PROC dbo.Last_Period_Revenue_MA';





GO


PRINT 'CREATE PROC dbo.Last_Period_Revenue_MA';





GO


CREATE PROC dbo.Last_Period_Revenue_MA





/************************************************************************************************************************************


Description:


Change Log


# Date Who Change


*************************************************************************************************************************************/

@CustomerCode NVARCHAR(255)


,@Period INT = -12





AS


BEGIN


BEGIN TRY


DECLARE @StrMDX AS NVARCHAR(MAX)


,@BOMDate AS SMALLDATETIME

,@DateFr AS SMALLDATETIME

,@DateTo AS SMALLDATETIME


,@StrSQL AS NVARCHAR(MAX)

,@Amount FLOAT;


SET @BOMDate = DATEADD(d,-DAY(GETDATE())+1, GETDATE());

SET @DateFr = DATEADD(m, @Period, @BOMDate);

SET @DateTo = DATEADD (d, -1, @BOMDate);


CREATE TABLE #MSCRM_Roll_DataSet1 (Amount FLOAT NULL);


CREATE TABLE #MSCRM_Roll_DataSet2 ( CustomerType NVARCHAR(255) NULL


,CustomerName VARCHAR(255) NULL


,CustomerSite VARCHAR(255) NULL


,FinancialYearDaily NVARCHAR(255) NULL


,FinancialPeriodDaily NVARCHAR(255) NULL


,CalendarDateDaily NVARCHAR(255) NULL


,ExclAmount FLOAT NULL);


CREATE TABLE #MSCRM_Roll_DataSet3 ( MthName NVARCHAR(20) NULL


,YrMth NVARCHAR(10) NULL


,ExclAmount FLOAT NULL);

SET @StrSQL = '([Calendar].[' +

right('0'+CAST(DAY(@DateFr) AS nvarchar),2)

+ '/'+ right('0'+CAST(MONTH(@DateFr) AS nvarchar),2)

+ '/'+ CAST(YEAR(@DateFr) AS nvarchar) + '] : [Calendar].['

+ right('0'+CAST(DAY(@DateTo) AS nvarchar),2)

+ '/'+ right('0'+CAST(MONTH(@DateTo) AS nvarchar),2)

+ '/'+ CAST(YEAR(@DateTo) AS nvarchar) + '])';

SET @StrMDX = 'INSERT INTO #MSCRM_Roll_DataSet1 SELECT * FROM OPENQUERY ([PROD_AS], ' + CHAR(39) +

'SELECT { [Measures].[Exclusive Amount] } ON COLUMNS

FROM ( SELECT ( { [Customer].[Customers].[CUSTOMER NAME].[' + @CustomerCode +'] } ) ON COLUMNS

FROM ( SELECT ( { ' + @StrSQL + ' } ) ON COLUMNS FROM [Sales])) ' + CHAR(39) + ')';





EXEC sp_executesql @StrMDX;


SET @Amount = (SELECT Amount FROM #MSCRM_Roll_DataSet1)





SET @StrMDX = 'INSERT INTO #MSCRM_Roll_DataSet2 SELECT * FROM OPENQUERY ([PROD_AS], ' + CHAR(39) +

'SELECT NON EMPTY { [Measures].[Exclusive Amount] } ON COLUMNS






,NON EMPTY { ([Customer].[Customers].[Ref Customer Site].ALLMEMBERS


* [Calendar].[Calendar Date Daily].ALLMEMBERS ) } ON ROWS


FROM ( SELECT ( { [Customer].[Customers].[CUSTOMER NAME].&[' + @CustomerCode +'] } ) ON COLUMNS

FROM ( SELECT ( { ' + @StrSQL + ' } ) ON COLUMNS FROM [Sales])) ' + CHAR(39) + ')';


EXEC sp_executesql @StrMDX;


INSERT INTO #MSCRM_Roll_DataSet3

SELECT MAX(DATENAME(m, CAST(SUBSTRING(CalendarDateDaily,7,4)

+ '-' + SUBSTRING(CalendarDateDaily,4,2)


+ '-' + SUBSTRING(CalendarDateDaily,1,2) AS SMALLDATETIME)))


, MAX(SUBSTRING(CalendarDateDaily,7,4)

+ '-' + SUBSTRING(CalendarDateDaily,4,2))


, SUM (ExclAmount) from #MSCRM_Roll_DataSet2

GROUP BY SUBSTRING(CalendarDateDaily,7,4)

+ '-' + SUBSTRING(CalendarDateDaily,4,2);


WHILE @DateFr < @DateTo

BEGIN

INSERT INTO #MSCRM_Roll_DataSet3

SELECT DATENAME(m,@DateFr)


, DATENAME(year,@DateFr)


+ '-' + right('0'+CAST(DATEPART(MONTH,@DateFr)as NVARCHAR(2)),2) , 0;


SET @DateFr = DATEADD(m,1,@DateFr);


END;


SELECT MthName

,MthName + ' ' + SUBSTRING(YrMth,3 , 2) MthYr

,YrMth

,ExclAmount

FROM #MSCRM_Roll_DataSet3;

DROP TABLE #MSCRM_Roll_DataSet1;


DROP TABLE #MSCRM_Roll_DataSet2;


DROP TABLE #MSCRM_Roll_DataSet3;


END TRY


BEGIN CATCH


SELECT ERROR_NUMBER() AS ErrorNumber

,ERROR_SEVERITY() AS ErrorSeverity

,ERROR_STATE() AS ErrorState

,ERROR_PROCEDURE() AS ErrorProcedure

,ERROR_LINE() AS ErrorLine

,ERROR_MESSAGE() AS ErrorMessage;

END CATCH






END


GO


/***************************************************************************************


Call Sample:


EXEC dbo.Last_Period_Revenue_MA


'BAUL4021 - Baulderstone Queensland Pty Ltd'


,-24


****************************************************************************************/