CODEWITHSUNDEEP
t-sqlmdxolap-cube
kenzolek
kenzolek

Reputation: 344

Change titles of columns in MDX with T-SQL

I created simply query with T-SQL processed OLAP cube like below:

SELECT * FROM OPENQUERY([linkedserver], 'SELECT NON EMPTY { [Measures].[Revenue] } ON COLUMNS, 
                                          NON EMPTY { ([Basic].[Name].[Name].ALLMEMBERS ) }  ON ROWS 
                                   FROM [SummaryCube]');

The result of query is the table. Titles of columns in this table are defaults, ex.:"[Basic].[Names].[Names].[MEMBER_CAPTION]" but I would like to change these titles for ex.: "Names". I cannot change using aliases or I'm using aliases wrong way. Can anyone tell me how can I change name of column?

Upvotes: 1

Views: 1869

Answers (2)

whytheq
whytheq

Reputation: 35557

I usually do some conversions as well:

SELECT  
        Names     = CONVERT(VARCHAR(100), "[Basic].[Name].[Name].[MEMBER_CAPTION]"),
        Revenue   = CONVERT(NUMERIC(18,2),CAST("[Measures].[Revenue]" AS FLOAT))
FROM    
OPENQUERY
  (
    [linkedserver], 
    'SELECT 
        NON EMPTY { [Measures].[Revenue] } ON COLUMNS, 
        NON EMPTY { ([Basic].[Name].[Name].ALLMEMBERS ) }  ON ROWS 
    FROM [SummaryCube]'
  );

And if you switched to the better olapextensions addin it would be:

DECLARE @Server NVARCHAR(30) = 'SummaryCubeServerName';
DECLARE @Database NVARCHAR(50) = 'SummaryCubeDatabaseName';
DECLARE @MDX NVARCHAR(MAX) = '
SELECT 
    NON EMPTY { [Measures].[Revenue] } ON COLUMNS, 
    NON EMPTY { ([Basic].[Name].[Name].ALLMEMBERS ) }  ON ROWS 
FROM    [SummaryCube];
'

CREATE TABLE #Results(
   Names   VARCHAR(250),
   Revenue FLOAT
);

INSERT INTO #Results
EXEC ExecuteOLAP @Server, @Database, @MDX;

SELECT 
    Names, 
    Revenue = ISNULL(CONVERT(DECIMAL(28,8),Revenue),0.0),
FROM #Results;

Upvotes: 1

iamdave
iamdave

Reputation: 12243

Instead of your SELECT *, double quote the names of the returned columns:

SELECT "[Basic].[Name].[Name].[MEMBER_CAPTION]" as Names

You need to use double quotes as SQL Server recognises square brackets as identifiers.

Upvotes: 1

Related Questions