Aggregate values based on multiple dimension members to create a Profit & Loss report via SCOPE statement

Question

I am relatively new to SSAS MDX and am facing the problem to create aggregates over all measures of different dimension members. The following picture best describes what I'm trying to achieve: Problem and What the solution should be

This is a draft and a simplification of a more complex scenario, so don't bother ;-). After running a series of experiments I figured out to use a simple dimension without connection to other dimensions to be able to use the SCOPE statement to override the produced results in the report. The simple dimension "Kurz PuL" contains the preceding members (Umsatz .. Periodenergebnis) and shall produce the Profits + Loss report.

Kurz PuL Dimension

What currently works is to show results of single members (like Umsatz, Wareneinsatz, SbA, Personal). But I am not able to aggregate values of more than one member to obtain an aggregate, e.g.: of Umsatz + Wareneinsatz = Rohertrag. I tried different approaches but none worked. See the following SCOPE statements which are used to "override" the resulting values that are displayed in Excel (first image ^^):

SCOPE Statements:

    /*
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;

SCOPE ([Kurz PuL].[Calculated].[Umsatz]);
    THIS = AGGREGATE([EinfacheBwaZeile].[Hierarchy].&[U]);
END SCOPE;

SCOPE ([Kurz PuL].[Calculated].[Wareneinsatz]);
    THIS = AGGREGATE([EinfacheBwaZeile].[Hierarchy].&[W]);
END SCOPE;

/* DOES NOT WORK: */
SCOPE ([Kurz PuL].[Calculated].[Rohertrag]);
    THIS = AGGREGATE(FILTER([EinfacheBwaZeile].[Hierarchy].CurrentMember, { [EinfacheBwaZeile].[Hierarchy].&[U], [EinfacheBwaZeile].[Hierarchy].&[W] }));
END SCOPE;

SCOPE ([Kurz PuL].[Calculated].[Personal]);
    THIS = AGGREGATE( { [EinfacheBwaZeile].[Hierarchy].&[P] });
END SCOPE;

SCOPE ([Kurz PuL].[Calculated].[Marketing]);
    THIS = AGGREGATE( { [EinfacheBwaZeile].[Hierarchy].&[M] });
END SCOPE;

/* DOES NOT WORK: */
SCOPE ([Kurz PuL].[Calculated].[Deckungsbeitrag]);
    THIS = AGGREGATE( {   [EinfacheBwaZeile].[Hierarchy].&[U], [EinfacheBwaZeile].[Hierarchy].&[W], [EinfacheBwaZeile].[Hierarchy].&[R], [EinfacheBwaZeile].[Hierarchy].&[P]
                        , [EinfacheBwaZeile].[Hierarchy].&[M], [EinfacheBwaZeile].[Hierarchy].&[D] });
END SCOPE;

SCOPE ([Kurz PuL].[Calculated].[SbA]);
    THIS = AGGREGATE( { [EinfacheBwaZeile].[Hierarchy].&[S] });
END SCOPE;

/* DOES NOT WORK: */
SCOPE ([Kurz PuL].[Calculated].[EBITDA]);
    THIS = AGGREGATE( {   [EinfacheBwaZeile].[Hierarchy].&[U], [EinfacheBwaZeile].[Hierarchy].&[W], [EinfacheBwaZeile].[Hierarchy].&[R], [EinfacheBwaZeile].[Hierarchy].&[P]
                        , [EinfacheBwaZeile].[Hierarchy].&[M], [EinfacheBwaZeile].[Hierarchy].&[D]
                        , [EinfacheBwaZeile].[Hierarchy].&[S], [EinfacheBwaZeile].[Hierarchy].&[A] });
END SCOPE;

/* DOES NOT WORK: */
SCOPE ([Kurz PuL].[Calculated].[EBIT]);
    THIS = AGGREGATE( {   [EinfacheBwaZeile].[Hierarchy].&[U], [EinfacheBwaZeile].[Hierarchy].&[W], [EinfacheBwaZeile].[Hierarchy].&[R], [EinfacheBwaZeile].[Hierarchy].&[P]
                        , [EinfacheBwaZeile].[Hierarchy].&[M], [EinfacheBwaZeile].[Hierarchy].&[D]
                        , [EinfacheBwaZeile].[Hierarchy].&[S], [EinfacheBwaZeile].[Hierarchy].&[A]
                        , [EinfacheBwaZeile].[Hierarchy].&[I] });
END SCOPE;

