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.
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…
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
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:
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
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
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
Please see reply in your comment on the other post
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
thank you!
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
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!
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
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
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
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 🙂
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.
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])}
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;
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]