Cognos Analytics provides self-service business intelligence training that enable users to efficiently navigate and access data to create and run customized reports and dashboards. The engaging, intent-driven interface features content-sensitive menus that guide users through the development process.

Publicly Available Training Options

Blink > Cognos = https://blink.ucsd.edu/technology/bi/tools/cognos/index.html#Training
Cognos: How to Schedule Report Distribution = https://ucsdservicedesk.service-now.com/services?id=kb_article_view&sysparm_article=KB0032959
Cognos Dashboards or Reports =

UCSD Available Training Options

OneDrive > Cognos Trainings > Processing in Cognos vs Hana

Sample Codes

Hana Codes = https://help.sap.com/docs/SAP_HANA_PLATFORM/4fe29514fd584807ac9f2a04f6754767/20e6a27575191014bd54a07fd86c585d.html

What	Code
monthname( Date )	SQL function that Cognos likes but Hana does not. Do not use with Activity Hubs.
Set null dates to null	cast (NULL, date) Use this to force cognos to treat Null values as dates
Remove null values	Filter for [Field Name] is not null This performs better than using [Field Name] <> ''
Return not null value	Don't use isnull, use coalesce COALESCE([EAH Employee ID], [RAH Employee ID]) COALESCE([Measure Name Amount], 0) must have the same data type
Days Open Group (days)	"if ( [Days Open] <=1) then ('1 day open') else if ([Days Open] between 1 and 2) then ('2 days open') else if ([Days Open] between 2 and 7) then ('2-7 days open') else if ([Days Open] between 7 and 14) then ('7-14 days open') else if ([Days Open] between 14 and 30) then ('14-30 days open') else if ([Days Open] between 30 and 60) then ('30-60 days open') else if ([Days Open] between 60 and 90) then ('60-90 days open') else ('90+ days open')"
Formatted Date Range	numtodsinterval(if ([End Date] is null) then ([Open Date]) else ([End Date] - [Open Date]), 'day')
Format Date	numtodsinterval([item]), 'interval type')
Format Date	DateDiff()
Format HH:MM time	to_char([Time], 'HH:MI AM')
Open Time Group (date time)	"CASE WHEN [Open Time] <= 001 00:00:00.000 THEN '24 Hours' WHEN [Open Time] > 001 00:00:00.000 AND [Open Time] <= 002 00:00:00.000 THEN '48 Hours' WHEN [Open Time] > 002 00:00:00.000 AND [Open Time] <= 007 00:00:00.000 THEN '1 week' WHEN [Open Time] > 007 00:00:00.000 AND [Open Time] <= 014 00:00:00.000 THEN '2 weeks' WHEN [Open Time] > 014 00:00:00.000 AND [Open Time] <= 028 00:00:00.000 THEN '4 weeks' ELSE '> 4 weeks' END"
End of Day	11:59:59
Start of Day	0:00:00
Yesterday	[Date] = current_date - 1 00:00:00.000
Last Week Starting Sunday	[Week - Sunday Start] between _add_days(current_date, -13) and _add_days(current_date, -7)
Last Week	between _add_days(current_date, -7) and _add_days(current_date, -1)
Last Month	between _first_of_month(_add_months(current_date, -1)) and _last_of_month(_add_months(current_date, -1))
Current Year Filter	extract (year, [Close Date]) = extract(year, (_last_of_month(_add_months(current_date, -1))))
Fiscal YTD	"[start year] = if (extract(month from current_date) <= 10) then (extract(year from current_date)-1) else (extract(year from current_date)) [start char yr] = cast([start year], varchar(4)) [start date] = cast(([start char yr] \|\| '-10-01'), date) filter = [Date] between [start date] and _last_of_month(_add_months(current_date, -1))"
YTD Change	Current Year - Prior Year
YTD % Growth	(Current Year - Prior Year) / absolute values (Prior Year)
Month Names	"CASE [MonthNum] WHEN 1 then 'Jan' WHEN 2 then 'Feb' WHEN 3 then 'Mar' WHEN 4 then 'Apl' 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' ELSE 'Dec' END"
Previous 4 months	"[Open Date] between _first_of_month(_add_months(current_date, -4)) and _last_of_month(_add_months(current_date, -1))"
First letter for first word	substr([item],1,1)
Returns 1st word	substr(UPPER([Short Description]), 1, (instr([Short Description],' ',1,1)))
Returns 4th word	"trim(substr(' '\|\|[Event Service Description]\|\|' ', instr(' '\|\|[Event Service Description]\|\|' ',' ',1,4)+1, instr(' '\|\|[Event Service Description]\|\|' ',' ',1,4+1)- instr(' '\|\|[Event Service Description]\|\|' ',' ',1,4)-1))"
Group by AH	CASE when Package like 'EAH%' then 'EAH' when Package like 'FINAH%' then 'FINAH' when Package like 'PAH%' then 'PAH' when Package like 'RAH%' then 'RAH' when Package like 'SAH%' then 'SAH' else 'Other' END
Seconds to time interval	cast(cast([Total Avail Time] as interval second) as interval hour to second)
Number to time interval	numtodsinterval([x])
Zero Time	cast(0, time)
Day from time interval	extract(day, [x])
Display last 2 years	tail ([Year], 2)
Change 4 to 04	substring (([x]+100), 2, 2)
Convert to number	to_num([x])
html open in new tab	<a href="http://myURL" target="_blank">text</a>
replaces 'test' with null	replace([X], 'test')
Convert number to string	to_char([x])
Approvals	"if ([Task Approval] = 'rejected') then ('Rejected') else if ([Approval] <> 'rejected' AND [Request Item Stage] = 'Request Cancelled') then ('Cancelled') else ('Approved')"
Month Format	to_char(date_trunc('Month',[Ticket Received Date]),'MON-YYYY')
Month Format	to_char([Date],'MMYYYY')
Create Null Dates	"if ([Date] = 9999-12-31T00:00:00.000000000 ) then (null) else ([Date])"
n indicates max size of field - may work without (n)	cast(field, character (n))
Cast as Date	cast( '1970-01-01', date)
Field converted from seconds to time interval	nvl(field,0) * cast ('000 00:00:01.000', interval day to second)
n is number of digits - p is number of decimals	cast(field, numeric (n,p))
Yesterday	_add_days (current_date, - 1)
8 days ago	_add_hours (current_date, -8)
Replaces value 'x' with value 'y'. Translate only works for single value text items	Translate([FIELD], 'x', 'y')
Displays all values in string up to 'abcd'. IF string 'abcd' is not in the field, will display blank.	Substr([FIELD], 1, instr([FIELD], 'abcd') -1)
Found in Assignee_inc and Assignee_task queries	[Incident Open Week - Sunday Start] <= _add_days(current_date, -7)
Capitalizes the first letter of the string in FIELD.	upper(substr([FIELD], 1, 1)) \|\| lower(substr([FIELD], 2))
Capitalizes the first letter each word.	initcap([FIELD])
Concatenates 2 string fields. Verify results when null involved.	[FIELD1] \|\| [FIELD2]
Cast as Date	cast(1900-01-01,date)
(current_timestamp) creates a Time Zone, Oracle automatically took care of the TZ. Netezza doesn't like it.	(cast(current_timestamp, timestamp))
Reduce text to fix Netezza requirement and place in single row	maximum (if ([Request Item Option] = 'Make Model') then (cast(lower([Request Item Option Text]), character (100))) else (' ') for [Request Item Number])
Changes <BR> to a blank.	Replace([FIELD], '<BR>', ' ')
Replace a value	if (instr([FIELD], '<BR>') = 0) then ([FIELD]) else (substr([FIELD], 1, instr([FIELD], '<BR>')-1))
Field converted from seconds to time interval	nvl([FIELD], 0) * cast('000 00:00:01.000', interval day to second)
Field converted from seconds to time interval	coalesce([FIELD], 0) * cast('000 00:00:01.000', interval day to second)
time interval between two timestamps	select extract(epoch from '2014-08-05 08:00:28'::timestamp - '2014-08-04 08:00:23'::timestamp)
time interval between two timestamps	age([time1], [time2])
remove last 5 characters of string	substring([Term], 1, (char_length ([Term])-5))
group values of field output and return one of the groups	SUBSTR[ING]_REGEXPR( <pattern> [ FLAG <flag> ] IN <regex_subject_string> [ FROM <start_position> ] [ OCCURRENCE <regex_occurrence> ] [ GROUP <regex_capture_group> ] ) Example: SELECT SUBSTR_REGEXPR('([[:digit:]]{4})([[:digit:]]{2})([[:digit:]]{2})' IN '20140401' GROUP 3) "substring_regexpr" FROM DUMMY; String value = '20140401' Requested output is GROUP3 --- which is the last 2 digits group (the '01' at the end of the string) Pattern ---------- '([[:digit:]]{4})([[:digit:]]{2})([[:digit:]]{2})' Group (section) 1 is 4 digits ------ ([[:digit:]]{4}) Group (section) 2 is 2 digits ------ ([[:digit:]]{2}) Group (section) 3 is 2 digits ------ ([[:digit:]]{2})