Examples for CALCULATED MEMBER (SAP Library

Examples for CALCULATED MEMBER

You can use the MDX Calculated Member construct to define new Members for special functions, depending on existing Members of Dimensions and Measures. The following examples show how the MDX function Calculated Member is used to:

Calculate formulas (example 1)
Filter using multiple values (example 2)
Calculate the linear regression (example 3)

Example 1

Use Calculated Member to:

Create formulas ad hoc,

in which you can use expressions that are not key figures,

as well as special MDX commands such as CURRENTMEMBER, PREVMEMBER, LEAD and LAG

The OLAP processor returns the figures; the MDX processor makes the calculation.

The following code fragment gives an example of this: At runtime, the Calculated Member profit change from previous month (Profit Change) is calculated by subtracting the Profit for the previous month from the profit for the current month:

WITH MEMBER [Measures].[Profit Change] AS
     '( ([Measures].[CKF_SI_PROFIT]) - ([Measures].[CKF_SI_PROFIT],
            [0CALMONTH].PREVMEMBER) )'
SELECT
     { [Measures].[CKF_SI_PROFIT], [Measures].[Profit Change] }
            ON COLUMNS,
     NON EMPTY [0CALMONTH].[LEVEL01].MEMBERS ON ROWS
FROM [0D_SD_C03/SAP_DEMO_ODBO]

The following graphic displays the results table: Both the profit and the profit change against the previous month is shown for all months of the year 2001.

This graphic is explained in the accompanying text

Example 2

Using the MDX function AGGREGATE allows you to carry out multiple filtering at run time.

The following MDX fragment gives an example of this. At run time, the filter specified in the WHERE clause is constructed. The system aggregates along the three values in the Calculated Member AGGREGATE function.

WITH MEMBER [0D_PLANT].[Aggregate] AS
     'AGGREGATE( { [0D_PLANT].[1111],
                                [0D_PLANT].[2222],
                                [0D_PLANT].[6666] } )'
SELECT
     { [Measures].[CKF_SI_PROFIT],
         [Measures].[0D_DOCUMENT],
         [Measures].[0D_OORVALSC] }
         ON COLUMNS,
     NON EMPTY [0CALMONTH].[LEVEL01].MEMBERS
         ON ROWS
FROM [0D_SD_C03/SAP_DEMO_ODBO]
WHERE ([0D_PLANT].[Aggregate])

The following graphic shows the results table: The system returns the profit, the number of documents, and the open orders for the months in 2001 for the selected values OD_PLANT = 1111,2222,6666.

This graphic is explained in the accompanying text

Example 3

Using Calculated Member also allows you to apply linear regression, a special form of time series analysis. In linear regression, the line of best-fit is calculated from a series of dots. This function can be used to predict future values.

The following code fragment shows an appropriate example:

WITH
     SET [Month] AS
        YTD( [0CALMONTH].[200112] )
     MEMBER [Measures].[Predict] AS
        LinRegPoint( Rank( [0CALMONTH].CURRENTMEMBER, [Month] ),
                                 [Month],
                                 [Measures].[CKF_SI_PROFIT],
                                 Rank( [0CALMONTH].CURRENTMEMBER,
                                    [Month] ) )
SELECT
     { [Measures].[CKF_SI_PROFIT], [Measures].[Predict] }
          ON COLUMNS,
     [Month] ON ROWS
FROM [0D_SD_C03/SAP_DEMO_ODBO]

The following graphic displays the results table: The profit is displayed as is the predicted value taken from the linear regression calculation:

This graphic is explained in the accompanying text

The following chart depicts the actual profit values per month (blue dots) against the predicted values (red dots) on the line of best-fit:

This graphic is explained in the accompanying text