Search code examples
ssasgroupingmdx

how to grouping by first letter in mdx ssas query

My MDX script: 

 SELECT NON EMPTY { [Measures].[Fact Emp Violation Count] } ON COLUMNS, NON EMPTY { ([Dim Date].[Calendar].[Year].ALLMEMBERS * [Dim Warning Level].[Violance Hierarchies].[Violance Type].ALLMEMBERS ) }  ON ROWS FROM ( SELECT ( { [Dim Date].[Calendar].[Year].&[2015] } ) ON COLUMNS FROM [Violation])

My tables:

enter image description here

I want to group based on violence type first letters

My desired outputs is:

Year     | Violence Type | Fact Emp Violation Count

CY 2015  |       1       | 19 + 18 + 2 + 23 + (violence type begins with '1')

CY 2015  |       2       | 11 + 4 + 1 + (violence type begins with '2')

Thanks.

Solution

  • First you need to do a small cube design change for the solution to be optimal. In the DSV, add a named column and call it [New Violance Type]. Logic for that field is straightforward -

    =left([Violance Type], 1)

    My computing it before hand and storing that in the cube does wonders to your MDX query response time. Although it can be computed as a part of MDX too, but it decreases the performance.

    Add this field to the same dimension as a new attribute hierarchy - [dim warning level].[New Violance Type]

    Then you can use the below MDX - 

    select non empty { [measures].[fact emp violation count] } on columns, 
non empty { ([dim date].[calendar].[year].allmembers * [dim warning level].[New Violance Type].allmembers ) }  on rows 
from ( select ( { [dim date].[calendar].[year].&[2015] } ) on columns from [violation])