You're almost there! Please answer a few more questions for access to the Applications content. Complete registration
Interested in joining? Complete your registration by providing Areas of Interest here. Register
Get Started with Redwood for Oracle Cloud HCM   Begin Now
To ensure that questions get required attention from community members and are NOT left unanswered, it’s important for the author to indicate (by selecting “Yes” or “No” when prompted) whether the question was answered. (newly added) Please note that it is also important to respond to EACH comment your question receives. Your Yes or No response ensures an accurate status for your question.

For more information, please refer to this announcement explaining best practices for getting answers to questions.

How to Subtract 5 hours from current date in OTBI criteria

Inactive
Inactive Green Ribbon
edited Oct 29, 2018 2:52PM in Reporting and Analytics for HCM 4 comments

Answers

  • Bhaskar Konar-83539
    Bhaskar Konar-83539 Red Ribbon
    edited Jun 26, 2018 3:12PM

    Hi Michael,

    Please try the following:

    TIMESTAMPADD(SQL_TSI_HOUR, -5, CURRENT_TIMESTAMP)

    I'm attaching a report XML for your convenience.

    Please let me know if it helps.

    Kind Regards,

    Bhaskar

  • user6284475
    user6284475 Red Ribbon
    edited Jun 26, 2018 3:18PM

    Too funny, I JUST encountered this same error this morning.

    I was trying to get people who were hired 30 days ago that have not created a goal in performance.  When I did hire date < CURRENT DATE - 30, it worked.  When I did hire date <= CURRENT_DATE - 30 days, it worked.  When I did hire date = or in 30 days it bombed with the same error above.  I also am putting an agent on so I needed exactly 30 days so people don't get harassed every day.  My warped workaround that I found that works is to say:

    Person Hire Date  is between  CURRENT_DATE - 30 and CURRENT_DATE - 30

    Go figure! 

    Good luck!

  • Bhaskar Konar-83539
    Bhaskar Konar-83539 Red Ribbon
    edited Jul 2, 2018 10:42AM

    Hi Michael,

    Please find attached doc with a sample report for Effective Date & Custom Date Time.

    Can you please execute the attached XML in your environment and share few records? We might need to format the column based on data type.

    Just wanted to make sure that Effective Date is giving you the Date Time Values. As over here in our system it is not having Time Part.

    Please give me a shout in case you need more information.

    Kind Regards,

    Bhaskar

  • Bhaskar Konar-83539
    Bhaskar Konar-83539 Red Ribbon
    edited Jul 2, 2018 4:16PM

    Hi Michael,

    I was trying to give you the code behind the - 5 hours calculation and I did with Current Timestamp as a result it will come same for all the rows.

    Please find below the xml which will give you Effective Date - 5 hours. Could please let me know your requirement? I mean what data set you want to extract ?

    <saw:report xmlns:saw="com.siebel.analytics.web/report/v1.1" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance&quot; xmlns:xsd="http://www.w3.org/2001/XMLSchema&quot; xmlns:sawx="com.siebel.analytics.web/expression/v1.1" xmlVersion="201201160">   
       <saw:criteria xsi:type="saw:simpleCriteria" subjectArea="&quot;Payroll - Payroll Balances Real Time&quot;">      
          <saw:columns>         
             <saw:column xsi:type="saw:regularColumn" columnID="c1d99ce944eb678f1">            
                <saw:columnFormula>               
                   <sawx:expr xsi:type="sawx:sqlExpression">"Balance Value Details"."Effective Date"</sawx:expr></saw:columnFormula>            
                <saw:displayFormat>               
                   <saw:formatSpec suppress="suppress" wrapText="true">                  
                      <saw:dataFormat xsi:type="saw:custom" customFormat="DD/MM/YYYY HH:mm:ss"/></saw:formatSpec></saw:displayFormat>            
                <saw:columnHeading>               
                   <saw:displayFormat>                  
                      <saw:formatSpec/></saw:displayFormat></saw:columnHeading></saw:column>         
             <saw:column xsi:type="saw:regularColumn" columnID="c4d2e1105a188dd5f">            
                <saw:columnFormula>               
                   <sawx:expr xsi:type="sawx:sqlExpression">TIMESTAMPADD(SQL_TSI_HOUR, -5, cast("Balance Value Details"."Effective Date" as timestamp))</sawx:expr></saw:columnFormula>            
                <saw:displayFormat>               
                   <saw:formatSpec suppress="suppress" wrapText="true">                  
                      <saw:dataFormat xsi:type="saw:custom" customFormat="DD/MM/YYYY HH:mm:ss"/></saw:formatSpec></saw:displayFormat>            
                <saw:tableHeading>               
                   <saw:caption fmt="text">                  
                      <saw:text>Balance Value Details</saw:text></saw:caption></saw:tableHeading>            
                <saw:columnHeading>               
                   <saw:caption fmt="text">                  
                      <saw:text>Effective Date - 5 Hours</saw:text></saw:caption>               
                   <saw:displayFormat>                  
                      <saw:formatSpec/></saw:displayFormat></saw:columnHeading></saw:column></saw:columns></saw:criteria>   
       <saw:views currentView="0">      
          <saw:view xsi:type="saw:compoundView" name="compoundView!1">         
             <saw:cvTable>            
                <saw:cvRow>               
                   <saw:cvCell viewName="titleView!1"/></saw:cvRow>            
                <saw:cvRow>               
                   <saw:cvCell viewName="tableView!1"/></saw:cvRow></saw:cvTable></saw:view>      
          <saw:view xsi:type="saw:titleView" name="titleView!1"/>      
          <saw:view xsi:type="saw:tableView" name="tableView!1" scrollingEnabled="true">         
             <saw:edges>            
                <saw:edge axis="page" showColumnHeader="true"/>            
                <saw:edge axis="section"/>            
                <saw:edge axis="row" showColumnHeader="true">               
                   <saw:edgeLayers>                  
                      <saw:edgeLayer type="column" columnID="c1d99ce944eb678f1"/>                  
                      <saw:edgeLayer type="column" columnID="c4d2e1105a188dd5f"/></saw:edgeLayers></saw:edge>            
                <saw:edge axis="column" showColumnHeader="rollover"/></saw:edges></saw:view></saw:views></saw:report>

     

    Kind Regards,

    Bhaskar