Concatenate Date values with Presentation Variable

Question

Hi All,

I have created a dashboard prompt with 1 presentation variable date prompt(Start date) showing First day of quarter and few column prompts in a dashboard. My requirement is to show the date selected in the prompt concatenated  with end day of quarter as 1 column in   'MM/DD/YYYY' format in the report. I tried to concatenate the values using the below syntax in column format but its giving me a syntax error:

TIMESTAMPADD( SQL_TSI_DAY , DAY_OF_QUARTER( (@{pv_start_date}{date '2016-01-01'})) * -(1) + 1, (@{pv_start_date}{date '2016-01-01'}))||'-'||TIMESTAMPADD( SQL_TSI_DAY , -(1), TIMESTAMPADD( SQL_TSI_QUARTER , 1, TIMESTAMPADD( SQL_TSI_DAY , DAY_OF_QUARTER( (@{pv_start_date}{date '2016-01-01'})) * -(1) + 1, (@{pv_start_date}{date '2016-01-01'}))))

I also tried using Evaluate function also but its giving an error with presentation variables.

Can anyone please help me how can I achieve this.

Note : I am using OBIEE 12.2.1.4.0 version.

Thanks in Advance for the help!!!!

User_TYZ1T · Answer

Thanks for your reply. I have used both CAST and Evaluate functions as per your suggestion but faced the below issues:

With Evaluate function got the same error as yesterday:

Evaluate('TO_CHAR(%1 ,%2)' AS CHAR ,TIMESTAMPADD( SQL_TSI_DAY , DAY_OF_QUARTER( (@{pv_start_date}{date '2016-01-01'})) * -(1) + 1, (@{pv_start_date}{date '2016-01-01'})) ,'MM/DD/YYYY')

State: HY000. Code: 42015. [nQSError: 42015] Cannot function ship the following expression: EVALUATE.

Please have your System Administrator look at the log for more details on this error. (HY000)

With  CAST(timestampfunction1 AS CHAR(10))  I am getting format as 2016/01/01 instead of 1/1/2016.I have used the below syntax for CAST function:

cast(TIMESTAMPADD( SQL_TSI_DAY , DAY_OF_QUARTER( (@{pv_start_date}{date '2016-01-01'})) * -(1) + 1, (@{pv_start_date}{date '2016-01-01'})) as char(10))

Can you please help to resolve this issue.

Thanks in Advance for the help!!!!

Jerry Casey · Answer

You may have several issues here.  One is that you are using both the CAST and TO_CHAR functions for the same purpose.  Pick one.  You also may not have access to the EVALUATE function, depending on your system, but there is no detail in those error messages, so I don't know about that.

I would start by creating a column that only contains your TIMESTAMPADD function to make sure that is working correctly.  Then, your final column would look like one of the two versions below:

CAST(timestampfunction1 AS CHAR(10))||' - '||CAST(timestampfunction2 AS CHAR(10))

OR

EVALUATE('TO_CHAR(%1,%2)' as char,timestampfunction1,'MM/DD/YYYY')||' - '||EVALUATE('TO_CHAR(%1,%2)' as char,timestampfunction2,'MM/DD/YYYY')

User_TYZ1T · Answer

Thank you for your response.

I have used Evaluate Function but its giving me the below error:

Error Codes: OPR4ONWY:U9IM8TAC:U9IM8TAC:U9IM8TAC:U9IM8TAC:OI2DL65P

State: HY000. Code: 10058. [NQODBC] [SQL_STATE: HY000] [nQSError: 10058] A general error has occurred.

Please have your System Administrator look at the log for more details on this error. (HY000)

State: HY000. Code: 43113. [nQSError: 43113] Message returned from OBIS.

Please have your System Administrator look at the log for more details on this error. (HY000)

State: HY000. Code: 43119. [nQSError: 43119] Query Failed:

Please have your System Administrator look at the log for more details on this error. (HY000)

State: HY000. Code: 42015. [nQSError: 42015] Cannot function ship the following expression: EVALUATE.

Please have your System Administrator look at the log for more details on this error. (HY000)

I have used the below syntax for Evaluate function:

Evaluate('TO_CHAR(%1 ,%2)' AS CHAR ,CAST(TIMESTAMPADD( SQL_TSI_DAY , DAY_OF_QUARTER( (@{pv_start_date}{date '2016-01-01'})) * -(1) + 1, (@{pv_start_date}{date '2016-01-01'})) AS CHAR(10)),'MM/DD/YYYY')

Can you please help to resolve this issue.

Thanks in Advance for the help!!!!