OBIEE Tips #1: Calculate the first day of the current month in answers
1 – Introduction
This post explains how to calculate the first day of the current month using OBIEE functions; this tip is useful when you need to build reports that shows current month data by default.
The steps below can be used also to calculate the first date of a given month.
2 – Calculate the first day of the current month
The OBIEE expression to calculate the first day of the current month is the following:
TIMESTAMPADD(SQL_TSI_DAY, -1*(DAYOFMONTH(CURRENT_DATE )-1) , CURRENT_DATE )
Let’s try to understand it.
CURRENT_DATE: OBIEE function that returns the current system date
DAYOFMONTH(<<expression>>): return the number of the passed day within a month (e.g. if <<expression>> equals 05/12/2010, the function returns 5)
TIMESTAMPADD(<<interval>>, <<expression>>, <<timestamp>>): adds a specified number of intervals to a specified timestamp, and returns a single timestamp; valid values for <<interval>> are: SQL_TSI_SECOND, SQL_TSI_MINUTE, SQL_TSI_HOUR, SQL_TSI_DAY, SQL_TSI_WEEK, SQL_TSI_MONTH, SQL_TSI_QUARTER, SQL_TSI_YEAR
In the answers we can for example add to columns from the time dimension and change the formula that generate the values for the second column by adding the expression above.
Assuming that today is Nov 3rd, in the expression above we are adding to the current month (e.g. November), the number of the current day of the month (e.g. 3), minus 1 (so, 2 days), multiplied by -1 (cause we really want to subtract this number of days).
So, the resulting expression for the current date (Nov. 3rd) would be: 3 + [-1*(2)] = 1 (The first day of the month).
Things are going to be more clear below by watching the screenshots. Let’s add another column that shows the current date:
Obviously, there are many other ways to get this information, but OBIEE does not provide a function to get the first day of the current month, so if you want to calculate it in the answer, using OBIEE functions, that’s probably the best way.
The expression used is useful to calculate also the first day of a given month, by changing the last parameter of the TIMESTAMPADD function with the date we need.
TIMESTAMPADD(SQL_TSI_DAY, -1*(DAYOFMONTH(CURRENT_DATE )-1) , <<timestamp>> )
3 – Filter report data by the first day of the current month
We just need to filter our time dimension by using the result of the expression above. We want the dates belonging to the time dimension to be greater or equal the first day of the current month. To do this, we add the expression in the filter:
Then we convert the filter in a SQL filter to make everything clear.
We can drop the Firts Day Of Current Month and Current Date columns, unless they are needed for the report output.
As we can see, now dates have been filtered and we have only data from November 1st.
Thanks,
Satya
This post explains how to calculate the first day of the current month using OBIEE functions; this tip is useful when you need to build reports that shows current month data by default.
The steps below can be used also to calculate the first date of a given month.
2 – Calculate the first day of the current month
The OBIEE expression to calculate the first day of the current month is the following:
TIMESTAMPADD(SQL_TSI_DAY, -1*(DAYOFMONTH(CURRENT_DATE )-1) , CURRENT_DATE )
Let’s try to understand it.
CURRENT_DATE: OBIEE function that returns the current system date
DAYOFMONTH(<<expression>>): return the number of the passed day within a month (e.g. if <<expression>> equals 05/12/2010, the function returns 5)
TIMESTAMPADD(<<interval>>, <<expression>>, <<timestamp>>): adds a specified number of intervals to a specified timestamp, and returns a single timestamp; valid values for <<interval>> are: SQL_TSI_SECOND, SQL_TSI_MINUTE, SQL_TSI_HOUR, SQL_TSI_DAY, SQL_TSI_WEEK, SQL_TSI_MONTH, SQL_TSI_QUARTER, SQL_TSI_YEAR
In the answers we can for example add to columns from the time dimension and change the formula that generate the values for the second column by adding the expression above.
Assuming that today is Nov 3rd, in the expression above we are adding to the current month (e.g. November), the number of the current day of the month (e.g. 3), minus 1 (so, 2 days), multiplied by -1 (cause we really want to subtract this number of days).
So, the resulting expression for the current date (Nov. 3rd) would be: 3 + [-1*(2)] = 1 (The first day of the month).
Things are going to be more clear below by watching the screenshots. Let’s add another column that shows the current date:
Obviously, there are many other ways to get this information, but OBIEE does not provide a function to get the first day of the current month, so if you want to calculate it in the answer, using OBIEE functions, that’s probably the best way.
The expression used is useful to calculate also the first day of a given month, by changing the last parameter of the TIMESTAMPADD function with the date we need.
TIMESTAMPADD(SQL_TSI_DAY, -1*(DAYOFMONTH(CURRENT_DATE )-1) , <<timestamp>> )
3 – Filter report data by the first day of the current month
We just need to filter our time dimension by using the result of the expression above. We want the dates belonging to the time dimension to be greater or equal the first day of the current month. To do this, we add the expression in the filter:
Then we convert the filter in a SQL filter to make everything clear.
We can drop the Firts Day Of Current Month and Current Date columns, unless they are needed for the report output.
As we can see, now dates have been filtered and we have only data from November 1st.
Thanks,
Satya
No comments:
Post a Comment