Hi Paramesh,
I've written some code for you. I'm a little bit blind because as is often in the case I don't have access to the cube.
If you have any further issues please create an SQL trace and / or and MDX trace within SQL Server Profiler then cut and paste the relevant text into your MSDN thread; -
USE myDatabase
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF EXISTS (SELECT * FROM sys.objects WHERE type = 'P' AND name = 'pExampleOpenQueryMDXCall')
DROP PROCEDURE dbo.pExampleOpenQueryMDXCall
GO
CREATE PROCEDURE dbo.pExampleOpenQueryMDXCall
/*********************************************************************************************
OBJECT: pExampleOpenQueryMDXCall
PARAMETERS: Customerid
DESCRIPTION: Example of calling MDX within Stored Procedures
RELATED LINKS;
Stored Procedures; - http://msdn.microsoft.com/en-us/library/ms190669.aspx
Calling MDX Within Stored Procedures; - http://jesseorosz.wordpress.com/2008/02/12/calling-mdx-from-a-sql-server-stored-procedure/
RETURNS: Data Set for User Selected CustomerIds
AUTHOR: Kieran Wood
EXAMPLE USAGE:
exec pExampleOpenQueryMDXCall
@CustomerID='[Customer].[CustomerID].[All]',
CREATED: 2013-05-21
DATE MODIFIED NAME COMMENTS
07-JAN-2013 Kieran Wood pExampleOpenQueryMDXCall
*********************************************************************************************/
@CustomerID varchar(max) ='[Customer].[CustomerID].[All]'
AS
BEGIN
-- VARCHAR(MAX) can store up to 2 GB of data - not just 8000 characters.....
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
PRINT ' @CustomerID = ' + @CustomerID
-- The problem with OpenQuery is that every column has a data type of string,
-- including the numerical columns. This means that the data type must be changed if you want
-- to perform numerical computations on the figures using T-SQL. So our numerical columns need to be
-- wrapped in CAST & CONVERT
-- Build SQL, N.B. ; - In open query you can't using dynamic SQL within the open query statement.
DECLARE @SQL nvarchar(max)
DECLARE @MDX nvarchar(max)
SET @MDX = '{[Measures].[Count]} ON COLUMNS, '
SET @MDX = @MDX + ' {[ProcessDate].[Date].[Date].ALLMEMBERS} '
SET @MDX = @MDX + ' DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS '
SET @MDX = @MDX + 'FROM ( SELECT ( { ' + @CustomerID + ' } ) ON COLUMNS '
SET @MDX = @MDX + ' FROM [MailSort]) '
PRINT '@MDX = '
PRINT @MDX
-- N.B. ; - MDX calls from linked server returns data types as ntext.
-- The following code assumes that you have set up a linked server to your Analysis Services instance
-- from your Database Engine instance called MailSort_CUBE
SET @SQL = 'SELECT DISTINCT CAST ("[Measures].[Count]" AS int) AS MEASURE, '
SET @SQL = @SQL + ' CAST ("[ProcessDate].[Date].[Date].[MEMBER_CAPTION]" as varchar(15)) AS myDate '
SET @SQL = @SQL + 'FROM openquery(MailSort_CUBE, ' + '''' + @MDX + '''' + ')'
PRINT '@SQL ' + @SQL
EXEC(@SQL)
END
GO
Other recent topics
