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
****************************************************************************************/

If you find this script useful please donate generously.