0845 643 64 63

Querying dimension members in MDX

Today we’re back looking at MDX with a quick how-to. How do you extract a list of dimension members using MDX?

This could be used for example to populate a user interface/parameter of a report/dashboard.

Let’s look at two scenarios; normal dimensions, and then hierarchies. All the queries here use the “Adventure Works DW 2008 R2” “Adventure Works” cube.

Standard dimension attributes

So how do we get a list of dimension members? Well lets divide the query up into rows and columns. We want the members listed on the rows.

1
2
3
4
SELECT
  xxx ON COLUMNS,
  [Geography].[Country].ALLMEMBERS ON ROWS
FROM [Adventure Works]

Why use .ALLMEMBERS instead of .MEMBERS? .ALLMEMBERS will include calculated members, whereas .MEMBERS won’t.

So what do we put on the rows? We want the display name for showing to the user, and the unique name so we can refer back to each exact member. We can get these by using properties of the current member, namely MEMBER_CAPTION and UNIQUENAME. Get a full list of dimension member properties here. To make this work, we just define a number of query-scoped calculated members using WITH, and then include them in the columns set…

1
2
3
4
5
6
7
8
WITH 
   MEMBER [Measures].[Label] AS [Geography].[Country].CURRENTMEMBER.MEMBER_CAPTION 
   MEMBER [Measures].[UniqueName] AS [Geography].[Country].CURRENTMEMBER.UNIQUENAME 
SELECT {[Measures].[Label], 
        [Measures].[UniqueName]
        } ON COLUMNS , 
      [Geography].[Country].ALLMEMBERS ON ROWS 
FROM [Adventure Works]

If you run this, you get the following output:

This may be what you want, but note that the first row of the result set contains the ‘ALL’ member, which you probably don’t want. You can remove this by altering the ROWS clause to exclude them. This is simply a case of repeating the hierarchy name, e.g. [Geography].[Country].[Country].ALLMEMBERS

1
2
3
4
5
6
7
8
WITH 
   MEMBER [Measures].[Label] AS [Geography].[Country].CURRENTMEMBER.MEMBER_CAPTION 
   MEMBER [Measures].[UniqueName] AS [Geography].[Country].CURRENTMEMBER.UNIQUENAME 
SELECT {[Measures].[Label], 
        [Measures].[UniqueName]
        } ON COLUMNS , 
      [Geography].[Country].[Country].ALLMEMBERS ON ROWS 
FROM [Adventure Works]

Hierarchies

With simple dimension attributes, you get a flat list of values. With hierarchies, whether standard user hierarchies, ragged or parent-child, you need to return a multi-level list. To do this we need to know what level each member is at. We can query this using the LEVEL.ORDINAL property. Adding this into the query, and replacing Geography for Employee, we get the following query:

1
2
3
4
5
6
7
8
9
10
WITH 
   MEMBER [Measures].[Label] AS [Employee].[Employees].CURRENTMEMBER.MEMBER_CAPTION 
   MEMBER [Measures].[UniqueName] AS [Employee].[Employees].CURRENTMEMBER.UNIQUENAME 
   MEMBER [Measures].[Ordinal] AS [Employee].[Employees].CURRENTMEMBER.LEVEL.ORDINAL 
SELECT {[Measures].[Label], 
        [Measures].[UniqueName], 
        [Measures].[Ordinal]
        } ON COLUMNS , 
      [Employee].[Employees].ALLMEMBERS ON ROWS 
FROM [Adventure Works]

With the following results:

You can then do all sorts of funky things using basic MDX navigation functions. e.g. returning the parent of each member, by simply adding …PARENT.UNIQUENAME as another column

1
2
3
4
5
6
7
8
9
10
11
12
WITH 
   MEMBER [Measures].[Label] AS [Employee].[Employees].CURRENTMEMBER.MEMBER_CAPTION 
   MEMBER [Measures].[UniqueName] AS [Employee].[Employees].CURRENTMEMBER.UNIQUENAME 
   MEMBER [Measures].[Ordinal] AS [Employee].[Employees].CURRENTMEMBER.LEVEL.ORDINAL 
   MEMBER [Measures].[Parent] AS [Employee].[Employees].PARENT.UNIQUENAME
