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
...
Sometimes it is useful to get the Time dimension member which corresponds to the actual current date. This can be done with eazyBI specific dimension hierarchy level property CurrentDateMember. For example, these expressions will return the members for the month of the current date and the week of current date:
Default values
Sometimes you might want to return some default value if a measure or function returns an empty value. In these cases you can use the CoalesceEmpty(expression, default_value) function, for example - CoalesceEmpty([Measures].[Store Sales], 0)
Comments
...
| Code Block |
|---|
[Time].[Month].CurrentDateMember[Time.Weekly].[Week].CurrentDateMember |
If there is no Time dimension level member that corresponds to the current date (e.g. if data for current month or week are not yet imported) then CurrentDateMember will return the last period before the current date which is present in the Time dimension corresponding level.
There is also an additional eazyBI specific dimension hierarchy level method DateMember for which you can provide a dynamic date expression argument and get the corresponding member (or the last period before that date which is present in the Time dimension). For example:
| Code Block |
|---|
[Time].[Day].DateMember('7 days ago') |
If you want to select a subset of Time dimension level members between specified dates then you can use DateBetween function to filter level members using a date rangeexpressions. For example, the following expression will return set of last 7 days from [Time].[Day] level members:
| Code Block |
|---|
Filter( [Time].[Day].Members,
DateBetween([Time].CurrentMember.StartDate,'7 days ago','today')
) |
Default values
Sometimes you want to return a default value if some measure or function will return an empty value. In these cases you can use CoalesceEmpty(expression, default_value) function, for example:
| Code Block |
|---|
CoalesceEmpty([Measures].[Store Sales], 0) |
Comments
It is possible to write comments in calculation formulas. Use comments either to describe some non-obvious complex calculations . Commenting or also commenting is also valuable during debugging of the writing and testing of calculation formulas - when . When something is not working as expected then comment all of the formula lines and leave uncommented just some fragments part of the formula that you would like to debug.
| Code Block |
|---|
-- one line comment |
...
expression -- comment until end of line |
...
/* multi line |
...
comment */ |
Special comments with annotations
...