MDX Totals / YTD

Question

Post reply

MDX Totals / YTD

Chrisd 89028

SSC Rookie

Points: 33

April 30, 2020 at 11:09 pm

#3747882

Admittedly I struggle with MDX, particularly with SharePoint's PerformancePoint which seems to add a bunch of noise.

I am trying to add totals (both rows and columns) to the following query, and later a single YTD column. All while preserving drill-through. Any hints much appreciated ....

WITH 
  MEMBER [Budgets].[Has Budget].[ Aggregation] AS 
    Aggregate
    (
      {
        [Budgets].[Has Budget].[All]
       ,[Budgets].[Has Budget].[HasBudget].&[False]
       ,[Budgets].[Has Budget].[HasBudget].&[True]
      }
    ) 
   ,SOLVE_ORDER = 0 
  MEMBER [Budgets].[Has Transactions].[ Aggregation] AS 
    Aggregate
    (
      {
        [Budgets].[Has Transactions].[All]
       ,[Budgets].[Has Transactions].[HasTransactions].&[False]
       ,[Budgets].[Has Transactions].[HasTransactions].&[True]
      }
    ) 
   ,SOLVE_ORDER = 0 
SELECT 
    Hierarchize
    (
      {
        Except
        (
          {
            [Dates].[Fiscal Years].[Fiscal Year].&[2020]
           ,[Dates].[Fiscal Years].[Fiscal Year].&[2020].Children
          }
         ,{
            Descendants
            (
              [Dates].[Fiscal Years].[Fiscal Year].&[2020]
             ,[Dates].[Fiscal Years].[Fiscal Year].&[2020].Level
             ,AFTER
            )
          }
        )
      }
    )
  * 
    {
      [Measures].[Actual]
     ,[Measures].[Budget]
     ,[Measures].[Variance]
    } ON COLUMNS
 ,NON EMPTY 
      Hierarchize({Descendants([Companies].[Company Id].[All],,AFTER)})
    * 
      (DISTINCT 
          Hierarchize
          (
            {
              Except
              (
                {Descendants([Accounts].[Account Number].[All],,AFTER)}
               ,{
                  [Accounts].[Account Number].[Company].&[Company001]
                 ,[Accounts].[Account Number].[Company].&[Company002]
                 ,[Accounts].[Account Number].[Company].&[Company003]
                }
              )
            }
          )
        * 
          Hierarchize({Descendants([Accounts].[Account Name].[All],,AFTER)})) ON ROWS
FROM [General Ledger]
WHERE 
  (
    [Budgets].[Has Budget].[ Aggregation]
   ,[Budgets].[Has Transactions].[ Aggregation]
   ,[Departments].[Department Name].[Department Name].&[300]
  );

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply

Site Owners SSC Guru Points: 80320 More actions · Answer 1

Thanks for posting your issue and hopefully someone will answer soon.

This is an automated bump to increase visibility of your question.