Warm tip: This article is reproduced from stackoverflow.com, please click
mdx ssas ssas-2016

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

发布于 2020-06-19 15:05:19

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] ;
Questioner
Jens-Peter Giersch
Viewed
11
vldmrrdjcc 2020-04-03 17:29

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'