3 Replies Latest reply on Nov 23, 2012 7:18 PM by Vijay

    Use presentation variable in date filter


      I have a Date presentation variable pvEffDt whose value is set in a dashboard prompt. How do I access this variable in a Date between filter? I actually need data for chosen date's previous month (weird, why don't I choose the previous month itself. yeah true, but I want to try something else).

      I have tried the following but to no avail - CAST (TIMESTAMPADD(SQL_TSI_MONTH, -1, cast('@{pvEffDt}[MM/DD/YYYY]' as TIMESTAMP) as DATE)

      I get the following error -

      Odbc driver returned an error (SQLExecDirectW).
      State: HY000. Code: 10058. [NQODBC] [SQL_STATE: HY000] [nQSError: 10058] A general error has occurred. [nQSError: 43113] Message returned from OBIS. [nQSError: 27002] Near <(>: Syntax error [nQSError: 26012] . (HY000)

      SQL Issued: {call NQSGetLevelDrillability('SELECT "AFM"."GRP_ID" saw_0, "HIER_DEF"."HIER_DSC" saw_1, "HIER_DIM"."CD_DSC_LV1" saw_2, "AFM"."HIER_ID" saw_3, "AFM"."ATTR_REF_DIM_KEY" saw_4, "AFM"."RPTD_CURCY_CD" saw_5, "AFM"."BMARK_ID" saw_6, "AFM"."WT_TOT_ADJ_ACCT" saw_7, "AFM"."WT_TOT_ADJ_BMARK" saw_8, "AFM"."PCT_CONTRIB_BASE_ACCT" saw_9, "AFM"."PCT_CONTRIB_LOCL_ACCT" saw_10, "AFM"."PCT_CONTRIB_BASE_BMARK" saw_11, "AFM"."PCT_CONTRIB_LOCL_BMARK" saw_12, "AFM"."SEC_SEL_BASE" saw_13, "AFM"."SEC_SEL_LOCL" saw_14, "AFM"."MKT_ALLOC_BASE" saw_15, "AFM"."MKT_ALLOC_LOCL" saw_16, "AFM"."INTERACTION_BASE" saw_17, "AFM"."INTERACTION_LOCL" saw_18, "AFM"."CURRENCY_EFFECT" saw_19, "AFM"."TOT_EFFECTS_BASE" saw_20, "AFM"."TOT_EFFECTS_LOCL" saw_21, "AFM"."SEC_SEL_BASE" - "AFM"."SEC_SEL_LOCL" saw_22, "AFM"."MKT_ALLOC_BASE" - "AFM"."MKT_ALLOC_LOCL" saw_23, "AFM"."INTERACTION_BASE" - "AFM"."INTERACTION_LOCL" saw_24, "AFM"."TOT_EFFECTS_BASE" - "AFM"."TOT_EFFECTS_LOCL" saw_25, "AFM"."EFF_DT" saw_26 FROM "Perf Attr" WHERE ("HIER_DIM"."CD_DSC_LV1" <> ''Total'') AND ("AFM"."GRP_ID" = ''3699'') AND ("AFM"."ATTR_REF_DIM_KEY" = 2) AND ("AFM"."EFF_DT" = ADD_MONTHS(DATE ''10/31/2012'', -1))')}