Cognos Training
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: https://blink.ucsd.edu/technology/bi/tools/cognos/index.html#Training
Cognos Dashboards or Reports:
Example Codes
| What | Code |
|---|---|
| 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)) |