The procedure used to create the simple calculated Dimension earlier can be extended to create a “range” Dimension to allow the display of counts for ranges of dates, for example, in bands, for example, Today, ‘+ 1 Day’, ‘+ 2 Days’, ‘+ 3 Days’, ‘+ 4 Days’, ‘+ 5 Days’, ‘+ 6 Days’, ‘1 – 2 Weeks’, ‘2 – 3 Weeks’, ‘3 – 4 Weeks’, ‘4 – 5 Weeks’, ‘5 – 120 Weeks’, ‘120 – 240 Weeks’, ‘>= 240 Weeks’.
This query calculation was tested with
select
case
when (days(current date) – days(TIMEINTERVAL))*24 < 24 THEN ‘Today’
when (days(current date) – days(TIMEINTERVAL))*24 Between 24 and 47 THEN ‘+ 1 Day’
when (days(current date) – days(TIMEINTERVAL))*24 Between 48 and 71 THEN ‘+ 2 Days’
when (days(current date) – days(TIMEINTERVAL))*24 Between 72 and 95 THEN ‘+ 3 Days’
when (days(current date) – days(TIMEINTERVAL))*24 Between 96 and 119 THEN ‘+ 4 Days’
when (days(current date) – days(TIMEINTERVAL))*24 Between 120 and 143 THEN ‘+ 5 Days’
when (days(current date) – days(TIMEINTERVAL))*24 Between 144 and 167 THEN ‘+ 6 Days’
when (days(current date) – days(TIMEINTERVAL))*24 Between 168 and 335 THEN ‘1 – 2 Weeks’
when (days(current date) – days(TIMEINTERVAL))*24 Between 336 and 503 THEN ‘2 – 3 Weeks’
when (days(current date) – days(TIMEINTERVAL))*24 Between 672 and 839 THEN ‘4 – 5 Weeks’
when (days(current date) – days(TIMEINTERVAL))*24 Between 840 and 20160 THEN ‘5 – 120 Weeks’
when (days(current date) – days(TIMEINTERVAL))*24 Between 20161 and 40319 THEN ‘120 – 240 Weeks’
when (days(current date) – days(TIMEINTERVAL))*24 > 40319 THEN ‘>= 240 Weeks’
else ‘Not In tested Range’
end as DaysIn,
DMCASE_KEY
FROM CAUSER.F_DMCASELOAD;
Figure 5-10The SQL for the Case Aging ranges is tested in Linux IBM Data Studio 4.1.3
And the Create View statement used to create the View, CAUSER.DMAgeing_Case:
CREATE VIEW CAUSER.DMAgeing_Case AS
select
case
when (days(current date) – days(TIMEINTERVAL))*24 < 24 THEN ‘Today’
when (days(current date) – days(TIMEINTERVAL))*24 Between 24 and 47 THEN ‘+ 1 Day’
when (days(current date) – days(TIMEINTERVAL))*24 Between 48 and 71 THEN ‘+ 2 Days’
when (days(current date) – days(TIMEINTERVAL))*24 Between 72 and 95 THEN ‘+ 3 Days’
when (days(current date) – days(TIMEINTERVAL))*24 Between 96 and 119 THEN ‘+ 4 Days’
when (days(current date) – days(TIMEINTERVAL))*24 Between 120 and 143 THEN ‘+ 5 Days’
when (days(current date) – days(TIMEINTERVAL))*24 Between 144 and 167 THEN ‘+ 6 Days’
when (days(current date) – days(TIMEINTERVAL))*24 Between 168 and 335 THEN ‘1 – 2 Weeks’
when (days(current date) – days(TIMEINTERVAL))*24 Between 336 and 503 THEN ‘2 – 3 Weeks’
when (days(current date) – days(TIMEINTERVAL))*24 Between 672 and 839 THEN ‘4 – 5 Weeks’
when (days(current date) – days(TIMEINTERVAL))*24 Between 840 and 20160 THEN ‘5 – 120 Weeks’
when (days(current date) – days(TIMEINTERVAL))*24 Between 20161 and 40319 THEN ‘120 – 240 Weeks’
when (days(current date) – days(TIMEINTERVAL))*24 > 40319 THEN ‘>= 240 Weeks’
else ‘Not In tested Range’
end as DaysIn,
DMCASE_KEY
FROM CAUSER.F_DMCASELOAD;
Figure 5-11The Create View statement is run for the CAUSER.DMAgeing_Case view
The next step after creating the preceding views is to load the Windows 10, IBM Cognos Analytics application, to set up a Datasource from the Linux CASTORE1 database and then create the Dashboards with the graphics used to display the OLAP database tables and our newly created views.
To access the IBM Cognos Analytics web application, the URL link is used:
http://DESKTOP-B3JB93R:9300/bi/v1/disp (http://servername:9300/bi/v1/disp)
Figure 5-12The Anonymous Logon option is set for the Cognos web application
This returns an error, since there is no LDAP server connection configured for validation, but for a test environment, we can set the Anonymous Logon tick box shown in Figure 5-12.
The URL (http://DESKTOP-B3JB93R:9300/bi/v1/disp) then launches the first page of the IBM Cognos Analytics 11.2 web application for processing Dashboard reports as shown in Figure 5-13.
Figure 5-13The IBM Cognos Analytics 11.2 web application main screen is displayed
The top-left “hamburger” icon is clicked, and the Manage menu option is selected as shown in Figure 5-14.
Leave a Reply