Page Comparison

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

...

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.

For example, the calculated measure [Measures].[Sales monthly growth] with the  formula [Measures].[Store Sales] - ([Measures].[Store Sales], [Time].CurrentMember.PrevMember) will calculate the [Measures].[Store Sales] growth comparing to the previous Time dimension members period. If this calculated measure is combined in a report with a month in the Time dimension it will show growth comparing to the previous month, if it is combined with a year then it will show growth comparing to the previous year.

There are several functions that help you "move in time":

• [Time].CurrentMember.PrevMember returns the previous member 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 of 2 (use any number for argument, Lag(1) is the same as PrevMember)
• [Time].CurrentMember.Lead(2) will return the next member with a distance 2 (Lead(1) is the same as NextMember)
• ParallelPeriod([Time].[Year], 1, [Time].CurrentMember) will return a "parallel" Time member one year ago (e.g. for a day level member Jan 01 2012 it will be Jan 01 2011 but for a month level member Jan 2012 it will be Jan 2011)
• OpeningPeriod([Time].[Day], [Time].CurrentMember) will return the first descendant of the current Time member at the Day level (first day of the year, quarter or 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 (last day of the year, quarter or month which is defined in the Time dimension). Sometimes you would like to test if your time navigation expression is working as you expect (before using it in further calculations), use the .Name member property to get the member name as the calculation formula result. For example, define a new calculated measure [Measures].[test opening day] with the formula OpeningPeriod([Time].[Day], [Time].CurrentMember). Name and use it in a report together with the Time dimension members and see if you get the expected result for the Time dimension members at different levels.

Sets

...

Sets

Now you know how to navigate to individual dimension members. But quite often you would like to perform operations on a set of dimension members. For example, you would like to get a sum or an average value of some measure over a selected set of dimension members.

The easiest way how to use sets in calculation formulas is to use a list of members enclosed in curly braces, e.g.

{[Customers].[USA].[CA], [Customers].[USA].[OR], [Customers].[USA].[WA]}

...

 {[Time].[2011], [Time].[2012]}

If you would like to select a set as a range of sequential dimension level members then you can specify the first and the last member and use "use :" between  between them. For example, this will create a set of dates from from Jan 01 2012 to  to Jan 15 2012 then formula would look like :

[Time].[2012].[Q1 2012].[Jan 2012].[Jan 01 2012]:[Time].[2012].[Q1 2012].[Jan 2012].[Jan 15 2012]

Quite often you don't don’t want to specify the exact range of members but would like to get all the dimension hierarchy level members. You can do it with the Members method, for example, to with Members method. This will get all months in Time dimension create a calculated member with the formula - the Time dimension:

[Time].[Month].Members

When you want to test which members will be returned by some set expression then you can use the SetToStr functions  function to create a string of concatenated member full names. For example, define the a calculated measure measure [Measures].[test all months] with formula the  with a formula:

SetToStr([Time].[Month].Members)

There are several other useful functions for working with sets, here . Here are some examples how to use them:

• [Customers].[USA].[CA].Children | returns  returns a set of child children members using the dimensional a dimension hierarchy (in this example all cities in California)
• Descendants([Customers].[USA], [Customers].[City]) | returns a  returns set of member descendants at a the specified hierarchy level (in this example all cities in USA)
• [Customers].[USA].[CA].[San Francisco].Siblings | returns  returns all members which have the same parent as this member (in this example all cities in California), it is the same as using using [Customers].[USA].[CA].[San Francisco].Parent.Children .

There are additional methods that you can use to get just the first or the last

...

member of these sets

...

– FirstChild, LastChild, FirstSibling, LastSibling.

Sets of time periods

There are additional useful set selection methods for Time periods

...