ex aggregation
TRANSCRIPT
-
8/9/2019 Ex Aggregation
1/23
SAP COMMUNITY NETWORK SDN - sdn.sap.com | BPX - bpx.sap.com | BA - boc.sap.com | UAC - uac.sap.com© 2011 SAP AG 1
!"#$% '(")(* +,-(.)#/$0%%1(%2)#/$ #$ 3+, 4(./1)"56-($21#/
Applies to:
SAP BW 3.x & SAP BI Net Weaver 2004s. For more information, visit the Business Intelligence homepage.
SummaryThe Objective of this article is to explain the necessity of Nested Exception Aggregation in BEx reports with ascenario.
Author: KVS VaraPrasad
Company: Cognizant Technology Solutions (India).
Created on: 25 October 2011
Author Bio
KVS VaraPrasad is working as a SAP BW consultant with Cognizant TechnologySolutions. He has got rich experience on various BW Implementation/Support Projects inboth SAP BW 3.5 and SAP BW 7.0.
-
8/9/2019 Ex Aggregation
2/23
Using Nested Exception Aggregation in BEx Reports- Scenario
SAP COMMUNITY NETWORK SDN - sdn.sap.com | BPX - bpx.sap.com | BA - boc.sap.com | UAC - uac.sap.com© 2011 SAP AG 2
Table of Contents
SAP BW 3.x & SAP BI Net Weaver 2004s. For more information, visit the Business Intelligence homepage. . 1
Summary............................................................................................................................................................. 1
Author Bio ................................ .................. ................. .................. ................. .................. ................. .................. 1
Purpose:.............................................................................................................................................................. 3
Report Requirements: .................. ................. ................. .................. ................. ................. .................. ............... 3
Counting total number of deliveries: ................................................................................................................... 4 How to create IF then ELSE Conditions in Calculated Key Figures: ................ ................. .................. ............. 12
Counting On-time Deliveries: ............................................................................................................................ 12
Counting Late Deliveries: .................................................................................................................................. 14
Counting Early Deliveries: ................................................................................................................................ 15
Nested Exception Aggregation: ........................................................................................................................ 17
Nested Exception Aggregation on On-time Deliveries CKF: ............................................................................ 18
Nested Exception Aggregation on Deliveries Late CKF: .................................................................................. 19
Nested Exception Aggregation on Deliveries Early CKF: ................ ................. .................. ................. ............. 20
Related Content .................. ................. .................. ................. .................. ................. .................. ................. .... 22
Disclaimer and Liability Notice .......................................................................................................................... 23
-
8/9/2019 Ex Aggregation
3/23
Using Nested Exception Aggregation in BEx Reports- Scenario
SAP COMMUNITY NETWORK SDN - sdn.sap.com | BPX - bpx.sap.com | BA - boc.sap.com | UAC - uac.sap.com© 2011 SAP AG 3
Purpose:This article addresses the requirement of Nested Exception aggregation in BW/BI Reports; I n this article I’mgoing to explain the following list:
How to use the formula variables with replacement path as processing type. How to use IF ELSE condition in the calculated Keyfigures. How to create and use Nested Exception Aggregation.
Report Requirements:In some sales based reports, Users wants to see the Total number of deliveries, On-time deliveries, earlydeliveries and Late Deliveries for particular time period.
To display the data as per above requirements we have to create Calculated Keyfigures to count the deliverynumbers based on Actual Delivery Date and Requested Delivery Date of Delivery number.
On-time Delivery: An On-time Delivery is a delivery number whose Actual Delivery Date isequal to Requested Delivery Date.
Late Delivery: A Late Delivery is a delivery number whose Actual Delivery Date is greater thanRequested Delivery Date.
Early Delivery: An Early Delivery is a delivery number whose Actual Delivery Date is less thanRequested Delivery Date.
-
8/9/2019 Ex Aggregation
4/23
Using Nested Exception Aggregation in BEx Reports- Scenario
SAP COMMUNITY NETWORK SDN - sdn.sap.com | BPX - bpx.sap.com | BA - boc.sap.com | UAC - uac.sap.com© 2011 SAP AG 4
Counting total number of deliveries:To count the delivery numbers we need a formula variable to hold the Delivery number for each record andthat formula should process with Replacement path with Delivery number (0DELIV_NUMB) as referenceCharacteristic.
Step 1: Create a Calculate Key Figure - Number of Deliveries
Step 2: Give the Description and technical names as Number of Deliveries and CKF_NO_DEL_1 respectively and create a new Formula Variable to hold the Delivery numbers.
-
8/9/2019 Ex Aggregation
5/23
Using Nested Exception Aggregation in BEx Reports- Scenario
SAP COMMUNITY NETWORK SDN - sdn.sap.com | BPX - bpx.sap.com | BA - boc.sap.com | UAC - uac.sap.com© 2011 SAP AG 5
Step 3: Create ZVAR_DEL_NUMB formula variable
Description: Delivery Number
Type of Variable: Formula
Processing by: Replacement Path
Reference Characteristic: Delivery (0DELIV_NUMB)
Set the Replacement Path attributes with below settings:
-
8/9/2019 Ex Aggregation
6/23
Using Nested Exception Aggregation in BEx Reports- Scenario
SAP COMMUNITY NETWORK SDN - sdn.sap.com | BPX - bpx.sap.com | BA - boc.sap.com | UAC - uac.sap.com© 2011 SAP AG 6
This formula is not ready for input (Don’t Check Variable is Ready for Input)
Set the Dimension as Number (Delivery number is numerical field):
-
8/9/2019 Ex Aggregation
7/23
Using Nested Exception Aggregation in BEx Reports- Scenario
SAP COMMUNITY NETWORK SDN - sdn.sap.com | BPX - bpx.sap.com | BA - boc.sap.com | UAC - uac.sap.com© 2011 SAP AG 7
Step 4: Drag and drop the Delivery Number (ZVAR_DEL_NUMB) formula variable into the Detail view ofNumber of Deliveries (CKF_NO_DEL_1) Calculated Key Figure.
Use default aggregation type Standard Aggregation as Exception Aggregation
-
8/9/2019 Ex Aggregation
8/23
Using Nested Exception Aggregation in BEx Reports- Scenario
SAP COMMUNITY NETWORK SDN - sdn.sap.com | BPX - bpx.sap.com | BA - boc.sap.com | UAC - uac.sap.com© 2011 SAP AG 8
Comparing Date values:Create two different formula variables with Replacement Path as processing type and having Actual DeliveryDate and Requested Delivery Dates as reference characteristics.
Formula variable with Actual Delivery date as reference characteristic:
Description: Actual Delivery Date Formula
Technical Name: VAR_ACT_DEL_DATE_FORMType of Variable: Formula
Processing by: Replacement Path
Reference Characteristic: Actual Delivery Date (0ACT_DL_DTE)
In the Replacement Path tab, select the Replace Variable with attribute as InfoObject and replace with Keyvalue. The Off set values for date field starts with 0(Zero) and the length should be 8.
-
8/9/2019 Ex Aggregation
9/23
Using Nested Exception Aggregation in BEx Reports- Scenario
SAP COMMUNITY NETWORK SDN - sdn.sap.com | BPX - bpx.sap.com | BA - boc.sap.com | UAC - uac.sap.com© 2011 SAP AG 9
Don’t Check the Variable is ready for Input
Set the Dimension as Date in the Currency/Unit tab
-
8/9/2019 Ex Aggregation
10/23
-
8/9/2019 Ex Aggregation
11/23
Using Nested Exception Aggregation in BEx Reports- Scenario
SAP COMMUNITY NETWORK SDN - sdn.sap.com | BPX - bpx.sap.com | BA - boc.sap.com | UAC - uac.sap.com© 2011 SAP AG 11
Don’t Check the Variable is ready for Input
Set the Dimension as Date in the Currency/Unit tab
Now we can compare Acutal Delivery date with Requested Delivery date value by using logical operatorsexample: = , < ,>, =…..
Date Difference = Actual Delivery Date Formula (VAR_ACT_DEL_DATE_FORM) - Requested Delivery DateFormula (VAR_REQ_DEL_DATE).
-
8/9/2019 Ex Aggregation
12/23
Using Nested Exception Aggregation in BEx Reports- Scenario
SAP COMMUNITY NETWORK SDN - sdn.sap.com | BPX - bpx.sap.com | BA - boc.sap.com | UAC - uac.sap.com© 2011 SAP AG 12
How to create IF then ELSE Conditions in Calculated Key Figures:General IF ELSE Condition:
IF (is true)
(Execute the statements block)
ELSE IF
ELSE IF
IF ELSE Condition Example:
We can write IF then Else statement for different comparisons in Calculated KeyFigure / Formula.
Example: (A ==B)*C + (A>B) *D + (A B is True
Then the statement becomes (0) * C + (1) * D + (0) * E Result is D
Case 3:
If A < B is True
Then the statement becomes (0) * C + (0) * D + (1) * E Result is E.
Counting On-time Deliveries:To count On-time Deliveries we have to compare Actual Delivery Date with Requested Delivery date. If bothdates are same then only we should consider that delivery number.
Create a new calculated keyfigure On-Time Del (CKF_ON_TIME_2) and drag and drop the Actual DeliveryDate and Requested Delivery date formula variables into the Detail view.
-
8/9/2019 Ex Aggregation
13/23
Using Nested Exception Aggregation in BEx Reports- Scenario
SAP COMMUNITY NETWORK SDN - sdn.sap.com | BPX - bpx.sap.com | BA - boc.sap.com | UAC - uac.sap.com© 2011 SAP AG 13
Formula to count On-time Deliveries:
On-Time Deliveries = ([VAR_ACT_DEL_DATE_FORM] Actual Delivery Date == [VAR_REQ_DEL_DATE]Requested Delivery Date ) * [CKF_NO_DEL_1] Number of Deliveries
Set the Exception Aggregation value to TOTAL and Reference Characteristic Requested Delivery date.
Exception Aggregation as „Total : As the name signifies, this function is used to aggregate the result of akey figure in a different manner than standard OLAP functionality. It works same as Standard Exception
Aggregation but summarized keyfigure with respect to the Reference Characteristic.
Similarly create Late deliveries and Early deliveries calculated keyfigures to count Late and Early deliveries.
-
8/9/2019 Ex Aggregation
14/23
Using Nested Exception Aggregation in BEx Reports- Scenario
SAP COMMUNITY NETWORK SDN - sdn.sap.com | BPX - bpx.sap.com | BA - boc.sap.com | UAC - uac.sap.com© 2011 SAP AG 14
Counting Late Deliveries:Create a new Calculated Keyfigure Deliveris Late (CKF_DEL_LATE_4) and drag and drop required formulavariables into the Detail view of CKF.
Formula to count Late Deliveries:
Deliveries Late = ([VAR_ACT_DEL_DATE_FORM] Actual Delivery Date > [VAR_REQ_DEL_DATE]Requested Delivery Date ) * [CKF_NO_DEL_1] Number of Deliveries
Set the Exception Aggregation value to TOTAL and Reference Characteristic Requested Delivery date(0DSDEL_DATE)
-
8/9/2019 Ex Aggregation
15/23
Using Nested Exception Aggregation in BEx Reports- Scenario
SAP COMMUNITY NETWORK SDN - sdn.sap.com | BPX - bpx.sap.com | BA - boc.sap.com | UAC - uac.sap.com© 2011 SAP AG 15
Counting Early Deliveries:Create a new Calculated Keyfigure Deliveris Early (CKF_DEL_EARLY_6) and drag and drop requiredformula variables into the Detail view of CKF.
Formula to count Early Deliveries:
Deliveries Early = ([VAR_ACT_DEL_DATE_FORM] Actual Delivery Date < [VAR_REQ_DEL_DATE]Requested Delivery Date ) * [CKF_NO_DEL_1] Number of Deliveries
Set the Exception Aggregation value to TOTAL and Reference Characteristic Requested Delivery date(0DSDEL_DATE)
-
8/9/2019 Ex Aggregation
16/23
Using Nested Exception Aggregation in BEx Reports- Scenario
SAP COMMUNITY NETWORK SDN - sdn.sap.com | BPX - bpx.sap.com | BA - boc.sap.com | UAC - uac.sap.com© 2011 SAP AG 16
Result of Default Aggregation “Standard Exception Aggregation”:
If we are not using Exception Aggregation as Total then the Calculated Key figures uses Standard Aggregation then the report result is as below:
Report Result:
Place Actual Delivery date and Requested Delivery dates in the columns of BEx designer of the report fordetail analysis of above error, then the report result will be as follows:
-
8/9/2019 Ex Aggregation
17/23
Using Nested Exception Aggregation in BEx Reports- Scenario
SAP COMMUNITY NETWORK SDN - sdn.sap.com | BPX - bpx.sap.com | BA - boc.sap.com | UAC - uac.sap.com© 2011 SAP AG 17
Report result:
As per above screenshot, we can understand that On-time, Delivery Late and Delivery Early are notsummarized at Actual, Requested Delivery date levels. In order to get summarized result we have to useTOTAL as an Exception Aggregation type with respect to dates. Here we need nested exception aggregationto get correct results. i.e first we have to create one Calculated Keyfigure with TOTAL Exception aggregatewith reference to Requested Delviery date and then create another Calculated Key Figure/Formula and callthe earlier CKF(with Requested Delivery date as reference cahracteristic) into this and set the TOTALException aggregation with reference characteristic Actual Delivery Date .
Nested Exception Aggregation:
If we need multiple exception aggregations or reference characteristics, we can nest formulas and calculatedkey figures in one another and specify an exception aggregation for each formula or calculated key figure.
Necessity of Nested Exception Aggregation:
As highlighted in the above screen shot, the results of the On-time deliveries, Deliveries Late and Deliveriesearly with the Actual Delivery Dates are „X’.
If we create one level of exception aggregation with reference to Requested Delivery date then we get belowresult (Detail level):
As per the result, the deliveries are not aggregated along with Actual Delivery Date (‘X’ value in resultcolumns). To avoid this situation we need one more level of Calculated Key Figures/Formula with Exception
Aggreateion on current Calculated KeyFigures.
-
8/9/2019 Ex Aggregation
18/23
Using Nested Exception Aggregation in BEx Reports- Scenario
SAP COMMUNITY NETWORK SDN - sdn.sap.com | BPX - bpx.sap.com | BA - boc.sap.com | UAC - uac.sap.com© 2011 SAP AG 18
Nested Exception Aggregation on On-time Deliveries CKF:Create New Calculate KeyFigure Deliveries On-Time (CKF_DEL_ON_TIME_3) and drag and drop the On-Time Del (CKF_ON_TIME_2) into the Detail View.
Set the Exception Aggregation to Total and Actual Delivery date (0ACT_DL_DTE) as ReferenceCharacteristic:
-
8/9/2019 Ex Aggregation
19/23
Using Nested Exception Aggregation in BEx Reports- Scenario
SAP COMMUNITY NETWORK SDN - sdn.sap.com | BPX - bpx.sap.com | BA - boc.sap.com | UAC - uac.sap.com© 2011 SAP AG 19
Nested Exception Aggregation on Deliveries Late CKF:Similarly Create New Calculate KeyFigure for Late Deliveries (CKF_LATE_DEL_5) and drag and drop theDeliveries Late (CKF_DEL_LATE_4) into the Detail View.
Set the Exception Aggregation to Total and Actual Delivery date (0ACT_DL_DTE) as ReferenceCharacteristic
-
8/9/2019 Ex Aggregation
20/23
Using Nested Exception Aggregation in BEx Reports- Scenario
SAP COMMUNITY NETWORK SDN - sdn.sap.com | BPX - bpx.sap.com | BA - boc.sap.com | UAC - uac.sap.com© 2011 SAP AG 20
Nested Exception Aggregation on Deliveries Early CKF:Create new Calculate KeyFigure Early Deliveries (CKF_EARLY_DEL_7) and drag and drop the DeliveriesEarly (CKF_DEL_LATE_4) CKF into the Detail View.
Set the Exception Aggregation to Total and Actual Delivery date (0ACT_DL_DTE) as ReferenceCharacteristic
-
8/9/2019 Ex Aggregation
21/23
Using Nested Exception Aggregation in BEx Reports- Scenario
SAP COMMUNITY NETWORK SDN - sdn.sap.com | BPX - bpx.sap.com | BA - boc.sap.com | UAC - uac.sap.com© 2011 SAP AG 21
Drag and drop the Deliveries On-Time (CKF_DEL_ON_TIME_3), Late Deliveries (CKF_LATE_DEL_5) andEarly Deliveries (CKF_EARLY_DEL_7) Calculated Key Figures into the Rows and Company code into theCoulumns.
Report result (Summarized Level):
-
8/9/2019 Ex Aggregation
22/23
Using Nested Exception Aggregation in BEx Reports- Scenario
SAP COMMUNITY NETWORK SDN - sdn.sap.com | BPX - bpx.sap.com | BA - boc.sap.com | UAC - uac.sap.com© 2011 SAP AG 22
Related Contenthttp://help.sap.com/saphelp_nw70/helpdata/en/75/21054da1392649948e5b94e4fc4bce/frameset.htm
http://help.sap.com/saphelp_nw70/helpdata/en/03/6ba03cc24efd1de10000000a114084/content.htm
http://help.sap.com/saphelp_nw04/helpdata/en/6f/56853c08c7aa11e10000000a11405a/content.htm
http://help.sap.com/saphelp_nw70/helpdata/en/ca/5f9ac61a205a459d0e7ef313d10321/content.htm
http://help.sap.com/saphelp_nw70/helpdata/en/75/21054da1392649948e5b94e4fc4bce/frameset.htmhttp://help.sap.com/saphelp_nw70/helpdata/en/03/6ba03cc24efd1de10000000a114084/content.htmhttp://help.sap.com/saphelp_nw04/helpdata/en/6f/56853c08c7aa11e10000000a11405a/content.htmhttp://help.sap.com/saphelp_nw70/helpdata/en/ca/5f9ac61a205a459d0e7ef313d10321/content.htmhttp://help.sap.com/saphelp_nw70/helpdata/en/ca/5f9ac61a205a459d0e7ef313d10321/content.htmhttp://help.sap.com/saphelp_nw04/helpdata/en/6f/56853c08c7aa11e10000000a11405a/content.htmhttp://help.sap.com/saphelp_nw70/helpdata/en/03/6ba03cc24efd1de10000000a114084/content.htmhttp://help.sap.com/saphelp_nw70/helpdata/en/75/21054da1392649948e5b94e4fc4bce/frameset.htm
-
8/9/2019 Ex Aggregation
23/23
Using Nested Exception Aggregation in BEx Reports- Scenario
Disclaimer and Liability NoticeThis document may discuss sample coding or other information that does not include SAP official interfaces and therefore is n otsupported by SAP. Changes made based on this information are not supported and can be overwritten during an upgrade.
SAP will not be held liable for any damages caused by using or misusing the information, code or methods suggested in this document,and anyone using these methods does so at his/her own risk.
SAP offers no guarantees and assumes no responsibility or liability of any type with respect to the content of this technical article orcode sample, including any liability resulting from incompatibility between the content within this document and the materials andservices offered by SAP. You agree that you will not hold, or seek to hold, SAP responsible or liable with respect to the content of thisdocument.