Cognos 10 Report Studio Tutorial

Calculate Age

In this article, we will learn How to calculate the age?

For calculating age, we will use the _years_between function.

_years_between ( date_expression1, date_expression2 )

Type: Function
Expression: _years_between ( date_expression1, date_expression2 )
Return: _years_between function accepts two arguments and calculates numbers of years between two expressions. Finally, it returns a positive or negative integer number representing the number of years between "date_expression1" and "date_expression2". 
It is simple If "date_expression1" < "date_expression2" then a negative value is returned else a positive value returned.
Example: _years_between ( 2014-01-30 , 2012-04-03 )
Result: 1

For this tutorial, we are using IBM Cognos 10.2.1 with Go Sales Query FM Modal and Report Studio. In our demo Report, we have a Report Header, List and Report Footer. 

In our list, we have Sales Staff first name, last name, and birth date. Open list query and add a new data item. For the data item expression use "_years_between (current_date,[Sales (query)].[Sales staff].[Birth date])". Save it and run the report. Following image shows the report output.

Note: current_date will returns a date value representing the current date of the computer that the database software runs on.

 

Similarly, you can use _months_between for calculating age in months and _days_between for calculating age in days.

_months_between

Type: Function
Expression: _months_between ( date_expression1, date_expression2 )
Return: _months_between function accepts two arguments and calculates numbers of months between two expressions. Finally, it returns a positive or negative integer number representing the number of months between "date_expression1" and "date_expression2". 
It is simple If "date_expression1" < "date_expression2" then a negative value is returned else a positive value returned.
Example: _months_between ( 1978-01-13 , 2018-05-06 )
Result: 483

_days_between

Type: Function
Expression: _days_between ( date_expression1, date_expression2 )
Return: _days_between function accepts two arguments and calculates numbers of days between two expressions. Finally, it returns a positive or negative integer number representing the number of months between "date_expression1" and "date_expression2". 
It is simple If "date_expression1" < "date_expression2" then a negative value is returned else a positive value returned.
Example: _months_between ( 1978-01-13 , 2018-05-06 )
Result: 14723

Following image shows the implementation for all the above functions.

Finally, we have a _ymdint_between function.

_ymdint_between

Type: Function
Expression: _ymdint_between ( date_expression1, date_expression2 )
Return: Returns a number representing the difference between "date_expression1" and "date_expression2". The returned value has the form YYYYMMDD, where YYYY represents the number of years, MM represents the number of months, and DD represents the number of days.
Example: _ymdint_between ( 1990-04-30 , 2003-02-05 )
Result: 120906, meaning 12 years, 9 months and 6 days.

Following image shows the final version of our Report.

Following is the report xml file to download.

FileDownload
Calculate Age.txtLogin to Download
...

About The Author

Hello, I am Mayank Sanghvi I am primarily a BI developer with experience in MSBI and Cognos BI Suit. Also having interest in various other technologies such as Android, Java, C#, ASP .NET and other. I enjoy learning new technologies and share my experience with others.

0 Comments
Leave A Comment

Please login to post your valuable comments.

add