CODEWITHSUNDEEP
summdxdimensions
user2674068
user2674068

Reputation: 53

MDX Sum all values from one dimension hierarchy

I´m new to MDX. I work with the Team System Cube from the Team Foundation Server in the Visual Studio Business Intelligence environment. My questions sounds very easy but I don´t know a solution.

1) I have a field which has a DateTime datatype. One dimension of this field (Hierarchy By Week) I use it to show my calendar weeks in my report. Now I have a second field which has an integer datatype (it´s a measure). I created some datasets and so it looks at the moment:

I understand the allocation of the values in the second field and the depending of the values on the dimension. My requirement, it sounds quite simple: I need the SUM of all field values so that it will look like the following table:

If I try to sum up there are no changes.

IIF(ISEMPTY([Measures].[RemainingWorkProductBacklogItem]) OR NOT Mid([Work Item].
[PlannedWeek__HierarchyByWeek].CurrentMember.UniqueName,58,10)
<= Format(Now(), "yyyy-MM-dd"), SUM( [Measures].[RemainingWorkProductBacklogItem]),NULL)

(The Mid function shows only the actual values, it shouldn´t play a role here.)

I also tried the YTD function combined with the sum function too but I didn´t succeed, still no changes (it shows null). Are there any functions which I can use or must I make changes in the cube?

2) A further question in general: If I have two fields (both DateTime) with two different dimensions, it is possible to arrange a mapping? E.G.

Dimension1:

Dimension2:

Both dimensions contain the week2 and 3. The report oriented on Dimension1, it is possible to show the data from Dimension2 too? (I´m sure that must be possible, I searched in the cube options but didn´t find it).

Thank you very much in anticipation

Eugen

Upvotes: 2

Views: 1760

Answers (1)

FrankPl
FrankPl

Reputation: 13315

1) Ytd only works correctly if you label your time dimension (it must be the whole dimension, not just some attributes of a dimension as time, and give the correct type to all attributes of this dimension. The type is configured for the dimension as well as the attributes with the Type property in BIDS. And then it only works for the first such dimension of your cube. Then you would use something like

Sum(Ytd([Dim Time].[PlannedWeek__HierarchyByWeek].CurrentMember))

or 

Sum(Ytd([Dim Time].[PlannedWeek__HierarchyByWeek].CurrentMember), [Measures].[RemainingWorkProductBacklogItem])

2) You definitely should have one time dimension with attributes like year, quarter, month, week, day, weekday, etc. (you do not need all of these, but some) in your cube. Do not mix it with other attributes. Then the reference would just be there without any further effort. I see no problem linking several fact tables to this dimension. And you would be able to apply the Ytd function to this as well, as you only have one time dimension.

MDX can get really hard if you have a wrong cube design, much harder than SQL with a wrong table design.

Upvotes: 2

Related Questions