CODEWITHSUNDEEP
ssasmdx
zlatkovin
zlatkovin

Reputation: 25

Sum and grouping in MDX query

I need an MDX query to sum data from one column and aggregates data from another by its type. For examply, my current query is:

    SELECT 
 { [Measures].[DeclCount], [Measures].[SubCount]} ON COLUMNS, 
 {  (
 [Organization].[OrganizationKey].[OrganizationKey].ALLMEMBERS * 
 [Organization].[Name].[Name].ALLMEMBERS * 
 [Organization].[Need Declaration10].[Need Declaration10].ALLMEMBERS *  
 [DeclType].[DeclarationNumber].[DeclarationNumber].ALLMEMBERS
 )
}  
  ON ROWS
FROM 
(
    SELECT
        DESCENDANTS([Organization].[OrganizationKey].CHILDREN) on COLUMNS
    FROM 
    (
        SELECT ([Organization].[Code].&[0105044397] ON COLUMNS
        FROM [MyCube]
    )
)

Current result is: Code - Name - Need Declaration10 - DeclType - DeclCount - SubCount Code1 - Organization1 - 0 - 1 - NULL - NULL Code1 - Organization1 - 1 - 2 - 1 - 2 Code1 - Organization1 - 1 - 3 - 2 - 2

Desired result is: 

Code - Name - Sum(Need Declaration10) - SumDeclCount1 - SumSubCount1 - SumDeclCount2 - SumSubCount2 - SumDeclCount3 - SumSubCount3
Code1 - Organization1 - 2 - NULL - NULL - 1 - 2 - 2 -2

How can i achieve this with MDX? Thanks for any help.

Edit: Thanks Frank, it's almost everything I need. The last two things are: 1. I have 10 different [DeclType].[DeclarationNumber]. Is it possible to modify ([DeclType].[DeclarationNumber].All, Measures.[Sum of Need Declaration10]) to calculate the sum for each of them? 2. I need to populate SSRS report with this query. Is it possible to get flattened dataset from this query? Ideal soultion would be set like (OrganizationKey, OrganizationName, DeclCount_X, SubCount_X, SumOfNeed_X) where X=1..10

Upvotes: 1

Views: 4957

Answers (1)

FrankPl
FrankPl

Reputation: 13315

As I already stated in the comment, this is more difficult than it should be as [DeclarationNumber] is an attribute/hierarchy but not a measure. I hope my workaround below will work, assuming that the [DeclarationNumber] only contains integer values. Nevertheless, they have to be converted from String to a numeric type to be able to use them similar to a measure.

The easy part is moving the moving the [DeclType].[DeclarationNumber] to the columns.

WITH MEMBER Measures.[Declaration10 numeric] as
            CInt([Organization].[Need Declaration10].CurrentMember.Name)
     MEMBER Measures.[Sum of Need Declaration10] as
            Sum(EXISTING [Organization].[Need Declaration10].[Need Declaration10].Members,
                Measures.[Declaration10 numeric]
            )
SELECT { ( 
           [DeclType].[DeclarationNumber].All,
           Measures.[Sum of Need Declaration10]
       ) }
       +
       (
         [DeclType].[DeclarationNumber].[DeclarationNumber].Members
         *
         {[Measures].[DeclCount], [Measures].[SubCount]}
       )
       ON COLUMNS,

       [Organization].[OrganizationKey].[OrganizationKey].ALLMEMBERS
       * 
       [Organization].[Name].[Name].ALLMEMBERS
       ON ROWS
FROM 
(
    SELECT
        DESCENDANTS([Organization].[OrganizationKey].CHILDREN) on COLUMNS
    FROM 
    (
        SELECT ([Organization].[Code].&[0105044397] ON COLUMNS
        FROM [MyCube]
    )
)

As I do not have access to your cube, there may be some tweaks necessary, be it to syntax or to some logic of the statement.

Upvotes: 1

Related Questions