Querying dimension members in MDX

28 responses to “Querying dimension members in MDX”

1. Jeff Hadden says:

   16 July 2013 at 13:37

   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?

   Reply
   • Alex says:

     16 July 2013 at 14:55

     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

     Reply
     • Jeff Hadden says:

       16 July 2013 at 17:17

       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.

       Reply
       • Alex says:

         16 July 2013 at 18:38

         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]

         Reply
2. Rahul says:

   6 May 2015 at 07:25

   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.

   Reply
3. apoorva nigam says:

   4 February 2016 at 09:58

   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….!!!!

   Reply
4. Allihandra says:

   15 April 2016 at 01:16

   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.

   Reply
5. Anubhav says:

   10 June 2016 at 08:02

   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

   Reply
   • Alex says:

     10 June 2016 at 09:16

     Please see reply in your comment on the other post

     Reply
6. Yoni says:

   22 June 2016 at 13:43

   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

    

   Reply
   • Alex says:

     22 June 2016 at 14:28

     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

     Reply
     • Yoni says:

       22 June 2016 at 14:42

       thank you!

       Reply
7. Nils says:

   22 August 2016 at 14:14

   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

   Reply
   • Alex says:

     22 August 2016 at 14:25

     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

     Reply
8. devendra says:

   16 September 2016 at 07:07

   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])

   Reply
   • Alex says:

     16 September 2016 at 10:42

     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

     Reply
9. yogesh says:

   28 September 2016 at 09:05

   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

   Reply
   • Alex says:

     28 September 2016 at 09:16

     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

     Reply
10. Jack says:

    17 November 2016 at 19:28

    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

    Reply
11. Lena Meer says:

    22 May 2018 at 12:41

    Hi there! I was wondering if you could help me with writing a similar query to what you have described, when I try to do it, I get “#Error” in the columns
    This is the query I`m writing:
    With member Measures.State as [DWH Dim Relationships].[Feature State].[Feature State].CURRENTMEMBER.member_caption

    SELECT {Measures.State} on columns,
    {[DWH Dim Relationships].[Feature Title].[Feature Title]}on rows
    FROM [CUBE_Rel_1_AED_SAFe]

    And I get a column named state and all the values inside it are “#Error”
    Could you please help me?

    Thank you!

    Reply
    • Alex says:

      29 May 2018 at 11:23

      It looks like a simple change to add “.MEMBERS” onto the rows. You’re specifying an attribute not a member or set. So change the rows line to
      {[DWH Dim Relationships].[Feature Title].[Feature Title].MEMBERS} on rows
      And hopefully it should work for you.
      Alex

      Reply
12. JAYRON SOARES says:

    30 December 2018 at 15:45

    Hi Alex,
    Thanks for your attention to making available these great mdx materials.
    I’m stuck trying to figure it out how to create a measure in Pivot Table Excel, bond to SSAS Cube.
    My MDX query:
    COUNT(EXISTING MATERIAL.[MATERIAL CODE].[MATERIAL CODE].MEMBERS).
    Its results are partially accurate however on the “grand total”/”subtotal” value is wrong
    How may I adjust this query in order to get the right result to “grand total and subtotal?
    Thanks in advanced

    Reply
    • Alex says:

      2 January 2019 at 10:17

      Hi Jayron, To offer any advice I’d need further details of what you’re expecting to see, and what you’re actually seeing in the totals

      Reply
13. Veronika Florova says:

    29 January 2019 at 14:19

    Hi, I am trying filter by date (in fact there are two date columns), so I have 4 selectors – two for the years and 2 for the months. The problem is that I don’t know how to put the four parameters in the “where” clause – it works fine with 2, but when I add the third I get an error , which says that the parameter is not in right date format , but I am sure that the problem is that I don’t write the query properly. Could you, please, show me how should the where clause looks with four parameters? Thanks in advance 🙂

    Reply
14. Anubhav says:

    12 February 2019 at 12:36

    Hi Alex

    Is there a way to use “OR” condition in MDX like

    (Serialnumber = 111 OR user_Serialnumber= 111 OR detected_Serialnumber= 111)

    Thanks a lot for your help.

    Reply
    • Alex says:

      12 February 2019 at 12:43

      Yes there are a few ways, the most efficient is to create a set of three tuples, each tuple filtering for one of the attributes. Something like this:

      {([MyDimension].[Serialnumber].[111], [MyDimension].[user_Serialnumber].[All], [MyDimension].[detected_Serialnumber].[All])
      ,([MyDimension].[Serialnumber].[All], [MyDimension].[user_Serialnumber].[111], [MyDimension].[detected_Serialnumber].[All])
      ,([MyDimension].[Serialnumber].[All], [MyDimension].[user_Serialnumber].[All], [MyDimension].[detected_Serialnumber].[111])}

      Reply
15. Sai says:

    11 October 2021 at 23:20

    Trying to group by on a dimension to get Id, count(*).. How would I achieve it?

    sql equivalent:
    select id,count(1) from emp group by id;

    Reply
    • Alex Whittles says:

      4 January 2022 at 09:07

      To get the count(*) you need to create a count measure in the cube. There is no concept of count(*) within the query.
      Grouping is entirely different in MDX than SQL, to group you just specify the grouped member on rows.
      e.g. SELECT [Measures].[MyCountMeasure] ON Columns, [emp].[id].members on rows from [MyCube]

      Reply