If you have not yet introduced yourself to the basic concepts of flex.bi then please start with an overview of flex.bi concepts and learn how to analyse and create reports. This tutorial will explain how to define new calculated members using MDX calculation formulas.
If you have never worked with MDX before, we suggest you watch this video: MDX basic concepts
Dimensions and hierarchies
...
Code Block |
---|
[Measures].[Store Sales] / ([Measures].[Store Sales], [Customers].DefaultMember) |
When in report you will combine [Measures].[Sales / customers total %]
with, for example, [Customers].[USA].[CA]
then you will see percentage of California sales from the total customer sales.
...
Please see also DefaultContext function documentation which can be used to override the context for evaluation of measures.
Moving
...
in time
As you can move to a different dimension hierarchy levels you can also move to a different dimension members in the same dimension. It is typically used in the Time dimension when you want to compare measures between different time periods or aggregate a time period range.
For example, a calculated measure [Measures].[
Store Sales
] it will calculate the value of the Store Sales measure for other corresponding dimension members for each row / column in report. If you would like to override some other dimension value in your calculation formula then you need to use tuples. Tuples are a combination of members from different dimensions. The MDX syntax for tuples is (member_1, member_2, ..., member_n). For example, if you would like to get the Store Sales measure value for all customers then you would use the tuple (monthly growth]
with a formula:
Code Block |
---|
[Measures].[Store Sales] - ([Measures].[Store Sales], [Time].CurrentMember.PrevMember) |
will calculate a [Measures].[Store Sales]
, [Customers].DefaultMember.
Let's define a calculated measure [Measures].[Percent] with the formula (and percentage formatting) [Measures].[Store Sales] / ([Measures].[Store Sales], [Customers].[DefaultMember]).
When you combine [Measures].[Percent] with e.g. [Customers].[USA].[CA] then you will see the percentage of California sales from total customer sales in the report.
Maybe you don't want to see the percentage from total sales, but the percentage from the customer hierarchies direct parent sales (e.g. for the city level show the percentage from corresponding state sales). In this case you can use the CurrentMember dimension or hierarchy property to access to the current member of a corresponding dimension for which the expression is currently evaluated. E.g. [Customers].CurrentMember will return the current member in the Customers hierarchy for which this expression is evaluated. Or in other words if the Customers dimension is placed in rows section then by using [Customers].CurrentMember we know for which row this formula is currently being evaluated.
If we have a current member then we can navigate to other dimension members relative from this member. [Customers].CurrentMember.Parent will return parent member in the Customers hierarchy for the current member. Ancestor([Customers].CurrentMember, [Customers].[Country]) will move from current member up to "ancestor" in the Country level.
So we can define the calculated measures [Measures].[Percent] as [Measures].[Store Sales] / ([Measures].[Store Sales], [Customers].CurrentMember.Parent)
...
growth comparing to the previous Time dimension member period. If this calculated measure will be combined in a report with a month in the Time dimension then it will show a growth comparing to the previous month. If it will be combined with a year then it will show the growth comparing to the previous year.
There are several functions that will help you “to move in time”:
[Time].CurrentMember.PrevMember
returns the previous member, which contains data, in the same hierarchy level (it will return an empty member for the first member)[Time].CurrentMember.NextMember
will return the next member[Time].CurrentMember.Lag(2)
will return the previous member with a distance 2 (use any number for the argument,Lag(1)
is the same asPrevMember
)[Time].CurrentMember.Lead(2)
will return the next member with a distance 2 (Lead(1)
is the same asNextMember
)ParallelPeriod([Time].[Year], 1, [Time].CurrentMember)
will return the “parallel” Time member one year ago (e.g. for the Day level memberJan 01 2012
it will be the dayJan 01 2011
but for the Month level memberJan 2012
it will be the monthJan 2011
)OpeningPeriod([Time].[Day], [Time].CurrentMember)
will return the first descendant of the current Time member at the Day level (the first day of a year, a quarter or a month which is defined in the Time dimension)ClosingPeriod([Time].[Day], [Time].CurrentMember)
will return the last descendant of the current Time member at the Day level (the last day of a year, a quarter or a month which is defined in the Time dimension)
If cube has only one Time dimension then the [Time].CurrentMember
argument can be omitted as it will be used by default. So you can use shorter expressions ParallelPeriod([Time].[Year], 1)
, OpeningPeriod([Time].[Day])
,
.ClosingPeriod([Time].[Day])
Sometimes you would like to test if your time navigation expression is working as you expect (before using it in further calculations). Then you can use .Name
member property to get a member name as a calculation formula result. For example, define calculated measure [Measures].[test opening day]
with a formula:
Code Block |
---|
OpeningPeriod([Time].[Day], [Time].CurrentMember).Name |
and use it in report together with the Time dimension members and see if you get the expected result for the Time dimension members at different levels.
Just like you can move to different dimension hierarchy levels you can also move to different dimension members in the same dimension. It is typically used in the Time dimension when you want to compare measures between different time periods or aggregate time period range.
...