/* DOES NOT WORK: */
SCOPE ([Kurz PuL].[Calculated].[EBT]);
    THIS = AGGREGATE( {   [EinfacheBwaZeile].[Hierarchy].&[U], [EinfacheBwaZeile].[Hierarchy].&[W], [EinfacheBwaZeile].[Hierarchy].&[R], [EinfacheBwaZeile].[Hierarchy].&[P]
                        , [EinfacheBwaZeile].[Hierarchy].&[M], [EinfacheBwaZeile].[Hierarchy].&[D]
                        , [EinfacheBwaZeile].[Hierarchy].&[S], [EinfacheBwaZeile].[Hierarchy].&[A]
                        , [EinfacheBwaZeile].[Hierarchy].&[I]
                        , [EinfacheBwaZeile].[Hierarchy].&[T] });
END SCOPE;

/* DOES NOT WORK: */
SCOPE ([Kurz PuL].[Calculated].[Periodenergebnis]);
    THIS = AGGREGATE( {   [EinfacheBwaZeile].[Hierarchy].&[U], [EinfacheBwaZeile].[Hierarchy].&[W], [EinfacheBwaZeile].[Hierarchy].&[R], [EinfacheBwaZeile].[Hierarchy].&[P]
                        , [EinfacheBwaZeile].[Hierarchy].&[M], [EinfacheBwaZeile].[Hierarchy].&[D]
                        , [EinfacheBwaZeile].[Hierarchy].&[S], [EinfacheBwaZeile].[Hierarchy].&[A]
                        , [EinfacheBwaZeile].[Hierarchy].&[I]
                        , [EinfacheBwaZeile].[Hierarchy].&[T]
                        , [EinfacheBwaZeile].[Hierarchy].&[E] });
END SCOPE;

I've been trying to find a resource that explains how to aggregate on more than one member but didn't find a solution. How can I use the AGGREGATE() function to combine/sum values that are related to multiple dimension members of [EinfacheBwaZeile].[Hierarchy].A, … .B, … .C ?

I really appreciate your answers! Thanks in advance, Cordt

-- UPDATE 2019-05-28 as reply to Moaz as of 2019-05-27: -- Hi Moaz, thank you for your suggestion. Sadly, that solution does not meet my needs. First thing to notice is, that I need a SCOPE-Statement, not a SELECT-MDX. Second, I need all the measures of specific members of another dimension to be "summarized". That looks at first sight like a "running total" but it depends on the values of "previous" members where some may be skipped. In words of the Adventure Works sample, the following shows what I need:

SCOPE (MountainBikeSales);
    THIS = AGGREGATE(Product.&[Bikes]);
END SCOPE;
SCOPE (BikesAndAccessories);
    THIS = AGGREGATE({ Product.&[Bikes], Product.&[Accessories] });
END SCOPE;
SCOPE (BikesAccsClothing);
    THIS = AGGREGATE({ Product.&[Bikes], Product.&[Accessories], Product.&[Clothing] });
END SCOPE;

Is that more clear? You could even think of a SCOPE that skips Accessories by summarizing Bikes and Clothing instead of Accessories. Problem to me is, that the first statement would succeed (that summarizes a single member) but the others won't.

Thank you for your advice! Cordt

Does anyone have an idea how to aggregate within a SCOPE multiple members? — Cordt

MoazRub MoazRub · Accepted Answer · 2019-05-27T14:46:33

I am not sure that i understand your problem exactly. But I think you want to know how to calculate running totals. It that is the case take a look at the below example

with 
member 
[Measures].[Internet Sales AmountRunningtotal]
as 
case when [Measures].[Internet Sales Amount] = null then null 
else 
sum({[Product].[Subcategory].firstchild:[Product].[Subcategory].currentmember},[Measures].[Internet Sales Amount])
end
select {[Measures].[Internet Sales Amount],
[Measures].[Internet Sales AmountRunningtotal]
} on columns,

non empty
([Date].[Calendar Year].[Calendar Year],[Date].[Calendar Quarter of Year].[Calendar Quarter of Year],
[Product].[Category].[Category],[Product].[Subcategory].[Subcategory])
on 
rows 
from 
[Adventure Works]

Result

Edit:Based on your edit

select 
{
[Measures].[Internet Sales Amount],
[Measures].[Internet Order Quantity],
[Measures].[Internet Tax Amount],
[Measures].[Internet Gross Profit Margin]
}on 0 , 
[Product].[Category].[Category]
on 1
from
[Adventure Works]

Results

Notice that all measures for Components have null values. Now Lets equate Components with Bikes & Accessories

Scope  ([Product].[Category].&[2]) ;  
this = (aggregate({[Product].[Category].&[1],[Product].[Category].&[4]},[Measures].[Measures].currentmember));
end scope;

select 
{
[Measures].[Internet Sales Amount],
[Measures].[Internet Order Quantity],
[Measures].[Internet Tax Amount],
[Measures].[Internet Gross Profit Margin]
}on 0 , 
[Product].[Category].[Category]
on 1
from
[Adventure Works]

Results

Aggregate values based on multiple dimension members to create a Profit & Loss report via SCOPE statement

1 Answers