Post reply

Grouping in mdx

  • January 4, 2013 at 4:26 am

    #268908

    Hi all,

    Is grouping is possible in mdx or any other approach is there to achieve grouping....

    thanks

    Rahul

  • January 4, 2013 at 4:29 am

    #1573684

    Can you give an example?

    I don't really understand how you'd need to group in MDX as it is implicitly done

    Mack

  • January 4, 2013 at 5:01 am

    #1573693

    for example,

    i have time dimension,product dim and sales fact .

    when i try to select month and sales amount, i am getting repeated months( i.e if i have 4 years then its giving 48 months)

  • January 4, 2013 at 5:10 am

    #1573695

    So you want the sum of the data for a select number of months without months appearing in the query?

    Try one of three types of MDX to do this

    Select [product] on rows,

    [sales fact] on columns

    from [cube]

    where ({[Months]})

    or

    Select [product] on rows,

    [sales fact] on columns

    from (select {[Months]} on rows from [cube])

    or

    with

    member [measures].[Sum Fact] As Sum({[Months}, [Sales Fact])

    Select [product] on rows,

    [measures].[Sum Fact] on columns

    from [cube]

    The second one will be quicker but will limit all calculations to the sub cube you select which isn't always ideal if you are doing something related to calculations outside of the subcube

    Mack

  • January 4, 2013 at 5:27 am

    #1573701

    thanks for quick reply...

    but my requirement is like

    I'm getting

    month sales amount

    January 1234

    February 3232

    January 1111

    March 2312

    January 1255

    March 1232

    But i want

    Month Sales amount

    January 3600

    February 3232

    March 3544

  • January 4, 2013 at 5:39 am

    #1573704

    Can you post the MDX used?

    Raunak J

  • January 4, 2013 at 5:40 am

    #1573705

    Are you trying to sum months - so January is Jan 10 + Jan 11 + Jan 12 + Jan 13

    Or do you want to sum across products like so:

    Select [Months] on rows,

    [sales fact] on columns

    from [cube]

    where ({[product]})

    Mack

  • January 4, 2013 at 6:05 am

    #1573714

    yes.. i want Jan 10 + Jan 11 + Jan 12 + Jan 13 like this...

    thanks

    Rahul

  • January 4, 2013 at 6:19 am

    #1573719

    hmmmmmmmm

    Looks like you make have to add a Month Name attribute to you time dimension so it will group the months of the year

    MonthMonth Name

    Jan-10Jan

    Jan-11Jan

    Jan-12Jan

    Jan-13Jan

    Feb-10Feb

    Feb-11Feb

    Feb-12Feb

    Feb-13Feb

    Another alternative is to set up 12 calculated members in the date dimension like so

    with

    member [Date].[Month].[January] as

    Sum(filter([Date].[Month].[All].Children, LEFT([Date].[Month].CurrentMember.Name,3) = "Jan"))

    member [Date].[Month].[February] as

    Sum(filter([Date].[Month].[All].Children, LEFT([Date].[Month].CurrentMember.Name,3) = "Feb"))

    SELECT NON EMPTY { [Measures].[Sales Revenue] } ON COLUMNS ,

    NON EMPTY {[Date].[Month].[January],[Date].[Month].[February] } ON ROWS

    FROM [Cube]

    Mack

  • January 4, 2013 at 6:29 am

    #1573724

    thanks ....

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply