MDX Script - MdxScript Commands Index

Calculations Command Script

                  /*
The CALCULATE command controls the aggregation of leaf cells in the cube.
If the CALCULATE command is deleted or modified, the data within the cube is affected.
You should edit this command only if you manually specify how the cube is aggregated.
*/
                  CALCULATE;
                  CREATE SET CURRENTCUBE.[Active Fiscal Years]
                  AS Extract
                  (
                  {
                  ([Date].[Fiscal Year].[Fiscal Year].Members
                  ,[Date].[Is Current Fiscal Year].&[Y]
                  ,[Date].[Is Available For Finance].&[Y])
                  ,
                  ([Date].[Fiscal Year].[Fiscal Year].Members
                  ,[Date].[Is Current Fiscal Year].Children
                  ,[Date].[Is Available For Finance].&[Y]
                  )
                  }
                  ,[Date].[Fiscal Year]
                  );
                  CREATE SET CURRENTCUBE.[Salary Category]
                  AS Extract
                  (
                  {
                  ([Gl Code].[Gl Category].[All],
                  [Gl Code].[Hr Classification].[Hr Classification].Members)
                  +
                  ([Gl Code].[Gl Category].[Gl Category].Members,
                  [Gl Code].[Hr Classification].[Hr Classification].Members)

                  ([Gl Code].[Gl Category].[Gl Category].Members,
                  [Gl Code].[Hr Classification].&[N/AA])
                  }
                  , [Gl Code].[Gl Category]
                  ) ;
                  CREATE MEMBER CURRENTCUBE.[Measures].[BudgetToPeriodAmount]
                  AS (
                  Aggregate
                  (
                  PeriodsToDate
                  (
                  [Date].[FiscalCalendar].[Fiscal Year]
                  ,[Date].[FiscalCalendar].currentmember
                  )
                  ,[Measures].[Budget Amount]
                  )
                  ),
                  FORMAT_STRING = "#,##0.00;-#,##0.00",
                  VISIBLE = 1 ,  DISPLAY_FOLDER = 'JournalAmounts' ,  ASSOCIATED_MEASURE_GROUP = 'Fact Gl Transactions';
                  CREATE MEMBER CURRENTCUBE.[Measures].[JournalToPeriodAmount]
                  AS (
                  Aggregate
                  (
                  PeriodsToDate
                  (
                  [Date].[FiscalCalendar].[Fiscal Year]
                  ,[Date].[FiscalCalendar].currentmember
                  )
                  ,[Measures].[Journal Amount]
                  )
                  ),
                  FORMAT_STRING = "#,##0.00;-#,##0.00",
                  VISIBLE = 1 ,  DISPLAY_FOLDER = 'JournalAmounts' ,  ASSOCIATED_MEASURE_GROUP = 'Fact Gl Transactions';
                  CREATE MEMBER CURRENTCUBE.[Measures].[JournalToPriorYearPeriodAmount]
                  AS (
                  Aggregate
                  (
                  PeriodsToDate
                  (
                  [Date].[FiscalCalendar].[Fiscal Year]
                  ,[Date].[FiscalCalendar].currentmember.LAG(12)
                  )
                  ,[Measures].[Journal Amount]
                  )
                  ),
                  FORMAT_STRING = "#,##0.00;-#,##0.00",
                  VISIBLE = 1 ,  DISPLAY_FOLDER = 'JournalAmounts' ,  ASSOCIATED_MEASURE_GROUP = 'Fact Gl Transactions';
                  CREATE MEMBER CURRENTCUBE.[Measures].[TotalExpensesToPriorYearPeriodJournalAmount]
                  AS (
                  (
                  [Gl Code].[Gl Class Code].[5]
                  ,[Measures].[JournalToPriorYearPeriodAmount]
                  )
                  +
                  (
                  [Gl Code].[Gl Class Code].[6]
                  ,[Measures].[JournalToPriorYearPeriodAmount]
                  )
                  +
                  (
                  [Gl Code].[Gl Class Code].[7]
                  ,[Measures].[JournalToPriorYearPeriodAmount]
                  )
                  ),
                  FORMAT_STRING = "#,##0.00;-#,##0.00",
                  VISIBLE = 1 ,  DISPLAY_FOLDER = 'JournalAmounts' ,  ASSOCIATED_MEASURE_GROUP = 'Fact Gl Transactions';
                  CREATE MEMBER CURRENTCUBE.[Measures].[TotalRevenueToPeriodJournalAmount]
                  AS (
                  (
                  [Gl Code].[Gl Class Code].[4]
                  ,[Measures].[JournalToPeriodAmount]
                  )
                  ),
                  FORMAT_STRING = "#,##0.00;-#,##0.00",
                  VISIBLE = 1 ,  DISPLAY_FOLDER = 'JournalAmounts' ,  ASSOCIATED_MEASURE_GROUP = 'Fact Gl Transactions';
                  CREATE MEMBER CURRENTCUBE.[Measures].[TotalExpensesToPeriodJournalAmount]
                  AS (
                  (
                  [Gl Code].[Gl Class Code].[5]
                  ,[Measures].[JournalToPeriodAmount]
                  )
                  +
                  (
                  [Gl Code].[Gl Class Code].[6]
                  ,[Measures].[JournalToPeriodAmount]
                  )
                  +
                  (
                  [Gl Code].[Gl Class Code].[7]
                  ,[Measures].[JournalToPeriodAmount]
                  )
                  ),
                  FORMAT_STRING = "#,##0.00;-#,##0.00",
                  VISIBLE = 1 ,  DISPLAY_FOLDER = 'JournalAmounts' ,  ASSOCIATED_MEASURE_GROUP = 'Fact Gl Transactions';
                  CREATE MEMBER CURRENTCUBE.[Measures].[TotalExpensesToPeriodNoBudget]
                  AS (
                  (
                  [Gl Code].[Gl Class Code].[5]
                  ,[Measures].[ActualToPeriodAmount]
                  )
                  +
                  (
                  [Gl Code].[Gl Class Code].[6]
                  ,[Measures].[ActualToPeriodAmount]
                  )
                  +
                  (
                  [Gl Code].[Gl Class Code].[7]
                  ,[Measures].[ActualToPeriodAmount]
                  )
                  +
                  (
                  [Gl Code].[Gl Class Code].[5]
                  ,[Measures].[CommitmentToPeriodAmount]
                  )
                  +
                  (
                  [Gl Code].[Gl Class Code].[6]
                  ,[Measures].[CommitmentToPeriodAmount]
                  )
                  +
                  (
                  [Gl Code].[Gl Class Code].[7]
                  ,[Measures].[CommitmentToPeriodAmount]
                  )
                  +
                  (
                  [Gl Code].[Gl Class Code].[5]
                  ,[Measures].[EncumbranceToPeriodAmount]
                  )
                  +
                  (
                  [Gl Code].[Gl Class Code].[6]
                  ,[Measures].[EncumbranceToPeriodAmount]
                  )
                  +
                  (
                  [Gl Code].[Gl Class Code].[7]
                  ,[Measures].[EncumbranceToPeriodAmount]
                  )
                  ),
                  FORMAT_STRING = "#,##0.00;-#,##0.00",
                  VISIBLE = 1 ,  DISPLAY_FOLDER = 'JournalAmounts' ,  ASSOCIATED_MEASURE_GROUP = 'Fact Gl Transactions';
                  CREATE MEMBER CURRENTCUBE.[Measures].[TotalRevenueToPeriodNoBudget]
                  AS (
                  (
                  [Gl Code].[Gl Class Code].[4]
                  ,[Measures].[ActualToPeriodAmount]
                  )
                  +
                  (
                  [Gl Code].[Gl Class Code].[4]
                  ,[Measures].[CommitmentToPeriodAmount]
                  )
                  +
                  (
                  [Gl Code].[Gl Class Code].[4]
                  ,[Measures].[EncumbranceToPeriodAmount]
                  )
                  ),
                  FORMAT_STRING = "#,##0.00;-#,##0.00",
                  VISIBLE = 1 ,  DISPLAY_FOLDER = 'JournalAmounts' ,  ASSOCIATED_MEASURE_GROUP = 'Fact Gl Transactions';
                  CREATE MEMBER CURRENTCUBE.[Measures].[ActualToPeriodAmount]
                  AS (
                  Aggregate
                  (
                  PeriodsToDate
                  (
                  [Date].[FiscalCalendar].[Fiscal Year]
                  ,[Date].[FiscalCalendar].currentmember
                  )
                  ,[Measures].[Actuals Amount]
                  )
                  ),
                  FORMAT_STRING = "#,##0.00;-#,##0.00",
                  VISIBLE = 1 ,  DISPLAY_FOLDER = 'JournalAmounts' ,  ASSOCIATED_MEASURE_GROUP = 'Fact Gl Transactions';
                  CREATE MEMBER CURRENTCUBE.[Measures].[CommitmentToPeriodAmount]
                  AS (
                  Aggregate
                  (
                  PeriodsToDate
                  (
                  [Date].[FiscalCalendar].[Fiscal Year]
                  ,[Date].[FiscalCalendar].currentmember
                  )
                  ,[Measures].[Commitment Amount]
                  )
                  ),
                  FORMAT_STRING = "#,##0.00;-#,##0.00",
                  VISIBLE = 1 ,  DISPLAY_FOLDER = 'JournalAmounts' ,  ASSOCIATED_MEASURE_GROUP = 'Fact Gl Transactions';
                  CREATE MEMBER CURRENTCUBE.[Measures].[EncumbranceToPeriodAmount]
                  AS (
                  Aggregate
                  (
                  PeriodsToDate
                  (
                  [Date].[FiscalCalendar].[Fiscal Year]
                  ,[Date].[FiscalCalendar].currentmember
                  )
                  ,[Measures].[Encumbrance Amount]
                  )
                  ),
                  FORMAT_STRING = "#,##0.00;-#,##0.00",
                  VISIBLE = 1 ,  DISPLAY_FOLDER = 'JournalAmounts' ,  ASSOCIATED_MEASURE_GROUP = 'Fact Gl Transactions';
                  CREATE MEMBER CURRENTCUBE.[Measures].[PercentageExpensesByClass]
                  AS (
                  CASE
                  WHEN
                  IsEmpty([Measures].[Journal Amount])
                  THEN 0
                  ELSE
                  (
                  [Gl Code].[Gl Class Code]
                  ,[Measures].[Journal Amount]
                  )

                  (
                  (
                  [Gl Code].[Gl Class Code].[5]
                  ,[Measures].[Journal Amount]
                  )
                  +
                  (
                  [Gl Code].[Gl Class Code].[6]
                  ,[Measures].[Journal Amount]
                  )
                  +
                  (
                  [Gl Code].[Gl Class Code].[7]
                  ,[Measures].[Journal Amount]
                  )
                  )
                  END
                  ),
                  FORMAT_STRING = "#,##0.00 %;-#,##0.00 %",
                  VISIBLE = 1 ,  DISPLAY_FOLDER = 'JournalAmounts' ,  ASSOCIATED_MEASURE_GROUP = 'Fact Gl Transactions';
                  CREATE MEMBER CURRENTCUBE.[Measures].[PriorYearPeriodPercentageExpensesByClass]
                  AS (
                  CASE
                  WHEN
                  IsEmpty([Measures].[PriorYearPeriodJournalAmount])
                  THEN 0
                  ELSE
                  (
                  [Gl Code].[Gl Category Code]
                  ,[Measures].[PriorYearPeriodJournalAmount]
                  )

                  (
                  (
                  [Gl Code].[Gl Class Code].[5]
                  ,[Measures].[PriorYearPeriodJournalAmount]
                  )
                  +
                  (
                  [Gl Code].[Gl Class Code].[6]
                  ,[Measures].[PriorYearPeriodJournalAmount]
                  )
                  +
                  (
                  [Gl Code].[Gl Class Code].[7]
                  ,[Measures].[PriorYearPeriodJournalAmount]
                  )
                  )
                  END
                  ),
                  FORMAT_STRING = "#,##0.00 %;-#,##0.00 %",
                  VISIBLE = 1 ,  DISPLAY_FOLDER = 'JournalAmounts' ,  ASSOCIATED_MEASURE_GROUP = 'Fact Gl Transactions';