none
Aggregate measures only with some dimensions RRS feed

  • Question

  • Hi,

    I creating a SSIS cube with 3 dimensions - D1, D2, D3 and  some measures.

    The measures should aggregate through D2 and D3, but not D1. I can't aggregate values through more than 1 D1 element. 

    I think I should use custom rollup column property in D1 dimension to achieve this, but I can't figure how to write the expression.

    I tried this :

    IIF([Indicadores].CurrentMember.Level.Name="Grupo", Aggregate([Indicadores].CurrentMember),null)

    Obs : [Indicadores] is the dimension that can't aggregate data, D1.


    After I used this formula, all values disappears. I already tried Sum, instead aggregate and already tried only [Indicadores].CurrentMember, doesn't work. If I try a fixed value - like 1 - the fixed value appears. 

    Is this the correct way to achieve this ? How to write this expression ?

    Edit : Also tried [Indicadores].CurrentMember.MemberValue, it doesn't work.

    Thanks !


    Dennes - Se resolveu, classifique a mensagem, por favor - [http://www.bufaloinfo.com.br] NOVO DVD Segurança no ASP.NET : http://www.bufaloinfo.com.br/LearingSeriesSegurancaASPNET2.asp



    • Edited by Dennes Friday, July 12, 2013 5:04 PM
    Friday, July 12, 2013 4:45 PM

Answers

All replies

  • A common scenario where you want to aggregate through all dimensions except 1 is when currency is involved.  In that case you don't want to aggregate values through the currency dimension.

    This is typically handled via a dimension setting called IsAggregatable:
    http://msdn.microsoft.com/en-us/library/ms174497.aspx

    If the IsAggregatable property is set to False on the source attribute of a level in a hierarchy, then no aggregatable level can appear in the hierarchy above that level. A non-aggregatable level must be the topmost level of any hierarchy or the IsAggregatable property of the source attributes for any levels above it must also be set to False.


    BI Developer and lover of data (Blog | Twitter)

    Friday, July 12, 2013 5:05 PM
  • Hi, Bill,

    Thank you !

    It became better, but it seems that instead aggregate, it takes the first value of a group of items in D1. I would prefer to display null in situations that aggregation isn't possible.

    Is there some way to solve this ?

    Thank you !



    Dennes - Se resolveu, classifique a mensagem, por favor - [http://www.bufaloinfo.com.br] NOVO DVD Segurança no ASP.NET : http://www.bufaloinfo.com.br/LearingSeriesSegurancaASPNET2.asp

    Friday, July 12, 2013 5:55 PM
  • An additional property you will probably want is the DefaultMember - this is what is used when you do not specify a member for that attribute.
    Friday, July 12, 2013 6:00 PM
  • Hi, Justin,

    I tried to use null as defaultMember, but without result.

    Thank you !


    Dennes - Se resolveu, classifique a mensagem, por favor - [http://www.bufaloinfo.com.br] NOVO DVD Segurança no ASP.NET : http://www.bufaloinfo.com.br/LearingSeriesSegurancaASPNET2.asp

    Friday, July 12, 2013 6:30 PM
  • You can't use NULL as a default member...the default member has to be a member that actually exists.

    You can make it appear as NULL (using SCOPE statements) but it can't actually be NULL...otherwise you wouldn't be able to display a cell-value because the coordinates in the cube-space would be incomplete.


    BI Developer and lover of data (Blog | Twitter)

    Wednesday, July 17, 2013 4:31 PM
  • IIF([Indicadores].CurrentMember.Level.Name="Grupo", Aggregate([Indicadores].CurrentMember),null)


    Dennes - Se resolveu, classifique a mensagem, por favor - [http://www.bufaloinfo.com.br] NOVO DVD Segurança no ASP.NET : http://www.bufaloinfo.com.br/LearingSeriesSegurancaASPNET2.asp



    Hi Dennes,

    Please refer to the following Aggregate() function syntax and example:
    Aggregate(Set_Expression [ ,Numeric_Expression ])

    For more information, please see:
    Aggregate (MDX): http://technet.microsoft.com/en-us/library/ms145524.aspx

    Generally, if the DefaultMember setting for an attribute is blank and its IsAggregatable property is set to True, the default member is the All member. If the IsAggregatable property is set to False, the default member is the first member of the first visible level. For more information, please see:
    http://technet.microsoft.com/en-us/library/ms174822(v=sql.105).aspx

    If you have any feedback on our support, please click here.

    Best Regards,


    Elvis Long
    TechNet Community Support

    Thursday, July 18, 2013 7:36 AM
    Moderator