asked on

How do I make an MDX Query, run from inside a stored procedure, using OPENROWSET, dynamic?

Using SQL Server 2014 and I'm trying to merge Analytic data and Relational data for a report. I'm almost there but there are parts that need to be dynamic in the MDX part.

See attached query. I need the DATASOURCE dynamic, the Initial Catalog dynamic, and the year currently hardcoded as 2017 dynamic. I try to replace them with variables and it keeps giving me errors.
SQLMDXQuery.txt

Pawan Kumar

Please use this -

DECLARE @SQL AS VARCHAR(MAX)= ''
DECLARE @Year AS VARCHAR(4) = '2017'
SET @SQL = ' SELECT NON EMPTY {  [Measures].[PnL], 
													      [Measures].[Hire Rate], 
													      [Measures].[TCE], 
													      [Measures].[Offhire Days], 
													      [Measures].[Net Days]  } ON COLUMNS,
											         NON EMPTY { ([Voyage].[Vessel].[Vessel].ALLMEMBERS ) } ON ROWS 
												 FROM ( SELECT ( { [Date].[Year].&'+ @Year +' } ) ON COLUMNS FROM 
																	( SELECT ( {  
																			 [Voyage].[Vessel Tag].&[Atlantis], 
																			  } ) ON COLUMNS 
																		 FROM [cubeVoyageResults])) 
														 WHERE ( [Voyage].[Vessel Tag].CurrentMember, [Date].[Year].&'+ @Year + ')'
EXEC ( 'SELECT * FROM OPENROWSET(''MSOLAP'',''DATASOURCE=SERVER; Initial Catalog=CUBEDB;'' , ' + @SQL + ' ); ' )

Open in new window

Paul Mauriello

ASKER

I tried that and got errors
Msg 156, Level 15, State 1, Line 46
Incorrect syntax near the keyword 'SELECT'.
Msg 102, Level 15, State 1, Line 46
Incorrect syntax near '{'.
Msg 102, Level 15, State 1, Line 52
Incorrect syntax near 'Date'.
Msg 102, Level 15, State 1, Line 54
Incorrect syntax near 'Voyage'.

ASKER CERTIFIED SOLUTION

Pawan Kumar

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Paul Mauriello

ASKER

Thank you that worked awesomely

Pawan Kumar

welcome. Glad to help as always. :)