Answers - Linking Prompt with Filter using Presentation Variable

Question

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

Thomas Dodds · Answer

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.

Manoj Dixit · Answer

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.