Return to: Calculated Member Builder
You can use the keyword Null to create a calculated member whose value is null.
When you create a calculated member, you can specify an integer value for its Solve Order property in the Advanced tab of the Properties pane. This property specifies a priority for solving formulas, zero being the highest priority. The order of solving formulas becomes an issue for cells at intersections where two or more formulas are involved and the result depends on the order in which the formulas are solved. For example, if a formula for Annual Growth is presented in columns and Performance is presented in rows, the cell at the intersections can mean Annual Growth of Performance or Performance of Annual Growth, depending on which formula is solved first.
A set in a function can be given a temporary alias for use within the function by using the keyword As. In this example, the set {State} is given the temporary alias S1 so it can be referred to later in the function in a nested iteration.
Sum({State} As S1, Sum(Geography.CurrentMember.Children,
Population * Val((Geography.CurrentMember.Properties("CityTax")) +
Val(S1.Current.Properties("StateTax")))))
For many expression examples in the following sections, SampleSet is defined to be the following:
{USA, Buffalo, France, NYC, London, California, LA, Nice, UK, Paris}
with sales data as indicated in the following table.
| Location | 1995 sales | 1996 sales |
|---|---|---|
| UK | 1900 | 1700 |
| London | 250 | 300 |
| France | 2500 | 2500 |
| Paris | 365 | 250 |
| Nice | 27 | 100 |
| USA | 5000 | 6500 |
| NYC | 900 | 1100 |
| Buffalo | 300 | 200 |
| California | 2000 | 3500 |
| LA | 500 | 900 |
| Function | Syntax | Description |
|---|---|---|
| SetToArray | SetToArray(«Set»[, «Set»]...[, «Numeric Expression»]) | Converts one or more sets to an array for use in a user-defined function. |
| Function | Syntax | Description |
|---|---|---|
| Dimension | «Hierarchy».Dimension | Returns the dimension that contains a specified hierarchy. |
| Dimension | «Level».Dimension | Returns the dimension that contains a specified level. |
| Dimension | «Member».Dimension | Returns the dimension that contains a specified member. |
| Dimensions | Dimensions(«Numeric Expression») | Returns the dimension whose zero-based position within the cube is specified by a numeric expression. |
| Dimensions | Dimensions(«String Expression») | Returns the dimension whose name is specified by a string. |
| Function | Syntax | Description |
|---|---|---|
| Hierarchy | «Level».Hierarchy | Returns a level's hierarchy. |
| Hierarchy | «Member».Hierarchy | Returns a member's hierarchy. |
| Function | Syntax | Description |
|---|---|---|
| Level | «Member».Level | Returns a member's level. |
| Levels | «Dimension».Levels («Numeric Expression») |
Returns the level whose position in a dimension is specified by a numeric expression. |
| Levels | Levels(«String Expression») | Returns the level whose name is specified by a string expression. |
| Function | Syntax | Description |
|---|---|---|
| IsEmpty | IsEmpty(«Value Expression») | Determines if an expression evaluates to the empty cell value. |
| Function | Syntax | Description |
|---|---|---|
| Ancestor | Ancestor(«Member», «Level») | Returns the ancestor of a member at a specified level. |
| ClosingPeriod | ClosingPeriod([«Level»[, «Member»]]) | Returns the last sibling among the descendants of a member at a level. |
| Cousin | Cousin(«Member1», «Member2») | Returns the member with the same relative position under a member as the member specified. |
| Current Member |
«Dimension».Current Member |
Returns the current member along a dimension during an iteration. |
| Default Member |
«Dimension».Default Member |
Returns the default member of a dimension. |
| FirstChild | «Member».FirstChild | Returns the first child of a member. |
| FirstSibling | «Member».FirstSibling | Returns the first child of the parent of a member. |
| Item | «Tuple».Item(«Numeric Expression») | Returns a member from a tuple. |
| Lag | «Member».Lag(«Numeric Expression») |
Returns a member prior to the specified member along the member's dimension. |
| LastChild | «Member».LastChild | Returns the last child of a member. |
| LastSibling | «Member».LastSibling | Returns the last child of the parent of a member. |
| Lead | «Member».Lead(«Numeric Expression») | Returns a member further along the specified member's dimension. |
| Members | Members(«String Expression») | Returns the member whose name is specified by a string expression. |
| NextMember | «Member».NextMember | Returns the next member in the level that contains a specified member. |
| OpeningPeriod | OpeningPeriod([«Level»[, «Member»]]) | Returns the first sibling among the descendants of a member at a level. |
| ParallelPeriod | ParallelPeriod([«Level»[, «Numeric Expression»[, «Member»]]]) | Returns a member from a prior period in the same relative position as a specified member. |
| Parent | «Member».Parent | Returns the parent of a member. |
| PrevMember | «Member».PrevMember | Returns the previous member in the level that contains a specified member. |
| ValidMeasure | ValidMeasure(«Tuple») | Returns a valid measure in a virtual cube by forcing inapplicable dimensions to their top level. |
| Function | Syntax | Description |
|---|---|---|
| Aggregate | Aggregate(«Set»[, «Numeric Expression»]) | Returns a calculated value using the appropriate aggregate function, based on the context of the query. |
| Avg | Avg(«Set»[, «Numeric Expression»]) | Returns the average value of a numeric expression evaluated over a set. |
| CoalesceEmpty | CoalesceEmpty(«Numeric Expression»[, «Numeric Expression»]...) | Coalesces an empty cell value to a number. |
| Correlation | Correlation(«Set», «Numeric Expression»[, «Numeric Expression»]) | Returns the correlation of two series evaluated over a set. |
| Count | Count(«Set»[, EXCLUDEEMPTY | INCLUDEEMPTY]) | Returns the number of tuples in a set, empty cells included unless the optional EXCLUDEEMPTY flag is used. |
| Covariance | Covariance(«Set», «Numeric Expression»[, «Numeric Expression»]) | Returns the covariance of two series evaluated over a set (biased). |
| CovarianceN | CovarianceN(«Set», «Numeric Expression»[, «Numeric Expression»]) | Returns the covariance of two series evaluated over a set (unbiased). |
| IIf | IIf(«Logical Expression», «Numeric Expression1», «Numeric Expression2») | Returns one of two numeric values determined by a logical test. |
| LinRegIntercept | LinRegIntercept(«Set», «Numeric Expression»[, «Numeric Expression»]) | Calculates the linear regression of a set and returns the value of b in the regression line y = ax + b. |
| LinRegPoint | LinRegPoint(«Numeric Expression», «Set», «Numeric Expression»[, «Numeric Expression»]) | Calculates the linear regression of a set and returns the value of y in the regression line y = ax + b. |
| LinRegR2 | LinRegR2(«Set», «Numeric Expression»[, «Numeric Expression»]) | Calculates the linear regression of a set and returns R2 (the coefficient of determination). |
| LinRegSlope | LinRegSlope(«Set», «Numeric Expression»[, «Numeric Expression»]) | Calculates the linear regression of a set and returns the value of a in the regression line y = ax + b. |
| LinRegVariance | LinRegVariance(«Set», «Numeric Expression»[, «Numeric Expression»]) | Calculates the linear regression of a set and returns the variance associated with the regression line y = ax + b. |
| Max | Max(«Set»[, «Numeric Expression»]) | Returns the maximum value of a numeric expression evaluated over a set. |
| Median | Median(«Set»[, «Numeric Expression»]) | Returns the median value of a numeric expression evaluated over a set. |
| Min | Min(«Set»[, «Numeric Expression»]) | Returns the minimum value of a numeric expression evaluated over a set. |
| Ordinal | «Level».Ordinal | Returns the zero-based ordinal value associated with a level. |
| Rank | Rank(«Tuple», «Set») | Returns the one-based rank of a tuple in a set. |
| Stddev | Stddev(«Set»[, «Numeric Expression»]) | Alias for Stdev. |
| StddevP | StddevP(«Set»[, «Numeric Expression»]) | Alias for StdevP. |
| Stdev | Stdev(«Set»[, «Numeric Expression»]) | Returns the standard deviation of a numeric expression evaluated over a set (unbiased). |
| StdevP | StdevP(«Set»[, «Numeric Expression»]) | Returns the standard deviation of a numeric expression evaluated over a set (biased). |
| Sum | Sum(«Set»[, «Numeric Expression»]) | Returns the sum of a numeric expression evaluated over a set. |
| Value | «Measure».Value | Returns the value of a measure. |
| Var | Var(«Set»[, «Numeric Expression»]) | Returns the variance of a numeric expression evaluated over a set (unbiased). |
| Variance | Variance(«Set»[, «Numeric Expression»]) | Alias for Var. |
| VarianceP | VarianceP(«Set»[, «Numeric Expression»]) | Alias for VarP. |
| VarP | VarP(«Set»[, «Numeric Expression»]) | Returns the variance of a numeric expression evaluated over a set (biased). |
| Function | Syntax | Description |
|---|---|---|
| AddCalculated Members |
AddCalculatedMembers («Set») |
Adds calculated members to a set. |
| BottomCount | BottomCount(«Set», «Count»[, «Numeric Expression»]) | Returns a specified number of items from the bottom of a set, optionally ordering the set first. |
| BottomPercent | BottomPercent(«Set», «Percentage», «Numeric Expression») | Sorts a set and returns the bottom N elements whose cumulative total is at least a specified percentage. |
| BottomSum | BottomSum(«Set», «Value», «Numeric Expression») | Sorts a set and returns the bottom N elements whose cumulative total is at least a specified value. |
| Children | «Member».Children | Returns the children of a member. |
| Crossjoin | Crossjoin(«Set1», «Set2») | Returns the cross product of two sets. |
| Descendants | Descendants(«Member», «Level»[, «Desc_flag»]) | Returns the set of descendants of a member at a specified level, optionally including or excluding descendants in other levels. |
| Distinct | Distinct(«Set») | Eliminates duplicate tuples from a set. |
| DrilldownLevel | DrilldownLevel(«Set»[, «Level»]) or DrilldownLevel(«Set», , «Index») |
Drills down the members of a set, at a specified level, to one level below. Alternatively, drills down on a specified dimension in the set. |
| DrilldownLevelBottom | DrilldownLevelBottom («Set», «Count»[, [«Level»][, «Numeric Expression»]]) |
Drills down the bottom N members of a set, at a specified level, to one level below. |
| DrilldownLevelTop | DrilldownLevelTop («Set», «Count»[, [«Level»][, «Numeric Expression»]]) |
Drills down the top N members of a set, at a specified level, to one level below. |
| Drilldown Member |
DrilldownMember («Set1», «Set2»[, RECURSIVE]) |
Drills down the members in a set that are present in a second specified set. |
| Drilldown MemberBottom |
DrilldownMemberBottom(«Set1», «Set2», «Count»[, [«Numeric Expression»][, RECURSIVE]]) | Like DrilldownMember except that it includes only the bottom N children. |
| Drilldown MemberTop |
DrilldownMemberTop («Set1», «Set2», «Count»[, [«Numeric Expression»][, RECURSIVE]]) |
Like DrilldownMember except that it includes only the top N children. |
| DrillupLevel | DrillupLevel(«Set»[, «Level»]) | Drills up the members of a set that are below a specified level. |
| DrillupMember | DrillupMember(«Set1», «Set2») | Drills up the members in a set that are present in a second specified set. |
| Except | Except(«Set1», «Set2»[, ALL]) | Finds the difference between two sets, optionally retaining duplicates. |
| Extract | Extract(«Set», «Dimension»[, «Dimension»...]) | Returns a set of tuples from extracted dimension elements. The opposite of Crossjoin. |
| Filter | Filter(«Set», «Search Condition») | Returns the set resulting from filtering a set based on a search condition. |
| Generate | Generate(«Set1», «Set2»[, ALL]) | Applies a set to each member of another set and joins the resulting sets by union. |
| Head | Head(«Set»[, « Numeric Expression »]) | Returns the first specified number of elements in a set. |
| Hierarchize | Hierarchize(«Set») | Orders the members of a set in a hierarchy. |
| Intersect | Intersect(«Set1», «Set2»[, ALL]) | Returns the intersection of two input sets, optionally retaining duplicates. |
| LastPeriods | LastPeriods(«Index»[, «Member»]) | Returns a set of members prior to and including a specified member. |
| Members | «Dimension».Members | Returns the set of all members in a dimension. |
| Members | «Hierarchy».Members | Returns the set of all members in a hierarchy. |
| Members | «Level».Members | Returns the set of all members in a level. |
| Mtd | Mtd([«Member»]) | A shortcut function for the PeriodsToDate function that specifies the level to be Month. |
| Order | Order(«Set», {«String Expression» | «Numeric Expression»}[, ASC | DESC | BASC | BDESC]) | Arranges members of a set, optionally preserving or breaking the hierarchy. |
| PeriodsToDate | PeriodsToDate([«Level»[, «Member»]]) | Returns a set of periods (members) from a specified level starting with the first period and ending with a specified member. |
| Qtd | Qtd([«Member»]) | A shortcut function for the PeriodsToDate function that specifies the level to be Quarter. |
| StripCalculatedMembers | StripCalculatedMembers («Set») |
Removes calculated members from a set. |
| StrToSet | StrToSet(«String Expression») | Constructs a set from a string expression. |
| Subset | Subset(«Set», «Start»[, «Count»]) | Returns a subset of elements from a set. |
| Tail | Tail(«Set»[, «Count»]) | Returns a subset from the end of a set. |
| ToggleDrillState | ToggleDrillState(«Set1», «Set2»[, RECURSIVE]) | Toggles the drill state of members. This function is a combination of DrillupMember and DrilldownMember. |
| TopCount | TopCount(«Set», «Count»[, «Numeric Expression»]) | Returns a specified number of items from the top of a set, optionally ordering the set first. |
| TopPercent | TopPercent(«Set», «Percentage», «Numeric Expression») | Sorts a set and returns the top N elements whose cumulative total is at least a specified percentage. |
| TopSum | TopSum(«Set», «Value», «Numeric Expression») | Sorts a set and returns the top N elements whose cumulative total is at least a specified value. |
| Union | Union(«Set1», «Set2»[, ALL]) | Returns the union of two sets, optionally retaining duplicates. |
| VisualTotals | VisualTotals(«Set», «Pattern») | Dynamically totals child members specified in a set using a pattern for the total label in the result set. |
| Wtd | Wtd([«Member»]) | A shortcut function for the PeriodsToDate function that specifies the level to be Week. |
| Ytd | Ytd([«Member»]) | A shortcut function for the PeriodsToDate function that specifies the level to be Year. |
| Function | Syntax | Description |
|---|---|---|
| CoalesceEmpty | CoalesceEmpty(«String Expression»[, «String Expression»]...) | Coalesces an empty cell value to a string. |
| IIf | IIf(«Logical Expression», «String Expression1», «String Expression2») | Returns one of two string values determined by a logical test. |
| Name | «Dimension».Name | Returns the name of a dimension. |
| Name | «Hierarchy».Name | Returns the name of a hierarchy. |
| Name | «Level».Name | Returns the name of a level. |
| Name | «Member».Name | Returns the name of a member. |
| SetToStr | SetToStr(«Set») | Constructs a string from a set. |
| TupleToStr | TupleToStr(«Tuple») | Constructs a string from a tuple. |
| UniqueName | «Dimension».UniqueName | Returns the unique name of a dimension. |
| UniqueName | «Level».UniqueName | Returns the unique name of a level. |
| UniqueName | «Member».UniqueName | Returns the unique name of a member. |
| Function | Syntax | Description |
|---|---|---|
| Current | «Set».Current | Returns the current tuple from a set during an iteration. |
| Item | «Set».Item(«String Expression»[, «String Expression»...] | «Index») | Returns a tuple from a set. |
| StrToTuple | StrToTuple(«String Expression») | Constructs a tuple from a string. |