Date related functions
Make timestamp from date key
Parameters map is a good solution to get current date; however, it present challenges to directly use date type, instead, data key as integer is easily to handle. In some cases, it is requested to turn it into real date in report. Code below can help_make_timestamp (
cast(#$CurrentDateKey{'Fact TableX'}# /10000 ,integer),
cast(#$CurrentDateKey{'Fact TableX'}# /100, integer) - ( cast(#$CurrentDateKey{'Fact TableX'}# /10000, integer))*100,
#$CurrentDateKey{'Fact TableX'}# - cast((#$CurrentDateKey{'Fact TableX'}# /100),integer)*100 )
Get last complete month date
Date + 1 Day - 1 Month, then get last day of month.
_last_of_month (_add_months (_add_days ([Fact].[Dim Time].[Current Date],1),-1))Last Complete Month in the format of STRING:
cast(extract(year, _add_months(current_date,-1)),varchar(4))||substring (cast(100+extract(month, _add_months(current_date,-1)),varchar(3)),2,2)
Last Complete Month in the format of NUMBER:
extract(year, _add_months(current_date,-1))*100+extract(month, _add_months(current_date,-1))
Make week start day
For report with weeks, it is requested to display the first day of each week. The data item can get first day of each week.case month(minimum([Dim Time].[Date] for [Dim Time].[Week Id]))
WHEN 1 THEN 'Jan'
WHEN 2 THEN 'Feb'
WHEN 3 THEN 'Mar'
WHEN 4 THEN 'Apr'
WHEN 5 THEN 'May'
WHEN 6 THEN 'Jun'
WHEN 7 THEN 'Jul'
WHEN 8 THEN 'Aug'
WHEN 9 THEN 'Sep'
WHEN 10 THEN 'Oct'
WHEN 11 THEN 'Nov'
WHEN 12 THEN 'Dec'
END
+
' ' + right('0' + cast(day(minimum([Dim Time].[Date] for [Dim Time].[Week Id])), varchar(2)), 2)
Half to date current year, prior year
When it is requested to get Half to date, get reference date first as data item, then get date started. CYTD- Current year to date; PYTD- Prior year to date; CHTD-Current half year to date; PHTD-Prior half year to date
[Dim Time].[Date] between [DateFrom] and [DateTo]
[DateFrom]
case when ([Current Month Number] >6 ) and (?TimePeriodGrp?='CHTD' or ?TimePeriodGrp?='PHTD') then _make_timestamp (year([DateTo]),07,01)
else _make_timestamp (year([DateTo]),01,01)
End
[DateTo]
case
when (?TimePeriodGrp?='CYTD') then ([Dim Time (Activity)].[ Current Date])
when (?TimePeriodGrp?='PYTD') then ( _add_years ([Dim Time (Activity)].[ Current Date], -1))
End
{ it also can be done by directly using filter
( ([Fact].[Dim Time].[Current Month Number] <7) and ([Fact].[Dim Time].[Year] = [Fact].[Dim Time].[Current Year] ) ) OR ( ([Fact].[Dim Time].[Current Month Number] >=7) and ([Fact].[Dim Time].[Year] = [Fact].[Dim Time].[Current Year] ) and [Fact].[Dim Time].[Month Number] >=7)
}
{ it also can be done by directly using filter
( ([Fact].[Dim Time].[Current Month Number] <7) and ([Fact].[Dim Time].[Year] = [Fact].[Dim Time].[Current Year] ) ) OR ( ([Fact].[Dim Time].[Current Month Number] >=7) and ([Fact].[Dim Time].[Year] = [Fact].[Dim Time].[Current Year] ) and [Fact].[Dim Time].[Month Number] >=7)
}
7d, MTD, YTD, PYTD
case when ?Relative Date?='7d'
then ( [Date] between _add_days(2007-05-17,-7) and (2007-05-17))
when ?Relative Date? ='MTD'
then( [Date] between _first_of_month(2007-05-17) and (2007-05-17))
when ?Relative Date? ='YTD'
then( [Date] between _add_days(2007-05-17,((_day_of_year(2007-05-17)-1)*-1))
and (2007-05-17))
Current YTD Sales:
if ( SalesDate between _make_timestamp(extract(year,current_date), 1, 1) and current_date )
then (SalesAmount)
else (0)
Prior YTD Sales:
if ( SalesDate between _make_timestamp((extract(year,current_date)-1), 1, 1) and _add_years(current_date,-1) )
then (SalesAmount)
else (0)
Get Last month of data available
assume available date is [AvailableDate] and time dimension is [DimTime]: (year(_add_months (_add_days ([Namespace].[DimTime].[ AvailableDate],1),-1)) * 100) + month(_add_months (_add_days ([Namespace].[ DimTime].[ AvailableDate],1),-1))
Get start date and end date based on last available month
Get year start date
1)
-_day_of_year ([End Date])
-366
2)
_add_days ([End Date], -_day_of_year ([End Date])+1)
1-Jan-12 12:00:00 AM
Get last complete Month date
1)
[Fact].[Dim Time].Current Date]
6-Jan-13 12:00:00 AM
2)
_add_days ([Fact].[Dim Time].Current Date],1)
7-Jan-13 12:00:00 AM
3)
_add_months (_add_days ([Fact].[Dim Time].Current Date],1),-1)
7-Dec-12 12:00:00 AM
4)
_last_of_month (_add_months (_add_days ([Fact].[Dim Time].Current Date],1),-1))
31-Dec-12 12:00:00 AM
Get last 13 Months
[Fact Table].[Dim Time].[Month Id] >=
(year(_add_months (_add_days ([Fact Table].[Dim Time].[Current Date],1),-13)) * 100) +
month(_add_months (_add_days ([Fact Table].[Dim Time].[Current Date],1),-13))and
[Fact Table].[Dim Time].[Month Id] <=
(year(_add_months (_add_days ([Fact Table].[Dim Time].[Current Date],1),-1)) * 100) +
month(_add_months (_add_days ([Fact Table].[Dim Time].[Current Date],1),-1))
Show date in crosstab
cast(cast (extract (year,[Returned items (query)].[Time dimension].[Date]), Char
(4))+substring (cast(cast(_round (((extract (month,[Returned items (query)].[Time
dimension].[Date])/100)+1)*100,0),integer),Char(3)),2,2)+substring (cast(cast
(_round (((extract (day,[Returned items (query)].[Time dimension].[Date])/100)+1)
*100,0),integer),Char(3)),2,2),integer)
string2date (
substring (number2string ([Query1].[Date]),1,4)
+'-'+
substring (number2string ([Query1].[Date]),5,2)
+'-'+
substring (number2string ([Query1].[Date]),7,2)
)
For IBM DB2 the calculation should look like this:
cast_integer(cast_char (Year([Returned items (query)].[Time dimension].[Date]) ,4)
||
right( replace ('0'||cast_char (Month([Returned items (query)].[Time dimension].
[Date]) ,2),' ',''),2)
||
right(replace ('0'||cast_char (day([Returned items (query)].[Time dimension].[Date]) ,
2),' ',''),2))
For Microsoft SQLServer the calculation should look like this:
cast_integer(convert({varchar},[Returned items (query)].[Time dimension].[Date],112))
For Oracle:
to_number(To_Char([Returned items (query)].[Time dimension].[Date], 'YYYYMMDD'))
Date related dimensional functions
Please load report specification for detailFind Current Period using closingPeriod Function
closingPeriod ([sales_and_marketing_cs].[Time].[Time].[Month])Find Current Period by Filtering on Measure Data
item(tail(filter(members([sales_and_marketing].[Time].[Time].[Month]), tuple([Revenue], currentMember([sales_and_marketing].[Time].[Time])) is not null), 1), 0)Check link for detail explanation http://www.ibm.com/developerworks/data/library/cognos/reporting/dimensional_queries/page561.html
Current Month/Quarter/YTD/ Trailing Twelve Months
[Month Current] = closingPeriod( [goc].[Years].[Years].[Month] )
Quarter
parent( [Month Current] )
YTD
total( currentMeasure within set
periodsToDate( [goc].[Years].[Years].[Year], [Month Current] ) )
Trailing Twelve Months
total( currentMeasure within set
lastPeriods( 12, [Month Current] ) )
Month % Change Prior Year
( [Month Current] - [Month Prior Year] ) / [Month Prior Year]
Where [Month Prior Year] = lag( [Month Current] , 12 )
Note: currentMeasure is in the context of the crosstab
Period to date
[monthClosingPeriod] =
closingPeriod( [GOC].[Years].[Years].[Month] )
[periodsToDateForClosingPeriodMonth] =
PeriodsToDate( [GOC].[Years].[Years].[Year] , [monthClosingPeriod] )
Show real date from power cube
how to turn current day to real date, but dynamically displayed.
Current Day_Date
descendants ([Current Day],1)
Yesterday_Date
descendants ([Yesterday],1)
descendants ([Yesterday],1)
Oracle date functions
Get the last day of the week
select trunc(sysdate, 'DAY')+6 from dual;
Get first day of year SELECT TRUNC(TO_DATE('17-DEC-2001'),'YEAR') "First Day" FROM Dual; Get last Day of Current Month
SELECT add_months(trunc(sysdate) - (to_number(to_char(sysdate,'DD')) - 1), 1) -1 FROM dual
Get first day of year SELECT TRUNC(TO_DATE('17-DEC-2001'),'YEAR') "First Day" FROM Dual; Get last Day of Current Month
SELECT add_months(trunc(sysdate) - (to_number(to_char(sysdate,'DD')) - 1), 1) -1 FROM dual
SQL server date functions
Get the first day of month
DATEADD(dd,-(DAY(DT.DAY_DT)-1),DT.DAY_DT)
Get date key
select cast(convert(varchar(8), getdate(), 112) as integer)
Get last Sunday
Select cast(convert(varchar(8), dateadd(day,-datepart(weekday,dateadd(WEEK,-1,GETDATE()))+1,dateadd(WEEK,-1,GETDATE())), 112) as integer)
Select cast(convert(varchar(8), dateadd(day,-datepart(weekday,dateadd(WEEK,-1,GETDATE()))+1,dateadd(WEEK,-1,GETDATE())), 112) as integer)
Useful Query items
2. TYD, QTD and HTD based day ( Assume [Fact Current Date],[Fact Current Quarter Number] and [Fact Current Quarter Number] )
#/* YTD at the begining of year*/#
_add_days ([End Date], -_day_of_year ([Fact Table].[Dim Time].[Fact Current Date])+1)
#/* QTD */#
if ([Fact Table].[Dim Time].[Quarter Id] = [Fact Table].[Dim Time].[Fact Current Year]*10+[Fact Table].[Dim Time].[Fact Current Quarter Number])
then ([Fact Table].[Fact Table].[Measure])
else (0)
#/* HTD */#
If ( [Fact Table].[Dim Time].[Fact Current Quarter Number] > 2 ) Then
( If ( [Fact Table].[Dim Time].[Quarter Number] > 2) Then
( If ([Fact Table].[Dim Time].[Quarter Id] <= [Fact Table].[Dim Time].[Fact Current Year]*10+[Fact Table].[Dim Time].[Fact Current Quarter Number])
Then [Fact Table].[Fact Table].[Measure])
else (0) )
Else ( 0) )
Else (
If ([Fact Table].[Dim Time].[Quarter Id]<=[Fact Table].[Dim Time].[Fact Current Year]*10+[Fact Table].[Dim Time].[Fact Current Quarter Number])
then ([Fact Table].[Fact Table].[Measure])
else (0) )
Time filter
filter(
[Time Dimension].[Time Hierarchy].[Date],
roleValue('_businessKey', currentMember([Time Dimension].[Time Hierarchy])) >= #sq(prompt('From', 'Date'))# and
roleValue('_businessKey', currentMember([Time Dimension].[Time Hierarchy])) <= #sq(prompt('To', 'Date'))#
)
[Time Dimension].[Time Hierarchy].[Date],
roleValue('_businessKey', currentMember([Time Dimension].[Time Hierarchy])) >= #sq(prompt('From', 'Date'))# and
roleValue('_businessKey', currentMember([Time Dimension].[Time Hierarchy])) <= #sq(prompt('To', 'Date'))#
)
Other useful tips and tricks
Display Report Run Times
layout calculation: Now() - AsOfNow()
Promote an individual report
Open the file ReportX.txt in a text editor such as Notepad and do the following:
a.Click Edit | Select All to highlight all the text in the file.
b.Click Edit | Copy to copy all the text in the file to the clipboard.
In Report Studio, click Tools | Open Report from Clipboard.
Extract Level
SUBSTR(
'/Level1/Level2/Level3/Level4/Level5/'||'/'
,INSTR('/Level1/Level2/Level3/Level4/Level5/'||'/','/',1,1)+1
,(INSTR('/Level1/Level2/Level3/Level4/Level5/'||'/','/',1,2)-INSTR ('/Level1/Level2/Level3/Level4/Level5/'||'/','/',1,1)-1)
)
Level2
SUBSTR(
'/Level1/Level2/Level3/Level4/Level5/'||'/'
,INSTR('/Level1/Level2/Level3/Level4/Level5/'||'/','/',1,2)+1
,(INSTR('/Level1/Level2/Level3/Level4/Level5/'||'/','/',1,3)-INSTR ('/Level1/Level2/Level3/Level4/Level5/'||'/','/',1,2)-1)
)
Level3
SUBSTR(
'/Level1/Level2/Level3/Level4/Level5/'||'/'
,INSTR('/Level1/Level2/Level3/Level4/Level5/'||'/','/',1,3)+1
,(INSTR('/Level1/Level2/Level3/Level4/Level5/'||'/','/',1,4)-INSTR ('/Level1/Level2/Level3/Level4/Level5/'||'/','/',1,3)-1)
)
Transform year quarter to date
to_date(decode(?Quarter?, 'Q1', '03/31/', 'Q2', '06/30/', 'Q3', '09/30/', 'Q4', '12/31/')||?Year?, 'MM/DD/YYYY')
Set up 'Current Month' and avoid prompt pop-up from query items in FM
case when #PROMPT('p_selected', 'string', '''CURRENT''')# = 'CURRENT' then add_months(current_date, -2) else (add_months(#PROMPT('Select a Month End Date','DATE''01-JAN-2000''','CURRENT_DATE')#,-1))
WHERE (
#PROMPT('PLEASE SELECT AS OF
MONTH IN YYYYMM FORMAT','STRING','''CURRENT''')# ='CURRENT'
AND
CALENDAR_YEAR_STR=CAST(date_part('year',add_months(CURRENT_DATE,-1)) AS
VARCHAR(20))
AND
CALENDAR_MONTH_STR=CAST(date_part('month',add_months(CURRENT_DATE,-1)) AS VARCHAR(20)) )
OR (
#PROMPT('PLEASE SELECT AS OF
MONTH IN YYYYMM FORMAT','STRING','''CURRENT''')# =CAST(
CAST(CALENDAR_YEAR_STR AS INTEGER)*100+CAST( CALENDAR_MONTH_STR AS INTEGER) AS VARCHAR(20)) )
Commonly used netezza date functions and how to do data manipulations?
--to select the current date and timestamp use:
select current_date
select current_timestamp
--to add months and convert date to integer use:
select to_number(to_char(add_months(current_date, -1),'YYYYMMDD'),'99999999') --to get date id
select to_number(to_char(add_months(current_date, -1),'YYYYMM'),'9999999') --to get month id
select date(current_date - cast('1 month' as interval))
--to convert timestamp to yyyy-mm-dd format:
select TO_CHAR(TO_DATE('2011-11-24 22:42:28','YYYY-MM-DD HH24:MI:SS'),'YYYY-MM-DD')
select TO_CHAR(current_timestamp,'YYYY-MM-DD')
--to extract year part from the current date
select extract (year from current_date)
select date_trunc('year', current_date)
to_number(to_char(current_date,'YYYY'),'9999') --integer format
--to extract day and month from the current date
select extract (day from current_date)
--to get first and last day of the year:
select to_date((extract (year from current_date)) || '0101','YYYYMMDD')
select to_date((extract (year from current_date)) || '1231','YYYYMMDD')
--to get first of the month given a date
select date_trunc('month', current_timestamp)
--to get last month end date
select date_trunc('month', current_timestamp)-1
--to add 7 days to current date
select date(current_date + cast('7 days' as interval))
--to get timestamp in a specific format
select to_timestamp( current_timestamp, 'YYYY-MM-DD HH24:MI:SS')
select to_timestamp( '2014-12-01 23:12:12', 'YYYY-MM-DD HH24:MI:SS')
--to add hours to a date
select TO_CHAR(date((current_date + cast('1 hour' as interval))),'YYYY-MM-DD')
--To calculate difference in days , weeks between two dates
select datediff(week,'2012-01-01','2015-12-31') as numweeks;
select datediff(day,'2009-01-01','2015-12-31') as numdays;
Commonly used oracle date functions
SELECT ADD_MONTHS( DATE '2016-02-29', 1 ) FROM dual;
SELECT CURRENT_DATE FROM dual;
SELECT CURRENT_TIMESTAMP FROM dual;
SELECT EXTRACT( MONTH FROM TO_DATE( '31-Dec-1999 15:30:20 ', 'DD-Mon-YYYY HH24:MI:SS' ) ) MONTH FROM dual;
SELECT LAST_DAY(CURRENT_DATE) FROM dual;
SELECT MONTHS_BETWEEN( DATE '2017-07-01', DATE '2017-01-01' ) MONTH_DIFF FROM dual;
SELECT NEXT_DAY(CURRENT_DATE) FROM dual;
SELECT TO_CHAR(ROUND( TO_DATE( '20-Jul-2017 16:30:15', 'DD-Mon-YYYY HH24:MI:SS' ) ), 'DD-Mon-YYYY HH24:MI:SS' ) rounded_result FROM dual;
SELECT TO_CHAR( CURRENT_DATE, 'YYYY-MM-DD' ) FROM dual;
SELECT TO_CHAR( CURRENT_DATE, 'DL' )FROM dual;
SELECT TO_CHAR(12345.67, '99999.9') FROM dual;
SELECT TO_CHAR(12345, '00000000') FROM dual;
SELECT TO_CHAR(CURRENT_DATE, 'YYYY_MM_DD') FROM dual;
SELECT TO_CHAR(CURRENT_DATE, 'Month') FROM dual;
SELECT TO_DATE('2003/07/09', 'yyyy/mm/dd') FROM dual;
SELECT TO_DATE('070903', 'MMDDYY') FROM dual;
SELECT TO_DATE('20020315', 'yyyymmdd') FROM dual;
SELECT TO_DATE('2015/05/15 8:30:25', 'YYYY/MM/DD HH:MI:SS') FROM dual;
How to placing a carriage return in a string
make a simple carriage return without using CHAR(10) or CHAR(13) or /r/n
[BI_TITLE] || '
' || [BI_DESCRIPTION] || '
' || 'Root cause: ' || [PRIMARYROOTCAUSE]
Useful links:
https://www.ibm.com/support/pages/output-wraps-multiple-rows-excel-2007: Output wraps into multiple rows in Excel 2007
Cognos Time and Date Functions from temple university
Using the periodsToDate function within IBM Cognos 10 Report Studio to Calculate an OLAP Running-Total
IBM Cognos 8 Framework Manager - Dimensional Modeling for Time Period Analysis
Disallow a user to send reports by e-mail
Automatically refreshing a report
What are the differences between DQM and CQE mode in IBM Cognos 10?
Bridge Query Subjects (only supported using DQM)
Working with Dynamic Filtering
How To Reduce Your Framework Manager Package
Thanks for the detailed explanation and it is really useful
ReplyDelete