Versions Compared

Version Old Version 4 New Version 5
Changes made by

Juris Aidma (Deactivated)

Dace Kvalberga

Saved on

Key

  • This line was added.
  • This line was removed.
  • Formatting was changed.
Style
#com-atlassian-confluence .wiki-content td {
border: none;
}
Align
alignjustified

If you have not yet read them then please start with overview of Standard-BI concepts and analyze and create reports tutorial. This tutorial will explain how to define new calculated members using MDX calculation Ja vēl neesat iepazinušies ar Standard-BI vispārējo pārskatu un datu analīzes un jaunu atskaišu izveidošanas funkcionalitātes aprakstiem, lūdzu iepazīstieties ar tiem. Šī instrukcija apraksta kā definēt jaunus aprēkināmos rādītājus izmantojot MDX kalkulāciju formulas.

Each cube dimension contains one or more hierarchies with one or more hierarchy levels. And each hierarchy level contains dimension members. There are normal members which are imported from source files or applications as well as you can define calculated members using calculation formulas. Calculation formulas are defined using MDX query language.

There is special dimension Measures which contains all imported measures. Most frequently calculated members are defined in Measures dimension and then they are typically called as calculated measures.

...

Align
alignjustified
If you would like to define new calculated member then expand corresponding dimension (e.g. Measures) and expand Calculated members section:

 

 

 

 

 

 

Align
alignjustified
If you would like to define new calculated member then click on Define new link, if you would like to edit (or delete) existing calculated member then click on edit link. If you do not have rights to edit calculated members in current account then you will see just show link to see definition of existing calculated members. When you define new or edit existing calculated member you will see the following calculated member definition form:

 

 

 

 

 

 

Align
alignjustified
Each calculated member should have name (unique within dimension) and calculation formula (the rest of tutorial will teach how to write calculation formulas). In addition you can specify how calculated member value should be formatted (e.g. as integer, decimal, date or using default formatting). From the right sidebar you can quickly select other members, dimensions, operators or functions to insert them in calculation formula.
After defining calculated member formula press Update to save it or Delete to delete existing calculated members or Cancel to discard any changes. If calculated member formula will be invalid then corresponding error message will be displayed.
Do not modify or delete pre-defined calculated members that were created by source application import - their original definition will be recreated next time you will perform source application import. If you need to create modified version of existing pre-defined calculated member then copy its calculation formula and create new calculated member with different name and modify copied calculation formula for the new calculated member.
Let's start writing some calculation formulas! Following examples will use Sales demo cube dimensions and measures to illustrate creation of calculation formulas.

 

 

 

...

Moving across dimension levels

Align
alignjustified

When you use Measures.Store Sales then it will calculate Store Sales measure value for other corresponding dimension members for each row / column in report. If in your calculation formula you would like to override some other dimension value then you need to use tuples which is combination of members from different dimensions. MDX syntax for tuples is (member_1, member_2, ..., member_n). For example, if you would like to get Store Sales measure value for all customers then you would use tuple (Measures.Store Sales, Customers.DefaultMember.
Let's define calculated measure Measures.[] with formula (and percentage formatting)Measures.Store Sales / (Measures.Store Sales, Customers.DefaultMember)
When in report you will combine Measures.[] with e.g. Customers.USA.CA then you will see percentage of California sales from total customer sales.
Maybe you don't want to see percentage from total sales but percentage from customer hierarchy direct parent sales (e.g. for city level show percentage from corresponding state sales). In this case you can use CurrentMember dimension or hierarchy property to access current member of corresponding dimension for which expression currently is evaluated. E.g. Customers.CurrentMember will return current member in Customers hierarchy for which this expression is evaluated. Or in other words if Customers dimension is placed on report rows then by using Customers.CurrentMember we know for which row this formula is currently evaluated.
If we have current member then we can navigate to other dimension members relative from this member. Customers.CurrentMember.Parent will return parent member in Customers hierarchy for current member. Ancestor(Customers.CurrentMember, Customers.Country) will move from current member up to "ancestor" in Country level.
So we can define calculated measure Measures.[] asMeasures.Store Sales / (Measures.Store Sales, Customers.CurrentMember.Parent)

 

Moving in time

Align
alignjustified

As 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 Time dimension when you want to compare measures between different time periods or aggregate time period range.
For example, calculated measure Measures.Sales monthly growth with formulaMeasures.Store Sales - (Measures.Store Sales, Time.CurrentMember.PrevMember)
will calculate Measures.Store Sales growth comparing to previous Time dimension member period. If this calculated measure will be combined in report with month in Time dimension it will show growth comparing to previous month, if it will be combined with year then it will show growth comparing to previous year.
There are several functions that help you "move in time":

  • Time.CurrentMember.PrevMember returns previous member in the same hierarchy level (it will return empty member for the first member)
  • Time.CurrentMember.NextMember will return next member
  • Time.CurrentMember.Lag(2) will return previous member with distance 2 (use any number for argument, Lag(1) is the same as PrevMember)
  • Time.CurrentMember.Lead(2) will return next member with distance 2 (Lead(1) is the same as NextMember)
  • ParallelPeriod(Time.Year, 1, Time.CurrentMember) will return "parallel" Time member one year ago (e.g. for day level member Jan 01 2012 it will be day Jan 01 2011 but for month level member Jan 2012 it will be month Jan 2011)
  • OpeningPeriod(Time.Day, Time.CurrentMember) will return first descendant of current Time member at Day level (first day of year, quarter or month which is defined in Time dimension)
  • ClosingPeriod(Time.Day, Time.CurrentMember) will return last descendant of current Time member at Day level (last day of year, quarter or month which is defined in Time dimension)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 member name as calculation formula result. For example, define calculated measure Measures.test opening day with formulaOpeningPeriod(Time.Day, Time.CurrentMember).Name
    and use it in report together with Time dimension members and see if you get expected result for Time dimension members at different levels. \\\\\\\\

 

...