1
votes

For now sometimes I have problems with creating difficult calculated members in SSAS. Is it possible to make case which will SUM certain measure on certain level when user choose another certain level of dimension? For example we have standard time dimension with 4 levels:

Year Month Week Day

Also we have some measure orders which have default function SUM in properties.

Which case do we need to calculate this: sum all orders in week which including current day which we have chosen already.

Also could you recommend me some nook or source for level up my mdx knowledge?

Thanks a lot.

1

1 Answers

1
votes

Yes, it's possible, when you use MDX functions, which are connected with levels.

Here is an example with Year > Quarter > Month > Day hierarchy: SelectUpperLevelMembers_1Initial

Use MDX calculated member to have a SUM of upper level member children (including selected one): SelectUpperLevelMembers_2MDX

The same if you want to create calculation inside the cube:

CREATE MEMBER CURRENTCUBE.[Measures].[SameLevelMembers]
as SUM({[Report Date].[Report Date].CurrentMember.Parent.Children},[Measures].[Count]),
VISIBLE = 1 ;

And a result in cube browser:

SelectUpperLevelMembers_3CubeBrowser

Hundreds of articles you can find there: http://ssas-wiki.com/w/Articles#MDX

I also like this one: http://mdxpert.com because of well-structured info.