Date parameter not working to send Bursting email

Question

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.

DharaniR · Answer

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

Abhilash K - Deloitte USI · Answer

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 '

Hello, This is a sample notification.

' parameter5, -- Message body 'true' parameter6 -- Attachment value ('true' or 'false') FROM dual