Creating Customized MDX Named-Sets

mdx, ssas

Solution

You must create separate `SessionDescription` members if you want to see separate entries on the rows e. g. like this:

WITH
     MEMBER [SessionLength].[Session Length].Glimpse AS
            Aggregate([SessionLength].[Session Length].&[0])
     MEMBER [SessionLength].[Session Length].Short AS
            Aggregate([SessionLength].[Session Length].&[1]:[SessionLength].[Session Length].&[5])
     MEMBER [SessionLength].[Session Length].Medium AS
            Aggregate([SessionLength].[Session Length].&[6]:[SessionLength].[Session Length].&[30])
     MEMBER [SessionLength].[Session Length].Long AS
            Aggregate([SessionLength].[Session Length].&[31]:[SessionLength].[Session Length].&[90])
     MEMBER [SessionLength].[Session Length].Extended AS
            Aggregate([SessionLength].[Session Length].&[90]:[SessionLength].[Session Length].&[240])
     SELECT
     { [Measures].[Count - Logins] }
     ON COLUMNS,
    NONEMPTY({
             [SessionLength].[Session Length].Glimpse,
             [SessionLength].[Session Length].Short,
             [SessionLength].[Session Length].Medium,
             [SessionLength].[Session Length].Long,
             [SessionLength].[Session Length].Extended
             }
             * {[Date].[Date].[Date]})
    ON ROWS
FROM MyCube

By the way, I left the `90` member in both, `Long` and `Extended` as it was in your original query. If you do not want to double-count these, you should remove them from one.

Problem

I have two dimensions called `[Session Length]` and `[Date]`, and a measure called `[Count - Logins]` in my Cube. The `[Session Length]` dimension contains an attribute called `[Session Length]` whose members are integers from `0` to `240`. I like to write an MDX query which would aggregate `[Count - Logins]` over customized subsets of the `[Session Length]` dimension (i.e. I want to create a customized set based on the `[Session Length]` dimension and aggregate the count over individual members of this custom set). Here is the query I have come up with so far but unfortunately I have no clue how to move forward: ``` WITH SET [Description] AS { [SessionLength].[Session Length].&[0], //Glimpse [SessionLength].[Session Length].&[1]:[SessionLength].[Session Length].&[5], //Short [SessionLength].[Session Length].&[6]:[SessionLength].[Session Length].&[30], //Medium [SessionLength].[Session Length].&[31]:[SessionLength].[Session Length].&[90], //Long [SessionLength].[Session Length].&[90]:[SessionLength].[Session Length].&[240]} //Extended MEMBER [SessionLength].[Session Length].SessionDescription AS Aggregate([Description]) SELECT { [Measures].[Count - Logins] } ON COLUMNS, NONEMPTY({[SessionLength].[Session Length].SessionDescription} * {[Date].[Date].[Date]}) ON ROWS FROM MyCube ``` With the following sample result set: ``` Session Length | Date | Count - Logins ------------------------------------------------- SessionDescription | 2014-02-01 | 22 SessionDescription | 2014-02-01 | 17 ``` As you can see the count is being aggregated over the whole set and not each member individually. Here is the result I'm hoping to produce: ``` Session Length | Date | Count - Logins ------------------------------------------------- Glimpse | 2014-02-01 | 3 Short | 2014-02-01 | 4 Medium | 2014-02-01 | 9 Long | 2014-02-01 | 5 Extended | 2014-02-01 | 1 Glimpse | 2014-02-02 | 2 Short | 2014-02-02 | 5 Medium | 2014-02-02 | 7 Long | 2014-02-02 | 2 Extended | 2014-02-02 | 1 ``` Any help would be appreciated. I know this can be achieved by modifying the DSV but I don't want to alter the Cube's structure.

Original source