Oracle Analytics Forum

Home› Oracle Analytics and AI› Oracle Analytics and AI Forums› Oracle Analytics Forums› Oracle Analytics Forum

Categories

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

Answers - Linking Prompt with Filter using Presentation Variable

Received Response
1
Views
2
Comments
Adz10
Adz10 Rank 2 - Community Beginner
edited Aug 13, 2024 6:58PM in Oracle Analytics Forum

Hi,

I have two columns called "FISCAL YEAR" and "FISCAL PERIOD NUMBER", I am trying to connect these columns to dashboard prompt.

So when end user selects any date it automatically extract year and month from the prompt and use for FISCAL YEAR and FISCAL PERIOD NUMBER.

I tried applying filter condition so Month and Year can be extracted by casting presentation variable via prompt.  (Filter converted into sql)

CAST("Time"."Fiscal Year" as char) = (CAST(YEAR((@{PVAR_PERIOD_START_DATE}{'01/01/2015'})) as Char))

CAST("Time"."Fiscal Period Number" as char) = (CAST(MONTH((@{PVAR_PERIOD_END_DATE}{'30/06/2015'}))as char))

I get this error -

Error Codes: OPR4ONWY:U9IM8TAC:OI2DL65P

State: HY000. Code: 10058. [NQODBC] [SQL_STATE: HY000] [nQSError: 10058] A general error has occurred. [nQSError: 43113] Message returned from OBIS. [nQSError: 43119] Query Failed: [nQSError: 59001] CalendarExtract operation is not permitted on LONG VARCHAR operand(s). (HY000)
Not able to figure out a way around this can someone help?
Thanks

Capture.PNG

Answers

  • Manoj Dixit
    Manoj Dixit Rank 6 - Analytics & AI Lead

    Some hint might exist here. How to return current month

    There seems to be an issue with data types around database column, RPD physical column and conversion. Why would one define a LONG datatype for a date value?

    Regards,

    Manoj.

  • Thomas Dodds
    Thomas Dodds Rank 8 - Analytics & AI Strategist

    You need a properly formed Time (Date) dimension that has the Year and the Period on the individual date row in the database table ... it's the most efficient and easiest to implement.   Use drives physical design.