Discussion Forumpublic

Reporting and Analysis

 
This is a public Forum  publicRSS

Topic

    Ashwitha S
    Based on date, value should display in a Report column
    Topic posted October 12, 2017 by Ashwitha SRegular, last edited October 12, 2017 
    59 Views, 2 Comments
    Title:
    Based on date, value should display in a Report column
    Content:

    Hi All,

    There are two columns in a Report. One is Date column which display date in DD-MM-YYY HH:MM AM/PM format and one is Items column (will display item names like Item1, Item2, Item3 and Item4).

    The third column should have values based on the date.

    • Items=Item1 and If the Day of the week is a Week Day, then value should be V1.
    • Items=Item2 and If the Day of the week is a Week Day then value should be V2, also If the Day of the week is a Saturday then value should be V3. 
    • Items=Item3 and If the Day of the week is a Sunday + Holiday then value should be V4
    • Items=Item4 and If the Day of the week is a Week Day, If the Day of the week is a Saturday, If the Day of the week is a Sunday + Holiday then value should be V1.

     

    Date

     

    Items

    If the Day of the week is a Week Day

    If the Day of the week is a Saturday

    If the Day of the week is a Sunday + Holiday

    DD-MM-YYY HH:MM AM

    Item1

    V1

    		    

    DD-MM-YYY HH:MM AM

    Item2

    V2

    V3

    		  

    DD-MM-YYY HH:MM AM

    Item3

    		    

    V4

    DD-MM-YYY HH:MM AM

    Item4

    V1

    V1

    V1

     

    Can anyone assist?

    Thanks,

    Ashwitha

    Version:
    Oracle Service Cloud February 2017

    Answer

     

    • Sasi Gudimetta

      Hi Ashwitha,

      This would be a very tedious task since all the values must be hard-coded in the column syntax itself using nested if conditions. A sample expression you can see below. That entire expression is the syntax in one column to accommodate all the scenario that you presented above. Here %w checks for the day of the week

      if(date_format(sysdate(),'%w') IN ('1','2','3','4','5'),if(incidents.item IN ('Item1','Item4'),'V1',if(incidents.item = 'Item2','V2')),if(date_format(sysdate(),'%w') = '6',if(incidents.item = 'Item2','V3',if(incidents.item = 'Item4','V1')),if(date_format(sysdate(),'%w') = '0',if(incidents.item = 'Item3','V4',if(incidents.item = 'Item4','V1')))))

      If you could provide the use case then maybe we could look for an alternate solution approach by maybe splitting the data into multiple columns or building a drill down to segregate data based on day and item.

      Regards,

      Sasi K Rao.

    • Simon Kilgarriff

      As Sasi states above creating this report isn't that easy.

      Is it worth considering if it might be worth using a custom field (or several) and populate the value/s either manually or via business rules/Custom Process Model and that might the report easier to build?   For example, there could be 3 custom fields: Weekday,  Saturday,   Sunday + Hol that get set with a value.   Then the report would be much easier to build.

      Sometimes I've found that using additional fields and getting them populated somehow can lead to more simple reports.  The trick is to 'how' to get them populated.