Oracle Analytics Publisher

Home› Oracle Analytics› Oracle Analytics Forums› Oracle Analytics Cloud and Server› Oracle Analytics Publisher

Categories

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

Date parameter not working to send Bursting email

Received Response
161
Views
2
Comments
DharaniR
DharaniR Rank 1 - Community Starter
in Oracle Analytics Publisher

Hello All

I am developing a parameterized report which has bursting logic to split and send the file to different emails for each project. The Bursting query is working individually when used in Dataset but when same query is used as bursting query, reqport requests completes but email outputs are not generated. I am using trunc(TO_TIMESTAMP(:p_as_of_date)) for puling the data in the main DS but same is not working in Bursting. When checking log no errors found and XML has data as well. Please advise how to convert the data formatting in When clause for bursting to work? The parameter defined is a Date parameter DT.

SELECT

          CASE

            WHEN (:p_as_of_date)--to_date('2023-10-10','YYYY-MM-DD') --to_date(trunc(:p_as_of_date),'YYYY-MM-DD') --to_char(:p_as_of_date) 

            IS NULL 

            THEN

              TRUNC(sysdate)

            ELSE

              to_date('2023-10-10','YYYY-MM-DD') --trunc(TO_TIMESTAMP(:p_as_of_date))

          END run_date

        FROM

          dual;

Also based on case condition I have a separate DM to add additional text on the layout, I have performed the field mapping to include the data set block but in the bursting report email, the additional text is not getting displayed.

Tagged:

Answers

  • Abhilash K - Deloitte USI
    Abhilash K - Deloitte USI Rank 5 - Community Champion

    You can try may be the timezone conversion also as BI server by default uses UTC timezone.

    We can use like SELECT TO_CHAR (

                  FROM_TZ (

                    TO_TIMESTAMP (NVL ( :p_run_date, SYSDATE)),

                    DBTIMEZONE)

                    AT TIME ZONE 'America/Los_Angeles',

                  'yyyy-MM-dd') 

    to get PST time for example.

    Below is one of sample email bursting query for any reference if it helps

    SELECT

        '1'

          KEY,

        'Output'

          TEMPLATE,

        'XLSX'

          OUTPUT_FORMAT,

        'EMAIL'

          DEL_CHANNEL,

        'TEST NOTIFICATION'

          OUTPUT_NAME,

        'toaddress@abc.com'

          AS parameter1,                   -- Email address

        null

          AS parameter2,

        'fromaddress@abc.com'

          AS parameter3,                       -- From

         ' Notification of Asset Transactions for '

        || TO_CHAR (SYSDATE, 'MM/DD/YYYY')

          parameter4,                       -- Subject

        '<html>

    <p style="color:blue">Hello, 

    <em>This is a sample notification.</em></p>

    </html>'

          parameter5,                     -- Message body

        'true'

          parameter6         -- Attachment value ('true' or 'false')

     FROM dual

  • DharaniR
    DharaniR Rank 1 - Community Starter

    Hi Abhilash

    I tried the suggested solution but still the parameter is considered as Null in the bursting code. The below query returns no records when parameter is passed. Please advise any solution.

     SELECT

      '1' AS "KEY",

      'Test1'              template,

      'en-00'              locale,

      'html'              output_format,

      'EMAIL'              del_channel,

      'Test.html'            output_name,

      '<Email>'    parameter1,

      'no-reply@oraclecloud.com'    parameter3,

      'Reply-To'            parameter7

    FROM

      dual

    WHERE

      to_char(from_tz(TO_TIMESTAMP(nvl(:p_as_of_date, sysdate)),

              dbtimezone) AT TIME ZONE 'America/Los_Angeles',

          'yyyy-MM-dd') IS NOT NULL