asked
How to remove "All" at month level in crossjoin mdx query
Hello All, I have some confusion on crossjoin function within MDx. I don't want the records with "All" at month level in the result set, Can somebody suggest how to get the result as in screen shot 2 ? WITH MEMBER measure_0 as [Measures].[Volume] SELECT (measure_0) on 0, non empty(crossjoin ([DimDate].[YR].[YR],[DimDate].[Qtr Year].members,[DimDate].[Month Year].members)) ON 1 FROM [SomeCube] Screen Shot 1: ![alt text][1] Screen Shot 2: ![alt text][2] [1]: /storage/temp/4309-sample.jpg [2]: /storage/temp/4310-sample1.png
@manikanta But you do have the "All" member on the month level in the second screenshot. Also, I am not sure why do you need to crossjoin the calendar dimension like this when in reality you probably have the hierarchy, and therefore, it would be easy enough to just use it. That said, if you don't want "All" member then try **[DimDate].[Month Year].Children** instead of **[DimDate].[Month Year].members**
Here is the MDX statement which will exclude the "All" member from the "Month Year": select [Measures].[Volume] on 0, non empty { [DimDate].[YR].[YR] * [DimDate].[Qtr Year].members * [DimDate].[Month Year].Children } on 1 from [SomeCube] The above should include the "All" member for quarters but not for years and months. ***Edit*** It is difficult to understand why there is a need to cross the different members of the date dimension just for the sake of replicating something which is readily available should the date dimension have a hierarchy (which it should). The problem with the cross join is that it finds all the intersections and includes them in the result. Marrying years, quarters and months like this inevitably produces the **year -> month** combinations even though these are naturally available in the hierarchy via **year -> quarter -> month** path, while removing the "All" from the quarters also hides the yearly total of **year -> All -> All** which is desired to be included. I am sure there are other, perhaps better ways to butcher the MDX to produce the result as requested in question, but here is one way of doing it: remove unwanted **year -> (All) -> month** by changing the quarters' members to children and then add the **year -> All -> All** slices ( which were forced to disappear by the previous step) back in via the union of the data with just the **year -> All -> All**. Doing this way also requires ordering the set appearing on rows by year. Here is the script: select Measures.[Volume] on 0, non empty order( union ( { [DimDate].[YR].[YR] * [DimDate].[Qtr Year].Children * [DimDate].[Month Year].Members }, { [DimDate].[YR].[YR] * [DimDate].[Qtr Year].[All] * [DimDate].[Month Year].[All] } ), [DimDate].[YR] ) on 1 from [SomeCube]; Hope this helps. Oleg
@manikanta I am not sure what you mean stating that you are "**getting the same result**" and "**missing in result set total value of year level and quarter level**" at the same time. The result is either the same or it is not. In your original query the "All" member for YR is not included because of [YR].[YR] choice. Thus, it is not included in my query either (I did not change this part). The [Qtr Year] on the other hand should include "All" because "All" is a member of the [Qtr Year] and members is requested. Picking Children for months hides their "All". It would be much easier to just pick the calendar hierarchy. The client tools include all its levels as columns anyway, so I am not sure about the reason to cross join something which is already a built-in hierarchy. Sorry I could not help.
18 People are following this question.
