General OBIEE

Get Involved. Join the Conversation.

Topic

    Linda Stelling
    Prefix - set effective date using SQL Expresssion
    Topic posted May 4, 2018 by Linda StellingSilver Medal: 2,000+ Points, tagged Analyses, OBI Answers, OBIEE Answers, OTBI, Reports, Scheduling or Agents 
    1443 Views, 2 Comments
    Title:
    Prefix - set effective date using SQL Expresssion
    Content:

    Hello - I'm hoping someone can help me with this please.

    I have an OTBI analysis that I want to schedule on the first of each month that will show changes in the previous month. One of the 'changes' could be that the record has been ended but because the because the subject area is a Real Time one (Payroll - Element Entries Real Time) the record is no longer current when the report is being run so it will not report the change. 

    Therefore, I am wondering if I could include the SQL expression below (to get to the 1st of the previous month) in the prefix to set the effective date to be the 'first of the previous month'. 

    TIMESTAMPADD(SQL_TSI_MONTH, -1, TIMESTAMPADD(SQL_TSI_DAY, DAYOFMONTH(CURRENT_DATE) * -(1) + 1, CURRENT_DATE))

    I hope that makes sense and if someone can help me do this I'd be very grateful. I tried several different ways to add this to the Prefix with no success so I think it might be a syntax issue, or maybe it's just not possible to do this?

    Many thanks

    Linda

     

    Comment

     

    • Shakher Sharma

      I am not sure if I understood your question correctly. I believe you want to pick all records modified since 1st of last month. Is that correct?

      You should be able to do the same.

      Here is the filter I applied:

      "- Header Details"."Journal Creation Date" >= TIMESTAMPADD(SQL_TSI_MONTH, -1, TIMESTAMPADD( SQL_TSI_DAY , DAYOFMONTH( CURRENT_DATE) * -(1) + 1, CURRENT_DATE))

      Here is the code of analysis if you would like to replicate this test case:

      SET VARIABLE PREFERRED_CURRENCY='User Preferred Currency 1';SELECT
         0 s_0,
         "General Ledger - Journals Real Time"."- Header Details"."Journal Creation Date" s_1,
         COUNT("General Ledger - Journals Real Time"."- Header Details"."Journal Name") s_2
      FROM "General Ledger - Journals Real Time"
      WHERE
      ("- Header Details"."Journal Creation Date" >= TIMESTAMPADD(SQL_TSI_MONTH, -1, TIMESTAMPADD( SQL_TSI_DAY , DAYOFMONTH( CURRENT_DATE) * -(1) + 1, CURRENT_DATE)))
      ORDER BY 1, 2 ASC NULLS LAST
      FETCH FIRST 75001 ROWS ONLY

       

       

      Attach is the screenshot of the filter

    • Rick Brobbel

      Or you can simply say:

      MONTH("Header Details"."Journal Creation Date") = MONTH(CURRENT_DATE) - 1

      Which won't work for January, so in that case you need:

      CASE WHEN MONTH(CURRENT_DATE) = 1 THEN MONTH("Header Details"."Journal Creation Date") = 12 AND YEAR("Header Details"."Journal Creation Date") = YEAR(CURRENT_DATE) - 1 ELSE MONTH("Header Details"."Journal Creation Date") = MONTH(CURRENT_DATE) - 1 AND YEAR("Header Details"."Journal Creation Date") = YEAR(CURRENT_DATE) END

      Good luck