Skip to end of metadata
Go to start of metadata

You are viewing an old version of this page. View the current version.

Compare with Current View Page History

« Previous Version 10 Next »

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

WhatCode
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 Rangenumtodsinterval(if ([End Date] is null) then ([Open Date]) else ([End Date] - [Open Date]), 'day')
Format Datenumtodsinterval([item]), 'interval type')
Format DateDateDiff()
Format HH:MM timeto_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"
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 Weekbetween _add_days(current_date, -7) and _add_days(current_date, -1)
Last Monthbetween _first_of_month(_add_months(current_date, -1)) and _last_of_month(_add_months(current_date, -1))
Current Year Filterextract (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))"
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 wordsubstr([item],1,1)
Returns 1st wordsubstr(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 intervalcast(cast([Total Avail Time] as interval second) as interval hour to second)
Number to time intervalnumtodsinterval([x])
Zero Timecast(0, time)
Day from time intervalextract(day, [x])
Display last 2 yearstail ([Year], 2)
Change 4 to 04substring (([x]+100), 2, 2)
Convert to numberto_num([x])
html open in new tab<a href="http://myURL" target="_blank">text</a>
replaces 'test' with nullreplace([X], 'test')
Convert number to stringto_char([x])
Approvals"if ([Task Approval] = 'rejected') then ('Rejected') else if ([Approval] <> 'rejected' AND [Request Item Stage] = 'Request Cancelled') then ('Cancelled') else ('Approved')"
Month Formatto_char(date_trunc('Month',[Ticket Received Date]),'MON-YYYY')
Month Formatto_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 Datecast( '1970-01-01', date)
Field converted from seconds to time intervalnvl(field,0) * cast ('000 00:00:01.000', interval day to second)
n is number of digits - p is number of decimalscast(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 itemsTranslate([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 Datecast(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 rowmaximum (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 valueif (instr([FIELD], '<BR>') = 0) then ([FIELD]) else (substr([FIELD], 1, instr([FIELD], '<BR>')-1))
Field converted from seconds to time intervalnvl([FIELD], 0) * cast('000 00:00:01.000', interval day to second)
Field converted from seconds to time intervalcoalesce([FIELD], 0) * cast('000 00:00:01.000', interval day to second)
time interval between two timestampsselect extract(epoch from '2014-08-05 08:00:28'::timestamp - '2014-08-04 08:00:23'::timestamp)
time interval between two timestampsage([time1], [time2])
remove last 5 characters of stringsubstring([Term], 1, (char_length ([Term])-5))
  • No labels