SQL Server Performance

Grouping and Totals

Discussion in 'SQL Server 2005 Analysis Services' started by gus, Jun 5, 2008.

  1. gus New Member

    Let's suppose I have one dimension [Dim] with 4 Attributes Attr1,Attr2,Attr3,Attr4.
    And of course 4 attribute Hierarchies [Dim].[Attr1], [Dim].[Attr2], [Dim].[Attr3], [Dim].[Attr4].
    There's no relationship betwen these attributes and no user hierarchy defined.
    I need a report grouped in this way Attr1 -> Attr2 -> Attr3 -> Attr4 but filtering by some condition in the lowest level.
    So first I do something like
    set MyFilteredSet as Filter ( [Dim].[Attr4].[Attr4] , <SomeCondition> )
    But I cannot see how can I define a crossjoined set with partial totals at each level.
    set MyRows as [Dim].[Attr1].[All].children * [Dim].[Attr2]. [All].children * [Dim].[Attr3].[All].children * MyFilteredSet
    This is perfect and Autoexists take care of empty rows, and it works as I need.
    But I need partial totals for each group, at each level.
    I've tried VisualTotals but it works just for the lowest level.
    I can't find a general solution with takes into account that the "lowest" level has been filtered and don't add up the filtered values in the above totals.
    On columns I have a couple of measures, it doesn't matter.
    Any help will be appreciated.

Share This Page