locked
[All] Member Value and SET's RRS feed

  • Question

  • Hello everybody,

    I'm having a real problem regarding the [All] member value.

    Inside a SET is there any way of making it exclude some of the members of a Dimension?

    For example:

    SET [t] AS ({[Dim].[Dim].[All]})

    Includes all the members of that dimension, but if the same set is included in a select where that same dimension is filtered and only should consider 1 member, that set still considers all the members...

    Example:
    Dim as 2 values --> A and B
    When I look Dim with the Measure X, A as value 10 (for example) and B holds the value 15.

    When I look at the set value without restrictions, the [All] returns 25 (considering its a sum, for simplification) but when I want to use the set inside a query that includes only the A value, the set still returns the value 25 and not only 10...


    I really need to look at the [All] value because of aggregations that I make (not all the measures are "summable") so I need the [All] to believe it contains only one element instead of two...

    I hope I've explained the problem.

    Thanks in advance,
    Best regards,
    João Teixeira
    Friday, December 26, 2008 3:39 PM

Answers

  • You can create a calculated measures with your newly created aggregate member to reference.  Going back to the AdventureWorksDW it would simply look like the following:

    with set [all minus bikes] as except(product.category.children, [Product].[Category].&[1])

    member [product].category.[all minus bikes] as aggregate([all minus bikes])

    member measures.[reseller sales amount minus bikes] as ([product].category.[all minus bikes], measures.[reseller sales amount])



    select {measures.[reseller sales amount],measures.[reseller sales amount minus bikes]} on 0,

    [product].category.children on 1

    from [adventure works]

     

    This was you don't need to put the aggregate member on the rows, but you still get the value.  It will be the same value for each row in my example, but if you referenced a different dimension on the rows like Date then you would see the different values based on that particular slice.


    Dan English -- http://denglishbi.spaces.live.com
    Friday, December 26, 2008 9:33 PM

All replies

  • The [All] is an aggregate member and you can't just exclude members from it.  You can create your own custom aggregated member based off a set of members that you want to evaluate.  Here is an example from AdventureWorksDW:

    with set [all minus bikes] as except(product.category.children, [Product].[Category].&[1])

    member [product].category.[all minus bikes] as aggregate([all minus bikes])

    select measures.[reseller sales amount] on 0,

    [product].category.[all minus bikes] on 1

    from [adventure works]


    Guess I am still trying to determine how you are trying to reference this and use this to return the results.


    Dan English -- http://denglishbi.spaces.live.com
    Friday, December 26, 2008 4:12 PM
  •  Hi,

    thanks Dan!!

    I've understand your solution, but the Aggregate([SET]) is return #Error, must try to understand why...

    Best regards,
    João Teixeira
    Friday, December 26, 2008 4:53 PM
  • Hi again,

    the query is return the following error:

    Aggregate functions cannot be used on calculated members in the Measures dimension.


    The query is:

    WITH

    --SET A AS [Direcção].[Direcção].CHILDREN

    SET [A] AS EXCEPT([DimX].[DimX].CHILDREN,[DimX].[DimX].&[Val1])
    MEMBER [FILTERED] as AGGREGATE([A])
    SELECT [A] ON COLUMNS,
    {[FILTERED]}
    ON ROWS
    FROM
    [PTC_CA_SERV_CLI]

    What could be causing this?

    Thanks in advance,
    Best regards.
    João Teixeira

    Friday, December 26, 2008 5:21 PM
  • You need to create the aggregated member within your dimension like:

    WITH MEMBER [DimX].[DimX].[A] as Aggregate([A])

    then you can reference the member.  You can not use the Aggregate funtion within a measure, but you can reference the aggregate member within a calculation. 

    Would need to see all of your MDX.

    It should work though if you create the set with your filter, create the aggregate member in your dimension, and then reference the aggregated member in your query.

     


    Dan English -- http://denglishbi.spaces.live.com
    Friday, December 26, 2008 5:28 PM
  •  Dan,

    first of all, many thanks for your help.

    Your suggestion gives me the value I need but not in the way I need :(... let me try to explain:

    WITH
    SET [A] AS [Dim].[Dim].&[Val1]}
    MEMBER [Ind_VAL] AS SUM([Dim2].[Dim2].&[Val], [Measures].[Val] )
    MEMBER
    [Dim].[Dim].[T] AS AGGREGATE([A])
    SELECT
    {
    [Ind_VAL]
    }
    ON columns,
    (
    {
    [Dim].[Dim].
    CHILDREN,
    [Dim].[Dim].[T]
    }
    *
    [Tempo].[Data].
    CHILDREN
    )
    ON rows
    FROM
    [PTC_CA_SERV_CLI]



    This query returns the value I need to know, but what I really wanted is to get it in like a Measure and not like a new "different" value for the dimension "Dim"...

    This way I could get that value in the Report I'm building with this query... speccialy because I want that value to represent the aggregation of that dimension in a time interval.

    Is it understandable?
    Best regards,
    Many Thanks,
    João Teixeira
    Friday, December 26, 2008 6:06 PM
  • You can create a calculated measures with your newly created aggregate member to reference.  Going back to the AdventureWorksDW it would simply look like the following:

    with set [all minus bikes] as except(product.category.children, [Product].[Category].&[1])

    member [product].category.[all minus bikes] as aggregate([all minus bikes])

    member measures.[reseller sales amount minus bikes] as ([product].category.[all minus bikes], measures.[reseller sales amount])



    select {measures.[reseller sales amount],measures.[reseller sales amount minus bikes]} on 0,

    [product].category.children on 1

    from [adventure works]

     

    This was you don't need to put the aggregate member on the rows, but you still get the value.  It will be the same value for each row in my example, but if you referenced a different dimension on the rows like Date then you would see the different values based on that particular slice.


    Dan English -- http://denglishbi.spaces.live.com
    Friday, December 26, 2008 9:33 PM