Active Records This function will calculate the number of active records (or any other number) for a given date, based on a start and end date. If the selected date is between the start and end for the record, it will contribute to the total. A requirement for this is a start and end date on the fact table. There must be three date dimensions on the cube, a Start, End and Date. The start and End Date dimensions must be linked to the relevant fact columns, while the third date dimension must be a Shell Dimension (or at least not linked to this fact table). This function only gives the correct result when the filter is set on the date level, not month or other aggregated levels. From Date Dimension_Level The date (key) level of the From date dimension 0 To Date Dimension_Level The date (key) level of the To date dimension 1 Select Date Dimension_Level The date (key) level of the Select/Filter date dimension 2 Select Dimension Dimension 3 Value Measure 4 Time Based Aggregate( ( NULL:LinkMember(Select Date.CurrentMember,From Date), LinkMember(Select Date.CurrentMember,To Date):NULL, Root(Select Dimension) ), Value ) All Periods Sums a given measure over all time. Used with the Closing business function Time Hierachy Hirachy 0 Amount Measure 1 Opening and Closing Balances sum(PeriodsToDate(Time Hierachy.[(All)]),Amount) Average Inventory Calculates the average inventory for two parallel time dimension members. The default usage is year by year comparison, but works on any level specified by the hierarchy level parameter Measure Measure 0 Time Dimension Hierarchy Hirachy 1 Hierarchy Level Hierachy_Level The level such as year, month or quarter 2 Distance Numeric_Value The distance for the parallel period 3 Inventory ( (Measure, Time Dimension Hierarchy.CurrentMember) + (Measure, ParallelPeriod(Hierarchy Level, Distance, Time Dimension Hierarchy.CurrentMember)) ) / Iif( IsEmpty(ParallelPeriod(Hierarchy Level, Distance, Time Dimension Hierarchy.CurrentMember)), 1, 2 ) Average Inventory Days Days in period Numeric_Value Number of days such as 365 for a year 0 Inventory Turns Measure 1 Inventory Iif( IsEmpty(Inventory Turns), NULL, Days in period / Inventory Turns ) Average of children This calculation will for any member in the specified hierarchy calculate the average of the children of that member for the specified measure (Value). The leaf level will be blank. Value Measure 0 Hierarchy Hirachy 1 Dimension AVG(Hierarchy.CurrentMember.Children, Value) Average of Children with leaf This calculation will for any member in the specified hierarchy calculate the average of the children of that member for the specified measure (Value). The leaf level will show the actual value for this member. Value Measure 0 Hierarchy Hirachy 1 Dimension iif(isleaf(Hierarchy.CurrentMember),Value, AVG(Hierarchy.CurrentMember.Children, Value)) Closing Calculates the closing balance for a measure. Use the All Periods business function for the total parameter. Total Measure An All Periods BF Measure 0 Time Hirachy 1 Opening and Closing Balances (Total, CLOSINGPERIOD(Time.currentmember.level, Time.currentmember)) Costs Of Goods Sold Calculates the costs of goods sold for use in inventory turnover calculation. Time Dimension Hierarchy Level Hierachy_Level 0 Days Numeric_Value Number of days i.e. 360 1 Measure Measure The measure that contains the cost value of goods sold 2 Inventory Sum(LastPeriods(Days, ClosingPeriod(Time Dimension Hierarchy Level)), Measure) Current and Previous Date Periods This Business Function will create three new members that can be used as filters in a time dimension. Current Month Current Quarter Current Year The Calendar Hierarchy must be a YQMD hierarchy. The name column of the date dimension level must be set to a field with the following transformation on the date table on the data warehouse: convert(varchar, [DateValue], 112) Date Level Hierachy_Level 0 CalendarHierarchy Hirachy 1 Utility Dimensions Null, Visible=0 ; CREATE HIDDEN TodayDate = Format(Now(), "yyyyMMdd"); CREATE SET [CalendarToday] AS Filter(Date Level.Members, CalendarHierarchy.MemberValue = (CalendarHierarchy.[All], TodayDate)); CREATE MEMBER CURRENTCUBE.CalendarHierarchy.[All].[Current Month] AS [CalendarToday].Item(0).Parent; CREATE MEMBER CURRENTCUBE.CalendarHierarchy.[All].[Current Quarter] AS [CalendarToday].Item(0).Parent.Parent; CREATE MEMBER CURRENTCUBE.CalendarHierarchy.[All].[Current Year] AS [CalendarToday].Item(0).Parent.Parent.Parent; CREATE MEMBER CURRENTCUBE.CalendarHierarchy.[All].[Previous Month] AS [CalendarToday].Item(0).Parent.LAG(1); CREATE MEMBER CURRENTCUBE.CalendarHierarchy.[All].[Previous Quarter] AS [CalendarToday].Item(0).Parent.Parent.LAG(1); CREATE MEMBER CURRENTCUBE.CalendarHierarchy.[All].[Previous Year] AS [CalendarToday].Item(0).Parent.Parent.Parent.LAG(1); CREATE MEMBER CURRENTCUBE.[Measures].[DummyTX] AS Null Current Date Periods This Business Function will create three new members that can be used as filters in a time dimension. Current Month Current Quarter Current Year The Calendar Hierarchy must be a YQMD hierarchy. The name column of the date dimension level must be set to a field with the following transformation on the date table on the data warehouse: convert(varchar, [DateValue], 112) Date Level Hierachy_Level 0 CalendarHierarchy Hirachy 1 Utility Dimensions Null, Visible=0 ; CREATE HIDDEN TodayDate = Format(Now(), "yyyyMMdd"); CREATE SET [CalendarToday] AS Filter(Date Level.Members, CalendarHierarchy.MemberValue = (CalendarHierarchy.[All], TodayDate)); CREATE MEMBER CURRENTCUBE.CalendarHierarchy.[All].[Current Month] AS [CalendarToday].Item(0).Parent; CREATE MEMBER CURRENTCUBE.CalendarHierarchy.[All].[Current Quarter] AS [CalendarToday].Item(0).Parent.Parent; CREATE MEMBER CURRENTCUBE.CalendarHierarchy.[All].[Current Year] AS [CalendarToday].Item(0).Parent.Parent.Parent; CREATE MEMBER CURRENTCUBE.[Measures].[DummyTX] AS Null Division Divedes a number with another, and checks for zerodivisions, if the divisor is zero, the function returns NULL. Divisor Measure 0 Dividend Measure 1 Calculations iif(Divisor, Dividend/Divisor, NULL) Earlier Period Returns the Value for an earlier period. For instance, if the Year Level is selected and the distance is set to one, it will give a Previous Year measure. This function will not roll over parent periods, so if month level is selected, january will not roll to december of the previous year. Level Dimension_Level The time hierachy level that decides what level to travel by 0 Distance Numeric_Value The distance to travel, positive values goes back in time, negative ahead. 1 Value Measure 2 Time Based (Level.CurrentMember.Lag(Distance), Value) Inventory Turns Cost of Goods Sold Measure 0 Average Inventory for the Period Measure 1 Inventory Cost of Goods Sold / Average Inventory for the Period Measure in Reporting Currency Described in a seperate document! ReportingCurrencyDimension Dimension The Dimension that contains the Reporting Currencies 0 TimeDimensionHierarchy Hirachy The Time Dimension Hierarchy that will be used to do the calculation 1 CompanyDimension Dimension_Level The Dimension that contains the companies and their default currency 2 BaseMeasure Measure The Measure in default currency that will be the input for the calculation 3 ExchRateMeasure Measure The Measure that contains the exchangerate (often a hidden measure) 4 Currency Iif ( IsLeaf(ReportingCurrencyDimension.CurrentMember), Iif ( IsLeaf(TimeDimensionHierarchy.CurrentMember) And IsLeaf(CompanyDimension.CurrentMember), (BaseMeasure/ExchRateMeasure)*100, Sum( CrossJoin( {Descendants(TimeDimensionHierarchy.CurrentMember,,Leaves)}, {Descendants(CompanyDimension.CurrentMember,,Leaves)} ), (BaseMeasure/ExchRateMeasure)*100 ) ), Null ), NON_EMPTY_BEHAVIOR = { BaseMeasure } Opening Calculates the opening balance for a period. This is used with a Closing Business Function measure. Closing Measure 0 Time Hirachy 1 Opening and Closing Balances (Closing, Time.currentmember.LAG(1)) ParallelPeriod ParallelPeriod function takes the ancestor of the specified member at the specified level, finds the ancestor's sibling with the specified lag, and finally returns the parallel period of the specified member among the descendants of the sibling. Time Hierarchy Hirachy 0 Hierarchy Level Hierachy_Level 1 Distance Numeric_Value 2 Measure Measure 3 Time Based (ParallelPeriod(Hierarchy Level, Distance, Time Hierarchy.CurrentMember), Measure) Percent for Columns Displays the percentage of the selected measure, as a part of the column total instead of grand total Remember to set the Format string to 'Percent'! Number Measure Amount or quantity 0 Percent Case when isempty(Number) then 0 else iif(isempty(Axis(1).Item(0).Item(0).Dimension.CurrentMember.Parent),1,Number/(Axis(1).Item(0).Item(0).Dimension.CurrentMember.Parent,Number)) End Percent for Rows Displays a percentage of the row total instead of the grand total Remember to set the Format string to 'Percent'! Number Measure The measure the use for the calculation 0 Percent Case when isempty(Number) then 0 else iif(isempty(Axis(0).Item(0).Item(0).Dimension.CurrentMember.Parent),1,Number/(Axis(0).Item(0).Item(0).Dimension.CurrentMember.Parent,Number)) End Percent of total How many percent does the current member of a dimension contriute towards the total for the dimension. Example: In a Color dimension, how many percent does the color Red contribute with to the total. Note: Use % in Format String to get the result as a percentage. Measure Measure 0 DimensionHierarchy Dimension_Level 1 Percent (Measure)/Sum(DimensionHierarchy.[(All)],(Measure)) Period Growth Calculates the growth for a measure by subtracting the value for the same measure a specified distance from the current member over a time dimension- and hierarchy Sample usage could be to calculate the growth for a measure compared with the same measure 6 months, 1 year or 2 weeks back Time Hierarchy Hirachy 0 Hierarchy Level Hierachy_Level 1 Distance Numeric_Value 2 Measure Measure 3 Time Based (Time Hierarchy.CurrentMember, Measure) - (ParallelPeriod(Hierarchy Level, Distance, Time Hierarchy.CurrentMember), Measure) Periods to date (XTD) Returns the sum of values for all the members in a given period, up until the current member. For instance, if a year level is specified as the level parameter, this will be a Year to Date measure (YTD). If a Month level is selected it will be MTD. Level Hierachy_Level 0 Value Measure 1 Time Based SUM(PeriodsToDate(Level), Value) Relative Dimension Contribution Calculates how much in percentage a member contributes to the total of the parent. For instance: With a [Date - YQMD] Hierachy, how big a percentage of the quarters turnover is made in each month in the quarter etc. Use the format string 'Percent' Measure Measure 0 Dimension Hierarchy Hirachy 1 Dimension IIF((Measure, Dimension Hierarchy.CurrentMember.Parent) = 0, 1, Measure / (Measure, Dimension Hierarchy.CurrentMember.Parent) ) Remainder of Year Date Hierachy Hirachy 0 Measure Measure 1 Time Based SUM({Date Hierachy.CurrentMember:Date Hierachy.currentMember.lastsibling},Measure) Rolling 12 Months Year Level Hierachy_Level 0 Date Hierarchy Hirachy 1 Value Measure 2 Time Based SUM( { ParallelPeriod( Year Level, 1, Date Hierarchy ).NextMember : Date Hierarchy.CurrentMember }, Value ) Running Total Calculates the running total over a time hierachy Time Hierarchy Hirachy 0 Measure Measure 1 Time Based Sum(PERIODSTODATE(Time Hierarchy.[(All)]), Measure) TimeCalculations MDX used for setting up dynamic time calculations. EX.: YTD, Running Total, Previous Year, Difference this year/last year PeriodHierarchy Hirachy Time Hierarchy to be used for the dynamic time calculations 0 PeriodLowestLevel Dimension_Level Lowest level in the time dimension 1 CalcDimName Member Name of the Time Calculation shell dimension 2 AggregationLevel Member Name of the Aggregation level in the Time Calculation dimension 3 PeriodAllYearMember Member Name of the Year all member in the time dimension 4 PeriodAllMonthMember Member Name of the Month all member in the time dimension 5 ComparisonLevel Member Name of the Comparison level in the Time Calculation dimension 6 Utility Dimensions Null, Visible=0 ; /////////////////////////////////////////////////////////////////////////////////////// // // Aggregations // /////////////////////////////////////////////////////////////////////////////////////// /////////////////////////////////////////////////////////////////////////////////////// // // RUNNING TOTAL - ID = 4 In TimeAggregations Table // /////////////////////////////////////////////////////////////////////////////////////// SCOPE ( PeriodHierarchy.MEMBERS, PeriodLowestLevel.MEMBERS ); ( CalcDimName.AggregationLevel.&[4]) = Aggregate ( { CalcDimName.AggregationLevel.DefaultMember } * { NULL : PeriodHierarchy.CurrentMember } ); /////////////////////////////////////////////////////////////////////////////////////// // // YEAR TO DATE - ID = 1 In TimeAggregations Table // /////////////////////////////////////////////////////////////////////////////////////// ( CalcDimName.AggregationLevel.&[1] ) = Aggregate( { CalcDimName.AggregationLevel.DefaultMember } * PeriodsToDate( PeriodHierarchy.[Year], PeriodHierarchy.CurrentMember ) ); /////////////////////////////////////////////////////////////////////////////////////// // // LAST 12 MONTHS (ROLLING 12 MONTHS) - ID = 2 In TimeAggregations Table // /////////////////////////////////////////////////////////////////////////////////////// ( CalcDimName.AggregationLevel.&[2] ) = Aggregate( { CalcDimName.AggregationLevel.DefaultMember } * { ParallelPeriod( PeriodHierarchy.[Year], 1, PeriodHierarchy ).NextMember : PeriodHierarchy.CurrentMember } ); /////////////////////////////////////////////////////////////////////////////////////// // // TOTAL YEAR - ID = 3 In TimeAggregations Table // /////////////////////////////////////////////////////////////////////////////////////// ( CalcDimName.AggregationLevel.&[3] ) = ( CalcDimName.AggregationLevel.DefaultMember, Ancestor( PeriodHierarchy.CurrentMember, PeriodHierarchy.[Year] ) ); /////////////////////////////////////////////////////////////////////////////////////// // // Tuple (All years, All Months) is the default number (keeps compatibility with OWC11) // /////////////////////////////////////////////////////////////////////////////////////// ( PeriodAllYearMember, PeriodAllMonthMember, Except( CalcDimName.AggregationLevel.AggregationLevel.MEMBERS, CalcDimName.AggregationLevel.DefaultMember ) ) = CalcDimName.AggregationLevel.DefaultMember; END SCOPE; /////////////////////////////////////////////////////////////////////////////////////// // // End Aggregations // /////////////////////////////////////////////////////////////////////////////////////// /////////////////////////////////////////////////////////////////////////////////////// // // Comparison // /////////////////////////////////////////////////////////////////////////////////////// /////////////////////////////////////////////////////////////////////////////////////// // // PREVIOUS YEAR - ID = 1 In TimeComparison Table // /////////////////////////////////////////////////////////////////////////////////////// SCOPE (PeriodHierarchy.MEMBERS, PeriodLowestLevel.MEMBERS); ( CalcDimName.ComparisonLevel.&[1], CalcDimName.AggregationLevel.Members ) = ( CalcDimName.ComparisonLevel.DefaultMember, ParallelPeriod( PeriodHierarchy.[Year], 1, PeriodHierarchy.CurrentMember) ); END SCOPE; /////////////////////////////////////////////////////////////////////////////////////// // // DIFF. OVER PREVIOUS YEAR - ID = 2 In TimeComparison Table // /////////////////////////////////////////////////////////////////////////////////////// SCOPE (CalcDimName.ComparisonLevel.&[2]) ; THIS = IIF( IsEmpty( CalcDimName.ComparisonLevel.DefaultMember ) OR IsEmpty( CalcDimName.ComparisonLevel.&[1] ), NULL, CalcDimName.ComparisonLevel.DefaultMember - CalcDimName.ComparisonLevel.&[1] ); NON_EMPTY_BEHAVIOR(THIS) = CalcDimName.ComparisonLevel.DefaultMember; END SCOPE; /////////////////////////////////////////////////////////////////////////////////////// // // DIFF. % OVER PREVIOUS YEAR - ID = 3 In TimeComparison Table // /////////////////////////////////////////////////////////////////////////////////////// SCOPE (CalcDimName.ComparisonLevel.&[3]) ; THIS = IIF( IsEmpty( CalcDimName.ComparisonLevel.&[2] ) OR IsEmpty( CalcDimName.ComparisonLevel.&[1] ), NULL, CalcDimName.ComparisonLevel.&[2] / CalcDimName.ComparisonLevel.&[1] ); NON_EMPTY_BEHAVIOR(THIS) = CalcDimName.ComparisonLevel.DefaultMember; FORMAT_STRING(THIS) = 'Percent'; END SCOPE; /////////////////////////////////////////////////////////////////////////////////////// // // Tuple (All years, All Months) is the default number (keeps compatibility with OWC11) // /////////////////////////////////////////////////////////////////////////////////////// ( PeriodAllYearMember, PeriodAllMonthMember, Except( CalcDimName.ComparisonLevel.ComparisonLevel.MEMBERS, CalcDimName.ComparisonLevel.DefaultMember ) ) = CalcDimName.ComparisonLevel.DefaultMember; /////////////////////////////////////////////////////////////////////////////////////// // // End Comparison // /////////////////////////////////////////////////////////////////////////////////////// // // // CREATE MEMBER CURRENTCUBE.[Measures].[DummyTX] AS Null YTD Sums the amount for the year, based on the specified date hierachy. Amount Measure 0 Year Level Hirachy 1 Time Based SUM(YTD(Year Level.CurrentMember), Amount)