Skip to Content
Author's profile photo Kodanda Pani KV

Step-By-Step How to Calculate Age In BEx Query Designer

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.

Assigned tags

      36 Comments
      You must be Logged on to comment or reply to a post.
      Author's profile photo Former Member
      Former Member

      Thanks a lot for this doc. Sometimes we need to calculate this kind of stuff.

      Author's profile photo SVU 123
      SVU 123

      Good one for the Beginers KP.

      rgds

      SVU

      Author's profile photo Umashankar Poojar
      Umashankar Poojar

      Thanks for sharing!

      Umashankar

      Author's profile photo Phani KV
      Phani KV
      Blog Post Author

      Thanks Umashanka and manna das...

      Author's profile photo Former Member
      Former Member

      Hi Kodanda,

      Thanks for sharing, nice scenario...

      Author's profile photo Former Member
      Former Member

      Hi,

      Thanks for the tip.

      Rémi

      Author's profile photo CH Raman
      CH Raman

      Good presentation and useful too. Thanks for sharing.

      Author's profile photo Phani KV
      Phani KV
      Blog Post Author

      Thanks Raman ur compliment.

      Author's profile photo Suman Chakravarthy K
      Suman Chakravarthy K

      Great stuff 🙂 Gave 5 ...

      Author's profile photo Phani KV
      Phani KV
      Blog Post Author

      Thanks Suman for ur comment.

      Author's profile photo Phani KV
      Phani KV
      Blog Post Author

      Thanks Suman ur comment, it is boost for me.

      Author's profile photo Satendra Mishra
      Satendra Mishra

      Nice stuff...... 🙂 Bookmarked.. 🙂

      Regards

      Sm

      Author's profile photo Phani KV
      Phani KV
      Blog Post Author

      Thanks Satendra..

      Author's profile photo Former Member
      Former Member

      Helpful INFO, Thanks For sharing 🙂 🙂

      Author's profile photo Krishna Chaitanya
      Krishna Chaitanya

      Good one great job 🙂 Gave 5.....  😉

      Regards,

      Krishna Chaitanya

      Author's profile photo Ganesh Bothe
      Ganesh Bothe

      Hi ,

      Nice document..thanks for sharing 🙂

      Author's profile photo Satendra Mishra
      Satendra Mishra

      Hi,

      Nice document thanks for sharing...

      Regards,

      SM

      Author's profile photo Phani KV
      Phani KV
      Blog Post Author

      Thanks to all ur compliments.

      Author's profile photo Former Member
      Former Member

      Hi

      Nice documentation.

      I have a question here:

      In the column structure what calculations done in the Selections (In hide mode).Can you explain this please....

      Thanks,

      Purushotham.

      Author's profile photo Phani KV
      Phani KV
      Blog Post Author

      Hi purushothama Reddy,

      I created New selection that KF Hide values, after that i applied New formula that is show values.

      Thanks,

      Phani.

      Author's profile photo Former Member
      Former Member

      Nice document...

      Helped a lot in related scenarios...

      Thanks.

      Author's profile photo Anshu Lilhori
      Anshu Lilhori

      Nice presentation with all screenshot and step by step walk through..

      Regards,

      AL

      Author's profile photo Phani KV
      Phani KV
      Blog Post Author

      Thanks Anshu,

      I got best appreciation form BEx Expert.

      Thanks,

      Phani.

      Author's profile photo Former Member
      Former Member

      Valuable information and thanks for sharing.

      Kiran.

      Author's profile photo Former Member
      Former Member

      Nice Explanation keep it up...

      Thanks,

      Phani.

      Author's profile photo Joice Samuel
      Joice Samuel

      Nice  explanation Phani :-).

      Keep it up!!

      Thanks,

      Joice

      Author's profile photo Former Member
      Former Member

      Hi Pani,

      Good representation of figures/images for better understanding.

      Thanks a lot for sharing.

      Regards,

      Bansi

      Author's profile photo Phani KV
      Phani KV
      Blog Post Author

      Thanks Joice and Bansi 🙂

      Author's profile photo Sherif Elkoumi
      Sherif Elkoumi

      Very nice info. Kodanda,

      can you upload it as pdf, so it could be easily to download it.

      Sherif

      Author's profile photo CH Raman
      CH Raman

      Hi Sherif,

      Right side, under Like button, below the Actions you may see one option as " View as PDF.Thru that option you can save pdf format.

      Thanks

      Author's profile photo Sherif Elkoumi
      Sherif Elkoumi

      Hallo Ramanjaneyulu,

      I am grateful to you for your help 🙂 really appreciate your quick response.

      Sherif

      Author's profile photo Hernan Valenzuela
      Hernan Valenzuela

      I use a simplier formula to calculate the age of a person:
      Just take his birthday and divide it with 365,25.
      The decimal places just do the job for leap years in a more simplier way and more accurate.

      Author's profile photo Former Member
      Former Member

      Thanks alot for the wonderful document.Can i ask you a question?We have same scenario,but i want to aggregate the sum of students in particular class.How is it posible?

      Author's profile photo Phani KV
      Phani KV
      Blog Post Author

      Hi,

      thanks for asking the question.

      if have the any class object put it into rows or free char panel - it will aggregate the based on the class and student number.

      Thanks,

      Phani.

      Author's profile photo Former Member
      Former Member

      hi,

      Thanks for your quick reply.

      Actually in our scenario we are using materials instead of students.And we use department instead of class.

      In my current report, material is drill down and different slabs of stock ageing are showing.

      But users need to know the department level drill down,instead of each article.Here Department is hierarchy of material.

      We are including department as below,

      Right click on the material and then properties and select Retailailing system article structure and select Expansion level "2", as follows,

      /wp-content/uploads/2014/12/2014_12_15_172730_608237.png

      While doing like above we are geting department but the stock ageing value aggregated is different.

      Any information reqarding this would be helpful.

      Author's profile photo Former Member
      Former Member

      Hi Phani,

      Can we add any standard formula variable like you added 0F_KEYDT to our created query ?

      If yes then how to do that ?

      I am new to the SAP Field please let me know...