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
...
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
...
Code Block |
---|
YTD(ParallelPeriod([Time].[Year], 1, [Time].CurrentMember)) |
And if you like shorter expression then you can omit the current member argument here as well – YTD(ParallelPeriod([Time].[Year], 1))
. Most of the time related functions will use [Time].CurrentMember
as a default member argument.
If you want to get a number of last periods from the Time dimension then you can use the LastPeriods
function. For example:
Code Block |
---|
LastPeriods(3, [Time].CurrentMember) |
will return a set with the current Time member and the two previous members. If you would like to get the current and the next two Time periods then use negative value -3
instead of 3
. And you can omit the last argument for this function as well and use LastPeriods(3)
.
When you want to get the current Time dimension member in the Weekly hierarchy then you need to use [Time.Weekly].CurrentMember
member expression. Sometimes you need to write a formula which should use either [Time].CurrentMember
(when creating e.g. monthly report) or [Time.Weekly].CurrentMember
(when creating weekly report). In this case you can use
Code Block |
---|
[Time].CurrentHierarchyMember |
Set operations
There are several operations that you can perform on sets:
{set1, set2, ..., setn}
returns returns a union of two or more setsExcept(set1, set2)
returns returns set1 members members but removes any member members that is in are in set2Head(set, number)
returns returns a set with the number of members specified by the given number from first number of members from the original set (if the given if number is is not specified then a set from the first set member from the set is returned)Tail(set, number)
returns returns the last last number of of members from a setset.Item(position)
returns returns one member from a set with a specified the specified position (starting from zero). So if you would like to get the first member of a set you can use the expression expressionHead(set).Item(0)
.
Quite frequently you would like to filter
...
set members using some condition. You can do this
...
with Filter(set, condition)
. For example, in this way you can filter all cities with sales larger than
...
1000
...
:
Code Block |
---|
Filter([Customers].[City].Members, [Measures].[Store Sales] > 1000) |
...
Within the condition expression [Customers].CurrentMember
...
references the current set member for which the condition is evaluated. For example, this will return all cities
...
which name starts
...
with San (
...
using MATCHES operator with regular expression)
...
Code Block |
---|
Filter([Customers].[City].Members, [Customers].CurrentMember.Name MATCHES 'San .*') |
...
...
Other typical function that is used in conditions
...
is IsEmpty
...
. This expression will return all cities which have non-empty sales
...
amount:
Code Block |
---|
Filter( [Customers].[City].Members, NOT IsEmpty([Measures].[Store Sales]) ) |
...
Previously
...
a simple set to string
...
function SetToStr
...
was mentioned
...
that is useful for expression testing purposes. But if you would like to format
...
set results in a
...
customized way then you can
...
use Generate(set, string_expression, separator_string)
. For example, the following expression will return city names concatenated using
...
comma where there is no sales amount recorded
...
:
Code Block |
---|
Generate( Filter( [Customers].[City].Members, IsEmpty([Measures].[Store Sales]) ), [Customers].CurrentMember.Name, ', ' ) |
Aggregates
Now that you know how to select different sets of dimension members you can calculate different aggregated values from these sets:
Sum(set, numeric_expression)
calculates calculates numeric_expression for each for each set member member and returns a sum of all these results. For example,Sum(LastPeriods(3)
., [Measures].[Store Sales])
will will calculate the total sales for the last three three Time periods periods starting from current the current Time dimension dimension member.Count(set)
returns returns a count of set members.Count(set, ExcludeEmpty)
will will return a count of set members for which corresponding measure values are not empty.Avg(set, numeric_expression)
calculates calculates the average of of numeric_expression in a in setMax(set, numeric_expression)
returns returns the maximum value of of numeric_expression in a in setMin(set, numeric_expression)
returns returns the minimum value of of numeric_expression in a set
Conditions
...
- in set
Median(set, numeric_expression)
returns the median value of numeric_expression in set
Aggregate members in other dimensions
In the beginning of this tutorial we defined a [Customers].[West coast]
calculated member in the Customers dimension with a formula:
Code Block |
---|
[Customers].[USA].[CA] + [Customers].[USA].[OR] + [Customers].[USA].[WA] |
But it would be better if we could expand or drill into West coast
when using it in reports. To enable that you need to define calculated member using Aggregate(set)
function. When you will combine such calculated member in a report together with other measure then you will get an aggregated result (by default a sum) of this measure over the specified set of members. So if you will define a [Customers].[West coast]
calculated member with a formula
Code Block |
---|
Aggregate({
[Customers].[USA].[CA],
[Customers].[USA].[OR],
[Customers].[USA].[WA]
}) |
then you will get the same result as previously but in addition you will be able to expand or drill into West coast
member and see the detailed results for CA
, OR
and WA
.
In similar way you can aggregate more complex set expressions. For example, you could define [Customers].[US without west coast]
calculated member with a formula:
Code Block |
---|
Aggregate( Except(
[Customers].[USA].Members,
{ [Customers].[USA].[CA],
[Customers].[USA].[OR],
[Customers].[USA].[WA] }
)
) |
which will calculate aggregate of all US states without west coast states and you can drill into these states as well.
Conditions
You can write formulas with conditions using IIF
and CASE
functions. For simple IF / THEN / ELSE type of conditions use IIF(condition, if_true_expression, if_false_expression)
(notice that there are two I
letters in IIF
), for example
Code Block |
---|
IIF([Measures].[Profit] > 0, 'Profit', 'Loss') |
If there are many conditions then it is easier to use CASE
function, for example
Code Block |
---|
CASE WHEN [Measures].[Profit] > 1000 THEN 'Big profit' WHEN [Measures].[Profit] > 0 THEN 'Small profit' ELSE 'Loss' END |
If all conditions are comparisons of the same expression to different expected values then another CASE
form can be used, for example
Code Block |
---|
CASE Time.CurrentMember.Level.Name WHEN 'Month' THEN Sum(LastPeriods(3), [Measures].[Store Sales]) WHEN 'Day' THEN Sum(LastPeriods(90), [Measures].[Store Sales]) END |
In In IIF
and and CASE
conditions conditions standard comparison operators can be used (=
, <
, <=
, <>
, >
, >=
) as well as as AND
, OR
and and NOT
operators operators as well as several specific operators:
IS
returns whether two objects are the same, for exampleCode Block [Customers].CurrentMember IS [Customers].DefaultMember
(which will be true if Customers current member is default All Customers member)
IN
andNOT IN
returns whether member is in a set, for exampleCode Block [Customers].CurrentMember IN [Customers].[USA].[CA].Children
Member properties
Dimension members has some default properties (like like .Name
and .Key
) as well as they can have additional custom properties. In flex.bi the eazyBI source application import (e.g. from Basecamp, Highrise or JIRA) are also importing additional dimension fields from source systems. MDX has a standard standard Properties
function function to access member properties but flex. bi defines an improved getProperty function (eazyBI defines an additional get
(and also with a longer name getProperty
) function which will return an empty result instead of an exception if no property is defined for the current dimension level).
For example, Highrise JIRA import adds the Created date property at property for all imported dealsissues. The following expression returns the Created date property at property value for Deals dimensions the Issue dimension current member - [Deals:
Code Block |
---|
[Issue].CurrentMember. |
...
get('Created |
...
at') |
Date type conversion
Sometimes you might need to convert a string expression to an integer, a decimal or a date expression value (for example, to convert Highrise custom field string value to corresponding type to be able to use it in further calculations). There are several functions available for data type conversions:
CInt(value)
returns returns a value converted to an integerCDbl(value)
returns returns a value converted to a double floating number type type (should be used when results need to should be a decimal value)- flex.bi eazyBI defines additional function
DateParse(value)
which which will try to convert a value to a date value using different date formats (for example both both2012-01-31
and andJan 31 2012
will will be converted to a the correct date). There is an a MDX standard standardCDate
function function but it supports less date typesdate formats. DateParse
function also supports dynamic date expressions as offset from today (learn more about them in date filters help page). For example, you can useDateParse('today')
orDateParse('30 days ago')
orDateParse('1 week from now')
.
Time difference calculations
flex.bi defines Flex.bi defines an additional function DateDiffDays(from_date, to_date)
which which will return the a difference in days between two dates. It can be used together with the with Now()
function function (which returns the current time) to get a distance in days between a selected time Time dimension member and the current date, for example
...
In addition, flex.bi defines the function DateAddDays(date, number_of_days)
which will return a new date in the past (if number_of_days is negative) or in the future (if number_of_days is positive). For example, this will return a date which is 5 days from the current Time dimension member - [Date].DateAddDays(DateParse([Time].CurrentMember.Name), 5)Sometimes it is useful to get the Time dimension member which corresponds to an actual current date. This can be done with the flex.bi specific dimension hierarchy level property CurrentDateMember. For example, these expressions will return the month of the current date and the week of the current date [Time].[Month].CurrentDateMember
[Time].[Weekly].[Week].CurrentDateMember
If there is no Time dimension level that corresponds to the current date (e.g. if data for current month or week is not yet imported) then the CurrentDateMember function will return the last period before the current date which is present in the corresponding Time dimension level.days is negative) or in the future (if number_of_days is positive). For example, this will return a date which is 5 days from the current Time dimension member
Code Block |
---|
{[Date].DateAddDays(DateParse([Time].CurrentMember.Name), 5)
|
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)
...