CODEWITHSUNDEEP
ssasmdxssas-2016
Jens-Peter Giersch
Jens-Peter Giersch

Reputation: 23

SSAS MDX: Calculated member doesn't show up in Excel

I created the calculated member as described below in SSMS (not VS, therefore I did not deploy it) by selecting the command and executing it. Accessing this member in MDX works out fine. But Excel doesn't show me this measure, and I also do not see it in the cube browser. I expect to find it below the measure groups.

Question: What did I miss?

Additional question: How can I place this measure in a measure group?

CREATE MEMBER [Logistics].[Measures].[Printed] AS 
    SUM
    ( 
        {
            (
                [Pick].[Pick Method].&[4]
                , EXCEPT([Pick].[Pick Type].MEMBERS, [Pick].[Pick Type].&[A])
                , EXCEPT([Loc].[Build Zone].MEMBERS, {[Loc].[Build Zone].[All], [Loc].[Build Zone].&[G1], [Loc].[Build Zone].&[G2], [Loc].[Build Zone].&[G3]})
                , EXCEPT([Loc].[Loc Code].MEMBERS, {[Loc].[Loc Code].[All], [Loc].[Loc Code].[EXPRESS]})
            )
        }
        , [Measures].[Nb of Pick lines]
    ) ;

Executing this using this MDX-SELECT works out fine and returns a sensible result:

SELECT 
        NON EMPTY
        {
            [Location].[Build Zone].MEMBERS, [Location].[Build].[Tested]
        } ON COLUMNS
            , NON EMPTY {[Calendar].[Calendar Year].[Month].&[2019]&[August].children} ON ROWS  
    FROM [Logistics] ;

Upvotes: 1

Views: 499

Answers (1)

vldmrrdjcc
vldmrrdjcc

Reputation: 2112

In SSMS you can only create Session-scoped calculated members, as described here

And what you did is that you successfully created this member, and in the same query window if you run this query:

select measures.allmembers on 0
from [Logistics]

you will actually see [Measures].[Printed] member. But as soon as you open a New Query window in SSMS on this cube, and run select measures.allmembers on 0 from [Logistics] again, you will not see your calculated member anymore.

So, the solution is to add this create member script in the Calculation Script in Visual Studio, as you mentioned, and to deploy the cube.

Additionally, to place a measure in measure group, or in some folder under it you can use ASSOCIATED_MEASURE_GROUP and DISPLAY_FOLDER properties. So something like this:

create member [MyCube].Measures.MyMeasure as 999, ASSOCIATED_MEASURE_GROUP = 'My measure group', DISPLAY_FOLDER = 'My display folder'

Upvotes: 1

Related Questions