Cognos 10 Report Studio Tutorial

How to Create Month to Date Report

In this article, we will learn How to create Month to Date or MTD report in IBM Cognos 10 Report Studio?
In this article, we will focus on

1. Month to Date (MTD) Calculation and Concept
2. Month to Date (MTD) Report
3. Compare data between the current month and the previous month.

Following is the image for the demo report which we will create in this article. 

Data
We are using vLemon visitor database for the year 2018. Following is the download URL of the excel file for the same.

FileDownload
vLemonVisitors.csvLogin to Download

In this file, we have each visitor visit date, time and country.

Prerequisite 
You need an FM Package for the above data. We already published it. We didn't add any business logic in the FM. We will add all the calculations in the reporting layer.

What is Month to Date or MTD?
Month to Date report or MTD refers to the period beginning the first day of the current month up to the current date. We need to calculate the date based on the current date.

Concept

First, we need to find the current date. Using the current date we will find the first date of the current month.

In Cognos to get the current date, we use current_date.
current_date: It returns a date value representing the current date of the computer that the database software runs on.
Example: current_date
Result: 2018-03-04
We have the current date next we need to get the first date of the current month. To get the first date of the current month we will use _first_of_month.

_first_of_month(date)
Returns a date or date-time, depending on the provided argument, by converting "date expression" to a date with the same year and month but with the day set to 1. It means the first day of the month.
Example: _first_of_month (2018-08-10)
Result: 2018-08-1

We have the first date. Next, we need to use the first date and the current date to get the current month data.

Current Month Data Filte

Code

<pre class="brush:as3;">
cast([Presentation].[Visitors].[VisitDate],date) BETWEEN

cast(

_first_of_month(current_date)

,date)
AND 
cast(current_date,date)</pre>

<p>&nbsp;</p>

Our query with the above filter will return the data for the current month. Next, we need to create a query to get the Last month data. To get the last month data, we needed the last month start and the end date. We will use current_date get the last month information. To calculate the last month, we need to first find the first date of the current month. Then we will subtract one day from the first date of the current month. It will return the last date of the last month. Then we will again use the _first_of_month function with the last day of the last month as an argument. It will return the first day of the last month.

_first_of_month (date)
Returns a date or datetime, depending on the argument, by converting "date expression" to a date with the same year and month but with the day set to 1.
Example: _first_of_month (2018-08-10)
Result: 2018-08-01

Next, we will use _add_days function to subtract one day from the month first date. It will return the last date of the last month. Then again we use _month to get the month number.

_add_days (date, integer_expression)
Returns the date or date time, depending on the format of "date", that results from adding "integer_expression" days to "date".
Add Days
Example: _add_days (2018-08-01, 1)
Result: 2018-08-02
Subtract Days
Example: _add_days (2018-08-01, -1)
Result: 2018-07-31

Following is the step by step calculation for the Last Day of the Last month

_first_of_month(current_date) => _first_of_mont(2018-08-10) = 2018-08-01

_add_days(_first_of_mont(current_date),-1)=>_add_days(2018-08-01,-1)=2018-07-31

Following is the step by step calculation for the First Day of the Last month

_first_of_month(current_date) => _first_of_mont(2018-08-10) = 2018-08-01

_add_days(_first_of_mont(current_date),-1)=>_add_days(2018-08-01,-1)=2018-07-31

_first_of_month(_add_days(_first_of_mont(current_date),-1))=>_first_of_month(2018-07-31) = 2018-07-01 

Now, we have the start and the end date for the last month. Use both the dates in a filter to get the last month data.

Last Month Filter

Code

<pre class="brush:as3;">
cast([Presentation].[Visitors].[VisitDate],date) BETWEEN
cast(
	_first_of_month(
		_add_days
		( 
			_first_of_month(current_date)
			, -1
		)
	)
,date)

AND 

cast(_add_days
( 
	_first_of_month(current_date)
	, -1
),date)
</pre>

Finally, we need a single query for the line chart and cross-tab. Create a final query which is the union of the current month and the last month query. Use the final query for the line chart and the cross-tab.

...

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