SELECT {[Measures].[Label], 
        [Measures].[UniqueName], 
        [Measures].[Ordinal],
        [Measures].[Parent]
        } ON COLUMNS , 
      [Employee].[Employees].ALLMEMBERS ON ROWS 
FROM [Adventure Works]

Now go forth and query those dimension members…
FrogBlog-Out

19 Responses to Querying dimension members in MDX

  • I’ve been scouring the internet looking for a solution to an MDX query problem. Common business practice would dictate that a hierarchy is more commonly used in pieces, most likely descendants of a specified member(s). This requires some sort of filtering mechanism. SUBCUBE and SUB SELECTS would be an obvious choice if it weren’t for the fact that they always return all members above and below the selected member(s). That kind of defeats the purpose of the filter. This MDX will demonstrate the point. If you toggle SELF to SELF_AND_AFTER the results do not change even though SELF eliminates the descendants.

    SELECT {[Measures].[Sales Amount Quota]} ON COLUMNS,
    [Employee].[Marital Status].MEMBERS
    ON ROWS
    FROM (SELECT
    {DESCENDANTS(
    NONEMPTY(
    FILTER([Employee].[Employees].MEMBERS
    ,[Employee].[Employees].PROPERTIES(“Gender”) = “Female”)
    )
    ,,SELF)} ON COLUMNS
    FROM [Adventure Works]
    )

    What is the best way to filter a parent/child hierarchy in this circumstance?

    • Hi Jeff

      Despite the query results staying the same, changing SELF to SELF_AND_AFTER does actually change the functionality of the query. To explain, run the following queries…

      SELECT {[Measures].[Sales Amount Quota]} ON COLUMNS,
      {DESCENDANTS(
      NONEMPTY(
      FILTER([Employee].[Employees].MEMBERS
      ,[Employee].[Employees].PROPERTIES(“Gender”) = “Female”)
      )
      ,,SELF)} ON ROWS
      FROM [Adventure Works]

      and

      SELECT {[Measures].[Sales Amount Quota]} ON COLUMNS,
      {DESCENDANTS(
      NONEMPTY(
      FILTER([Employee].[Employees].MEMBERS
      ,[Employee].[Employees].PROPERTIES(“Gender”) = “Female”)
      )
      ,,SELF_AND_AFTER)} ON ROWS
      FROM [Adventure Works]

      They return different numbers of rows. The reason is due to the nature of what your query is doing. In the first query we’re finding all female employees. Which includes female employees and female bosses. (‘SELF’ prevents the query from returning subordinates of the female bosses, so the DESCENDANTS function is irrelevant here).

      The second query is finding all descendants of all female employees. This includes female employees, female bosses, as well as all descendants (male or female) for all female bosses.

      In a parent/child hierarchy the values of children are included in the value of the parent. So the value of a female boss is the same whether or not you include her subordinates, unless you ask for the member’s DataMember, which specifically excludes the child values from the aggregation.

      Does that make any sense?

      There are countless ways of writing descendants, filters, etc. for parent/child dimensions. If you let me know exactly what query results you’re trying to get then I can put together a query for you.

      Alex

      • The point is on filtering or limiting the Employees parent child hierarchy. You’ve moved the DESCENDANT function to the ROW axis which eliminates the filtering problem I am explaining, but also changes the granularity of the ROW axis. I want to use Employees hierarchy strictly as a filter, unfortunately executing it in a SUBCUBE or SUB SELECT causes the result to always include members above and below the selected members. If you toggle between SELF and SELF_AND_AFTER the results are the same.

        SELECT {[Measures].[Sales Amount Quota]} ON COLUMNS,
        [Employee].[Employees].MEMBERS ON ROWS
        FROM (SELECT
        {DESCENDANTS(
        NONEMPTY(
        FILTER([Employee].[Employees].MEMBERS
        ,[Employee].[Employees].PROPERTIES(“Gender”) = “Female”)
        )
        ,,SELF)} ON COLUMNS
        FROM [Adventure Works]
        )
        How would I predictably limit the members of Employees and use that set to evaluate across another attribute as my original example shows (Marital Status)? I need the option of parametizing between SELF and SELF_AND_AFTER. Unfortunately, the sub select behavior invalidates the DESCENDANT using SELF.

        • Yes I changed the nature of the query in order to explain what your queries were doing behind the scenes. You said they were doing the same thing, which is incorrect. They are doing different things albeit with the same results. Working with any MDX it’s vital to have a clear understanding of what the filtering and functions are doing in order to diagnose any query problems.

          The problem in your example is not that the subselect is invalidating the descendents, it’s that the value for each parent includes the values of all children. Therefore when you’re aggregating the results it makes no difference whether the children are there or not, so the difference between SELF and SELF_AND_AFTER is totally meaningless in your example, and so you shouldn’t be parameriterizing it in the first place!

          e.g. using the adventureworks data…
          The total quota for Amy E. Alberts is $24,202,000. The total value for Amy E. Alberts, and all of her descendants, is still $24,202,000
          If you want the value for JUST Amy, not including her descendants, then you have two options
          1) Use the .DataMember of each employee in the hierarchy, which means use the value of this employee EXCLUDING the values of any children
          2) Don’t use the hierarchy. Just use [Employee].[Employee].MEMBERS instead of the [Employee].[Employees] hierarchy. Then each employee will be treated individually.

          Use the first option when you want to retain filtering on the hierarchy, use the 2nd when you don’t.

          To get the value of the datamember, just use ([Measures].[Sales Amount Quota], [Employee].[Employees].DATAMEMBER) instead of [Measures].[Sales Amount Quota]. To make this work you can’t use a subselect, so you need to restructure the query somewhat.

          Be careful though when using DESCENDANTS on top of a filter statement, you run the risk of double counting values. e.g. a female employee will be counted twice if she has a female boss. This is because both women will be returned from the filter, therefore descendants will operate on them both. So you should use the DISTINCT function to protect against this.

          Basically, the following query should show you a way of doing what you want, leaving you to parameterize the SELF/SELF_AND_AFTER

          WITH MEMBER [Measures].[Individual Quota] AS
          ([Measures].[Sales Amount Quota]
          , [Employee].[Employees].DATAMEMBER)

          MEMBER [Measures].[Quota for Female with subordinates] AS
          (
          SUM(
          DISTINCT(DESCENDANTS(
          FILTER([Employee].[Employees].MEMBERS
          ,[Employee].[Employees].PROPERTIES(‘Gender’) = ‘Female’)
          ,,SELF_AND_AFTER))
          , [Measures].[Individual Quota]
          )
          ),FORMAT_STRING=’Currency’

          MEMBER [Measures].[Quota for Female without subordinates] AS
          (
          SUM(
          DISTINCT(DESCENDANTS(
          FILTER([Employee].[Employees].MEMBERS
          ,[Employee].[Employees].PROPERTIES(‘Gender’) = ‘Female’)
          ,,SELF))
          , [Measures].[Individual Quota]
          )
          ),FORMAT_STRING=’Currency’

          SELECT {[Measures].[Sales Amount Quota], [Measures].[Quota for Female with subordinates], [Measures].[Quota for Female without subordinates]} ON COLUMNS,
          [Employee].[Marital Status].MEMBERS
          ON ROWS
          FROM [Adventure Works]

  • Hi,

    The Member_Key we can get by the method suggested in the post, but when we get the Member_Key, Id as Measures/members then performance is very slow.
    Is there any way i can get the Member_key in a set in rows section.

    Like

    With SET [UniqueName] AS [Geography].[Country].CURRENTMEMBER.UNIQUENAME

    Select Measures.Value on Columns,
    UniqueName on Rows
    From Cube

    I want the value as just like
    Value
    Employees, UniqueName

    Please urgently required this.

  • while fetching data from SSAS cubed (in MDX format) in datazen only measure values are fetching not dimension values..???
    please help me finding the reason and solving it….!!!!

  • Hi there Alex, this question is unrelated to this post however I just want to ask anyway. I have struggled with the impact of my new Date Tool Util Dimension. I found after releasing that the users are no longer able to use Excel’s built-in everything. While I have worked around the sorting, I am stumped with the lack of filtering. My first effort was creating a dynamic set for the Top/Bottom 20 (based on the Prior YTD Diff) that gave me the correct results but restricted the ability to “Show on Report” the related attributes, so I add the attribute as a hierarchy column which causes unwanted subtotal rows which cannot be removed.
    Users are stuck viewing a single piece of the Dimension w/o causing the undesired results.
    I thought someone in google land would have an answer but I haven’t found it. I am probably too green to know the right phrase to search. What would be ideal at this point is somehow using axis() to dynamically apply TOP/BOTTOM n to whatever is currently selected in the pivottable. (Or any other suggestion that would allow filtering on these “ghost” dimension members.
    I have been wearing blinders for several days on this problem so while this message makes perfect sense in my world, I may be leaving out major details that allow this to make sense to anyone else, but if you follow and have any suggestions, help a girl out πŸ™‚
    Thanks in advance, your articles have streamlined my efforts in so many areas.

  • Hi Alex
    I am new to MDX. I have a cube with few dimensions. Report Date and Account are two of them. One account id may come under multiple Report dates. I need to find minimum report date for every accountid.
    for eg i have below data set in cube
    Report Date Account Id
    20160101 4
    20160101 2
    20160102 2
    20160102 3
    20160102 1
    20160102 4
    20160103 1
    20160103 2
    20160103 3

    and i want the below result through mdx

    Report Date Account Id
    20160102 1
    20160101 2
    20160102 3
    20160101 4

    ie the minimum date for every Accountid.
    Any ideas? I really do appreciate the help.
    Thanks
    Anubhav

  • MDX – search by member name:

    How can I filter/search by the name of the member and not its ID. I need a simple way to replace ID β€œ1002” (that can vary in a different environment) to the string β€œApple” – meaning, instead of writing β€œ1002” I will write β€œApple”.
    Suggestions?

    Thank you

    with

    member [Measures].[Apple_Active] as
    ([Device App Version].[App Name].&[1001],–TypeA
    [Model and Manufacturer].[Manufacturer].&[1002],–Apple
    [Measures].[Number of Active Users Past 30 Days])

    select
    [Measures].[Apple_Active]

    on 0
    from users

     

    • Hi Yoni

      It’s all to do with the ampersand
      If the ampersand is used then it specifies an ID
      If the ampersand is not used then it specifies the member name

      e.g.
      [Model and Manufacturer].[Manufacturer].&[1002]
      [Model and Manufacturer].[Manufacturer].[Apple]

      Regards
      Alex

  • Hi Alex,

    I am new to MDX queries

    The below query returns error”Query (10, 1) Parser: The syntax for ‘with’ is incorrect.”

    select { [Measures].[Usage Amount]} on 0,
    { Descendants([DIM Date].[Year – Quarter – Month – Day].[Month].&[2015]&[09]:
    [DIM Date].[Year – Quarter – Month – Day].[Month].&[2016]&[08],
    [DIM Date].[Year – Quarter – Month – Day].[Month]) } on 1
    from [TDC0025]
    where { ( [DIM Subscriber].[BETA Nr].&[318813130] , [DIM Subscriber].[SPILT BILLING].&[0]) }

    with member Measures.WeekendProcentAfSamletForbrug
    as sum((({ [DIM Date].[Year – Quarter – Month – Day].[Month].&[2015]&[09]:
    [DIM Date].[Year – Quarter – Month – Day].[Month].&[2016]&[08] }),
    [DIM Date].[Weekend].&[Weekend]),
    ([Measures].[Usage Amount],[DIM Subscriber].[SPILT BILLING].&[0]) ) /
    sum( ({ [DIM Date].[Year – Quarter – Month – Day].[Month].&[2015]&[09]:
    [DIM Date].[Year – Quarter – Month – Day].[Month].&[2016]&[08] }),

    ([Measures].[Usage Amount],[DIM Subscriber].[SPILT BILLING].&[0]) )
    select non empty { [Measures].[WeekendProcentAfSamletForbrug] } on 0
    from [TDC0025]
    where { ([DIM Call Category].[TDC Category Desc].&[Alle], [DIM Subscriber].[BETA Nr].&[318813130]) }

    Please suggest

    • Hi Nils
      You’re trying to run two queries in the same batch, try running one at a time and see if it works.
      Alex

  • Hi
    I am running the below query i am getting error :Server: The operation has been cancelled because there is not enough memory available for the application. If using a 32-bit version of the product, consider upgrading to the 64-bit version or increasing the amount of memory available on the machine.

    Can you help me this how to achive

    select non empty{[Measures].[Stretch Status]} on columns,
    non empty{(
    [DIM EMPLOYEE].[EMP PERMANENT ATC].[EMP PERMANENT ATC].MEMBERS*
    [DIM EMPLOYEE].[EMP PERM GROUP ATC].[EMP PERM GROUP ATC].MEMBERS*
    [DIM EMPLOYEE].[EMP CURRENT ATC].[EMP CURRENT ATC].MEMBERS*
    [DIM EMPLOYEE].[EMP CURR GROUP ATC].[EMP CURR GROUP ATC].MEMBERS*
    [DIM EMPLOYEE].[EMP EMPLOYEE NUMBER].[EMP EMPLOYEE NUMBER].MEMBERS*
    [DIM EMPLOYEE].[EMP PAYROLL ID].[EMP PAYROLL ID].MEMBERS*
    [DIM EMPLOYEE].[EMP FULL NAME].[EMP FULL NAME].MEMBERS*
    [DIM EMPLOYEE].[EMP PRIMARY DESIGNATION].[EMP PRIMARY DESIGNATION].MEMBERS*
    [DIM EMPLOYEE].[EMP TRACK].[EMP TRACK].MEMBERS*
    [DIM EMPLOYEE].[EMP TIER].[EMP TIER].MEMBERS*
    [DIM EMPLOYEE].[EMPLOYEE EMPLOYMENT NATURE].[EMPLOYEE EMPLOYMENT NATURE].MEMBERS*
    [DIM EMPLOYEE].[EMP EMPLOYMENT NATURE GROUP].[EMP EMPLOYMENT NATURE GROUP].MEMBERS*
    [DIM EMPLOYEE].[EMP DOJ].[EMP DOJ].MEMBERS*
    [DIM EMPLOYEE].[EMP GENDER].[EMP GENDER].MEMBERS*
    [DIM EMPLOYEE].[EMP BILLABLE STATUS].[EMP BILLABLE STATUS].MEMBERS*
    [DIM EMPLOYEE].[EMP DEPLOYABLE STATUS].[EMP DEPLOYABLE STATUS].MEMBERS*
    [DIM EMPLOYEE].[EMP CORE CATEGORY].[EMP CORE CATEGORY].MEMBERS*
    [DIM EMPLOYEE].[EMP TARGET BILLABLE PERCENTAGE].[EMP TARGET BILLABLE PERCENTAGE].MEMBERS*
    [DIM EMPLOYEE].[EMP DEFAULT EXPENSE ACCOUNT].[EMP DEFAULT EXPENSE ACCOUNT].MEMBERS*
    [DIM EMPLOYEE].[EMP UNIT].[EMP UNIT].MEMBERS*
    [DIM EMPLOYEE].[EMP HR DEPARTMENT NAME].[EMP HR DEPARTMENT NAME].MEMBERS*
    [DIM EMPLOYEE].[EMP HR DEPARTMENT CODE].[EMP HR DEPARTMENT CODE].MEMBERS*
    [DIM EMPLOYEE].[EMP COA DEPARTMENT NAME].[EMP COA DEPARTMENT NAME].MEMBERS*
    [DIM EMPLOYEE].[EMP COA DEPARTMENT CODE].[EMP COA DEPARTMENT CODE].MEMBERS*
    [DIM EMPLOYEE].[HEADCOUNT].[HEADCOUNT].MEMBERS*
    [DIM EMPLOYEE].[EMP CEG].[EMP CEG].MEMBERS*
    [DIM EMPLOYEE].[EMP PRIMARY PRACTICE].[EMP PRIMARY PRACTICE]*
    [DIM EMPLOYEE].[EMP GROUP PRACTICE NAME].[EMP GROUP PRACTICE NAME].MEMBERS*
    [DIM EMPLOYEE].[EMPLOYEE CORE TYPE].[EMPLOYEE CORE TYPE].MEMBERS*
    [DIM EMPLOYEE].[EMP CORE UNIT].[EMP CORE UNIT].MEMBERS*
    [DIM EMPLOYEE].[EMP SECONDARY PRACTICE].[EMP SECONDARY PRACTICE].MEMBERS*
    [DIM EMPLOYEE].[EMP REPORTING MANAGER NAME].[EMP REPORTING MANAGER NAME].MEMBERS*
    [DIM EMPLOYEE].[EMP DOTTED LINE RM NAME].[EMP DOTTED LINE RM NAME].MEMBERS*
    [DIM EMPLOYEE].[EMP HR PARTNER NAME].[EMP HR PARTNER NAME].MEMBERS*
    [DIM EMPLOYEE].[EMP EMAIL ID].[EMP EMAIL ID].MEMBERS*
    [DIM EMPLOYEE].[EMP CATEGORY].[EMP CATEGORY].MEMBERS*
    [DIM EMPLOYEE].[EMP LEGAL COMPANY].[EMP LEGAL COMPANY].MEMBERS*
    [DIM EMPLOYEE].[EMP COMPANY].[EMP COMPANY].MEMBERS*
    [DIM EMPLOYEE].[EMP NATIONALITY].[EMP NATIONALITY].MEMBERS*
    [DIM EMPLOYEE].[ONF ONSHORE OFFSHORE].[ONF ONSHORE OFFSHORE].MEMBERS*
    [DIM EMPLOYEE].[EMPLOYEE ONLINE DEPLOYMENT MODE].[EMPLOYEE ONLINE DEPLOYMENT MODE].MEMBERS*
    [DIM PROJECT].[ACC NAME].[ACC NAME].MEMBERS*
    [DIM PROJECT].[PROJECT NAME].[PROJECT NAME].MEMBERS*
    [DIM PROJECT].[PRJ CODE].[PRJ CODE].MEMBERS*
    [DIM PROJECT].[DC NAME].[DC NAME].MEMBERS*
    [DIM PROJECT].[SBU GROUPSBU NAME].[SBU GROUPSBU NAME].MEMBERS*
    [DIM PROJECT].[SBU SBU NAME].[SBU SBU NAME].MEMBERS*
    [DIM PROJECT].[SBU SEGMENT NAME].[SBU SEGMENT NAME].MEMBERS*
    [DIM PROJECT].[SBU NEW SBU CODE].[SBU NEW SBU CODE].MEMBERS*
    [DIM STAFFED REQUEST].[PROJECT ROLE TIER].[PROJECT ROLE TIER].MEMBERS*
    [DIM STAFFED REQUEST].[REQ REQUESTED ROLE].[REQ REQUESTED ROLE].MEMBERS*
    [DIM STAFFED REQUEST].[ALLOCATION START DATE].&[2014-01-20T00:00:00],
    [DIM STAFFED REQUEST].[ALLOCATION END DATE].&[2014-01-20T00:00:00]

    )} DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS
    from
    ( SELECT STRTOSET(@FromDate)*
    STRTOSET(@ToDate)*
    STRTOSET(@GroupSBU)*
    STRTOSET(@SBU)*
    STRTOSET(@SBUSegment)*
    STRTOSET(@Account)*
    STRTOSET(@Project)*
    STRTOSET(@NatureGroup)*
    STRTOSET(@PrimaryPractice)*
    STRTOSET(@CurrentATC)*
    STRTOSET(@PermanentATC)*
    STRTOSET(@EmployeeUnit)
    ON COLUMNS
    FROM [HeadCountCube])

    • I’m not surprised that you’re getting an out of memory error, this is a crazy MDX query to try and run. You’re cross joining 52 attributes together.
      Assuming that each only has 10 possible values (which I expect is a huge under-estimate) then you’re asking to the cube to create a dataset containing 10^52 values, which is approximately the same as the number of atoms in the Sun. In your query, it will only try and filter out the non empty values after it’s done this cross joining.

      If you want a data dump like this, you’re better off using T-SQL not MDX. MDX is for aggregated pivot style queries not data dumps.
      There are ways around this in MDX, by only bringing (cross joining) out the key from each dimension, and then returning member properties to get the remaining attribute values, but I would still question the validity of what you’re trying to do.

      Regards
      Alex

  • Hi Alex,

    am having one scenario i dont know how to achieve this scenario .
    in mdx i want like comma separated value.this mdx query i need to use in ssrs report.
    Eg:As per the report am given sample understanding tables results:
    F_ID solution_Type Technique_CODE
    6787933 ORANGE 11
    6787933 RED 22
    6787988 YELLOW 33
    6787988 BLUE 44
    6787988 GREEN 55
    6787990 RED 22
    6787999 BLUE 44
    6787999 ORANGE 11

    O/p i want:

    Client Need o/p like Below: comma separated results based on each F_id

    F_id Solution_Type Teachnique_Code
    6787933 orange,red 11,22
    6787988 yellow,blue,green 33,44,55
    6787990 red 22
    6787999 blue,orange 44,11

    • Hi Yogesh
      I’m not aware of any way to achieve that in MDX I’m afraid. You may be able to do it using T-SQL PIVOT or FOR XMLPATH, or using SSRS functionality.
      Alex

  • Hi,

    Im trying to run the below query but I keep getting hit with the Error (1030753): Memory allocation failed – output grid too large error message. Its when I take the clients down to level 2 that this error occurs, with clients at level 1 it works fine however problem is I only get client names at level 2, at level 1 its just client categories so this is unhelpful. Any assistance would be much appreciated?

    An alternative would be just to load data for specific clients – could anybody advise how I would adapt the query to just load the specific clients im interested in?

    Thanks!

    SELECT {{[Account]}, {[I]}, {[E]} ON COLUMNS,

    NON EMPTY

    Crossjoin({Descendants([Year].[2015],0)},
    Crossjoin({Descendants([Time].[12 Months],1)},
    Crossjoin({Descendants([Every Product].[P&L View],2)},
    Crossjoin({Descendants([Client],2)},
    Crossjoin({Descendants([Markets],2)},
    {Descendants([Organization].[Europe],3)}

    )))))

    ON ROWS

Leave a Reply

Your email address will not be published. Required fields are marked *

HTML tags are not allowed.

344,224 Spambots Blocked by Simple Comments

The Frog Blog

I'm Alex Whittles.

I specialise in designing and implementing SQL Server business intelligence solutions, and this is my blog! Just a collection of thoughts, techniques and ramblings on SQL Server, Cubes, Data Warehouses, MDX, DAX and whatever else comes to mind.

Data Platform MVP

Frog Blog Out
twitter
rssicon