Is there a way to report against Last Full Week's data in CA Service Desk Manager (CA SDM) using CA Business Intelligence (CABI) reports?

Document ID : KB000019481
Last Modified Date : 14/02/2018
Show Technical Document Details

Description:

Out of the box CA SDM Universe in CABI exposes filters like Last 30 days, Last 7 days etc., but there is no direct option to report against the real Last Week. This document shows a way to expose this functionality over a Web Intelligence (WEBI) Report.

Solution:

Out of the box CA SDM Universe in CABI exposes filters like Last 30 days, Last 7 days etc., but there is no direct option to report against the real Last Week. This document shows a way to expose this functionality within a Web Intelligence (WEBI) Report.

Take an example of a WEBI report, which has Open Date and Ref Num fields from the Incident Details class of the CA SDM Universe.

Click on Open Date field on the right hand pane and then add this formula:


 =If [Open Date] <= RelativeDate(LastDayOfWeek(CurrentDate()); -7)     
 And [Open Date] >= RelativeDate(LastDayOfWeek(CurrentDate());-13) 
 Then [Open Date] 

Then click on Ref Num field on the right hand pane and then add this formula:


 =If [Open Date] <= RelativeDate(LastDayOfWeek(CurrentDate()); -7)     
 And [Open Date] >= RelativeDate(LastDayOfWeek(CurrentDate());-13) 
 Then [Ref Num] 

Now it should only show the Open Date and Ref Num of data for the real Last Week.

NOTE:

The significance of -13 and -7 are explained below. You could tweak the numbers to adjust to last week or last minus 2 week or last minus 3 week etc.


 RelativeDate(LastDayOfWeek([Field1]);-13) --> Start of Day of Last week's first day     
 RelativeDate(LastDayOfWeek([Field2]);-7) --> End of Day of Last week's last day