CODEWITHSUNDEEP
ssisfilterssasmdx
Alexo
Alexo

Reputation: 131

SSIS MDX Query Problem

Hallo at all!

I have a little Problem with my Query in MDX.

I try to query up the Damage Repair Types from my Cube. Next i explain my Dimension and the Fact Table:

Dimension: Demage Repair Type

RepairTypeKey | Name | RepairTypeAlternateKey | RepairSubTypeAlternateKey | SubName
0 |Unknown |0 | NULL | NULL
1 |Repair |1 |1 | 1 Boil
2 |Replacement |2 |NULL | NULL
3 |Repair |1 |2 | 2 Boils
4 |Repair |1 |3 | 3 Boils

So I have in my Fact Table "CLaimCosts" for every Claim one RepairTypeKey. I Fill the Tables and design a Cube. The Dimension have a Hirarchy with RepairType and SubRepairType. I Process the Cube and it works Fine:

Now I Create a Query with MDX:

select
    {
        [Measures].[Claim Count],
        [Measures].[Claim Cost Position Count],
        [Measures].[Claim Cost Original],
        [Measures].[Claim Cost Original Average],
        [Measures].[Claim Cost Possible Savings],
        [Measures].[Claim Cost Possible Savings Average],
        [Measures].[Claim Cost Possible Savings Percentage] 
    } on 0,

    NON EMPTY{
        NonEmpty([Damage Repair Type].[Hierarchy].Allmembers, ([Measures].[Claim Count]))
    } on 1

    from 
        Cube

    where 
    (
        ({StrToMember(@DateFrom) : StrToMember(@DateTo)})
        ,([Claim Document Type].[Document Type].&[4])


    )

Now i try to Run the Query and it Works but i have to much Rows Shown:

Demage Repair Type | Demage Repair Sub Type | Claim Count | ....
NULL |NULL | 200000
Replacement | NULL | 150000
Repair | NULL | 45000
Repair | 1 Boil | 10000
Repair | 2 Boil | 15000
Repair | 3 Boil | 19000
Unknown |NULL | 1000

My Problem are the frist Row (Sum) and the third Row (Sum)! I don't need this Rows but I don't know how to Filter them! I don't need this Sums because i have the Childs with the right Counts!

How I can Filter this? Please help me. It doesn't work!

Sorry for my bad English and Thank you!

Alex

Upvotes: 1

Views: 798

Answers (1)

Boyan Penev
Boyan Penev

Reputation: 854

NonEmpty([Damage Repair Type].[Hierarchy].Allmembers, ([Measures].[Claim Count]))

You can use:

NonEmpty([Damage Repair Type].[Hierarchy].Levels(2).Members, [Measures].[Claim Count])

This way we exclude the All members. Also, when you use the level members (e.g. [dim].[hier].[lvl].Members) instead of the hierarchy members (e.g. [dim].[hier].members) you don't get the aggregate members - e.g. the All member which is commonly present in all hierarchies other than non-aggregatable attribute hierarchies.

Upvotes: 1

Related Questions