Summary

This article explains about How to calculate the date of birth in BEx query designer level, cube level coming as date of birth and how to calculate the Age in BEx query designer.

Author Bio:

Kodanda Pani K.V is a SAP BI consultant currently working with Rukshaya Emerging Technology Pvt Ltd (Bangalore/INDIA). I have over 3 years of experience in   various BW/BI implementation / Support Projects.

1. Introduction:

This article addresses the requirement of Formula variables in BW/BI Reports, in this article I am explaining the following types of variables.

1. How to use the Formula Variables in BW/BI Reports.

2. Live Scenarios:

I am working with Higher Education Application. Date of Birth is coming from the source and we have calculated the Age in BEx Query Designer level based on the student date of birth and system date.

3. Creation of Variables:

1. SAPExit Formula Variable on System Date

SAP Exit Formula Variable – OF_KEYDT variable on 0CALDAY, the properties are..

Type of Variable = Formula

Variable Name = OF_KEYDT

Processing by = SAP Exit

Characteristic = Calendar Day

Variable Represents = Signal Values

Variable Entry = Optional

Un Check ready for input.

Currency/Unit – Date

2. Create Student Date of Birth Variable.

Formula Variable – ZVF_DOB variable on 0CALDAY, the properties are..

Type of Variable = Formula

Variable Name = ZVF_DOB

Processing by = Replacement path

Characteristic = Date of Birth

Variable Represents = Signal Values

Variable Entry = Optional

Un Check ready for input.

Currency/Unit – Date

4. Cube level data:-

At the cube level we have Student Date of birth and Student number. Please find below sample values of some students with their birth dates whose ages have been calculated at the query level.

5. Existing system variable 0F_KEYDT

               We are using existing system variable key Date for Due date Net payment which gives the current date of the system.

SAP Exit Formula Variable – OF_KEYDT variable on 0CALDAY, the properties are..

Type of Variable = Formula

Variable Name = OF_KEYDT

Processing by = SAP Exit

Characteristic = Calendar Day

Variable Represents = Signal Values

Variable Entry = Optional

Un Check ready for input.

Currency/Unit – Date

6. Customer Variable ZVF_DOB

We created the customer variable student date of birth – type of variable formula,

Formula Variable – ZVF_DOB variable on 0CALDAY, the properties are..

Type of Variable = Formula

Variable Name = ZVF_DOB

Processing by = Replacement path

Characteristic = Date of Birth

Variable Represents = Signal Values

Variable Entry = Optional

Un Check ready for input.

Currency/Unit – Date

In Replacement path tab replace variable with – info object and replace with – Attribute value and attribute character key

In currency/Unit tab – Dimension object – Date.

7. How to write Formula.

               We created the two variables system date and student date of birth variable.

(System date – student date of birth) / 365.

8. How to create Age Slabs

  Based on the requirement we have developed Age Slabs like 20, 20 – 22, 22 – 24, 24 – 26,< 26.

In Extended tab Formula Collision – Use result of competing formula.

               Formula collision means conflict of formulae. In reporting, when there are two different formulae in rows (say: Summation) and column (say: Multiplication) structures, formula collision comes into picture.

In query definition, we have to give which formula we have to take into consideration for the cell in which formula collision occurs.

In Aggregation tab make is as Counter for all detailed values that are not zero, null, and error

And reference char – Student.

Now we can see the in Columns panel age slabs

9. Query output value

Now report level coming as Age only and next report Student numbers with Age slabs.

Query output value

Student number and with age slabs.

Related Contents

http://help.sap.com/saphelp_nw70/helpdata/en/d2/02223c5f00612be10000000a11402f/frameset.htm

Hopes it is Helps.