Oracle Transactional Business Intelligence

Home› Oracle Analytics› Oracle Analytics Forums› More Solutions› Oracle Transactional Business Intelligence

Categories

Welcome to the Oracle Analytics Community: Please complete your User Profile and upload your Profile Picture

OTBI Prompt - Last Day of the Previous Month 23:59:59

Received Response
119
Views
6
Comments
David Oldfield
David Oldfield Rank 2 - Community Beginner
in Oracle Transactional Business Intelligence

Hi all,

Hope you are keeping well.

So far, within a Prompt we are using the following code and this does indeed bring back the Last Day of the Previous Month successfully:

SELECT TIMESTAMPADD( SQL_TSI_DAY , -(1), TIMESTAMPADD( SQL_TSI_DAY , DAYOFMONTH( CURRENT_DATE) * -(1) + 1, CURRENT_DATE))
FROM "Workforce Management - Historical Processed Time Cards Real Time"

Can this be extended so this also includes the time of 23:59:59 (Currently, this remains as 12:00:00).

Kind regards,
David

Tagged:

Answers

  • Rajasekhar Bandaru-Oracle
    Rajasekhar Bandaru-Oracle Rank 5 - Community Champion

    Hi David

    Please review SQL Query generated by OTBI analysis using NQSQuery.log.

    Fusion Applications: How to Get nqquery Using Manage Session (Doc ID 2225909.1)

    Using the BI Publisher data model create a dataset using SQL Query and check the data available in the underlying database tables.

  • David Oldfield
    David Oldfield Rank 2 - Community Beginner

    Many thanks for the appreciated response Rajasekhar - I have attempted the same by changing the Prompt Parameters before running the report to 23:59:59 :

    image.png

    In the Prompt Statements it does not seem to be showing me the aspect where I change the time element to 23:59:59:

    image.png

    SET VARIABLE OBIS_REFRESH_CACHE=1,QUERY_SRC_CD='DashboardPrompt';SELECT TIMESTAMPADD(SQL_TSI_MONTH, -1, TIMESTAMPADD( SQL_TSI_DAY , DAYOFMONTH( CURRENT_DATE) * -(1) + 1, CURRENT_DATE))
    FROM "Workforce Management - Historical Processed Time Cards Real Time"

    SET VARIABLE OBIS_REFRESH_CACHE=1,QUERY_SRC_CD='DashboardPrompt';SELECT
    TIMESTAMPADD( SQL_TSI_DAY , DAYOFMONTH( CURRENT_DATE) * -(1) + 1, CURRENT_DATE)
    FROM "Workforce Management - Historical Processed Time Cards Real Time"

    SET VARIABLE QUERY_SRC_CD='DashboardPrompt';SELECT "Location"."Worker Location Name" saw_0 FROM "Workforce Management - Historical Processed Time Cards Real Time" ORDER BY saw_0
    FETCH FIRST 65001 ROWS ONLY

    SET VARIABLE QUERY_SRC_CD='DashboardPrompt';SELECT "Business Unit"."Business Unit Name" saw_0 , DESCRIPTOR_IDOF("Workforce Management - Historical Processed Time Cards Real Time"."Business Unit"."Business Unit Name") saw_1 FROM "Workforce Management - Historical Processed Time Cards Real Time" ORDER BY saw_0
    FETCH FIRST 65001 ROWS ONLY

    SET VARIABLE QUERY_SRC_CD='DashboardPrompt';SELECT "Organization Hierarchy"."Level 03 Organization Name" saw_0 FROM "Workforce Management - Historical Processed Time Cards Real Time" ORDER BY saw_0
    FETCH FIRST 65001 ROWS ONLY

    Kind regards,
    David

  • Rajasekhar Bandaru-Oracle
    Rajasekhar Bandaru-Oracle Rank 5 - Community Champion

    Hi David

    Glad to know the issue is resolved.

    Thanks & Regards

    Rajasekhar

  • David Oldfield
    David Oldfield Rank 2 - Community Beginner

    Hi Rajasekhar,

    Apologies, I have been unable to get the updated logic in order to add the time element of 23:59:59.

    Kind regards,
    David

  • Rajasekhar Bandaru-Oracle
    Rajasekhar Bandaru-Oracle Rank 5 - Community Champion

    Hi David

    From NQSQuery.log, Please review the physical Query generated for the Prompt and review the logic used.

  • David Oldfield
    David Oldfield Rank 2 - Community Beginner

    Hi Rajasekhar,

    I am unsure if I am doing something incorrectly here - I attempted to change the time element on both of the prompts to 23:59:59

    image.png

    And below is the output incl. the logs output:

    image.png

    Logs Output:

    image.png
    image.png

    I don't see where its attempting to include the time element of 23:59:59 ?

    Kind regards,
    David