CODEWITHSUNDEEP
sql-servert-sqlmdxdata-warehouse
Silviu Preda
Silviu Preda

Reputation: 628

MDX+TSQL column name doesn't exist

I have to create a mixture of MDX and TSQL as follows:

    select "[State].[Country].[Country].[MEMBER_CAPTION]" as State,
    "[Measures].[someMeasure]" as [Sum]
    from openquery(my_olap_server,
    'select [Measures].[someMeasure] on 0,
    filter([State].[Country].[Country],not isempty([Measures].[someMeasure])) on 1
    from (select {[State].[Country].[Country].&[index_here]} on columns from [My Cube])')

If the MDX returns no value, than the [State].[Country].[Country].[MEMBER_CAPTION] does not exists, so the query fails with the message 

Msg 207, Level 16, State 1, Line 2
Invalid column name '[State].[Country].[Country].[MEMBER_CAPTION]'

.

Is there a way to force either MDX or TSQL (but i'm guessing MDX) to provide this? Thanks

Upvotes: 0

Views: 880

Answers (1)

Silviu Preda
Silviu Preda

Reputation: 628

    select "[Measures].[StateName]" as State,
    "[Measures].[someMeasure]" as [Sum]
    from openquery(my_olap_server,
    'with member [Measures].[StateName]
    as [State].[Country].CURRENTMEMBER.MEMBER_CAPTION
    select {[Measures].[StateName],[Measures].[someMeasure]} on 0,
    filter([State].[Country].[Country],not isempty([Measures].[someMeasure])) on 1
    from (select {[State].[Country].[Country].&[index_here]} on columns from [My Cube])')

is the solution. Simple enough :)

Upvotes: 1

Related Questions