Skip to Content
avatar image
Former Member

Issue with Filter Expression using calculated Object

Dear Experts,

I have a projection inside a calculation view , This Projection has Input Parameter $$IP_FiscalYear$$ which accept year from user. This Input parameter is propagated to semantic layer.

I want to restrict this projection for previous year data So I have created following two calculated column objects at projection level -

InputYearStartDate = '$$IP_FiscalYear$$ + '0101'

PreviousYear = leftstr(string(adddays(date("InputYearStartDate"),-1)),4)

Now I have created a Expression Filter for projection on dimension object FiscalYear with Filter condition defined as below to restrict previous year data-

"FiscalYear" = "PreviousYear"

I could not see the data in preview. (tested with input parameter value = 2016 to see previous year i.e 2015 data).

However I could see the data and above two calculated variables if I filter expression with Input parameter

value  as - "FiscalYear" = "'$$IN_FiscalYear$$' (this will show the data for same input parameter year).

Could you please guide me whats wrong here when using calculated column in Filter Expression. Please note that all the Objects here are defined as NVARCHAR.

Thanks & Regards,

Jiten

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

1 Answer

  • Best Answer
    avatar image
    Former Member
    Apr 12, 2016 at 09:54 AM

    Hi Jiten,

    1. Is your value help of input parameter $$IP_FiscalYear$$  listing down the values  at data preview?

    2. Are you creating input parameter using column FiscalYear and applying the filter on same column?

    Can you attach screenshots of your model if possible?

    Regards,

    Shireesha

    Add comment
    10|10000 characters needed characters exceeded

    • Former Member Former Member

      Thank you Shrieesha !

      You explained well for the value help at data preview. This given me clue to recheck my whole model again. Also I found that my model was using a root level reuse view which had expression filter for current Year and that was filtering data at root level itself to fetch only current year data. Hence on top node it was not displaying data for previous Year filter. I somehow missed it since my complex model was using many reuse views at many level.

      I will be more careful next time while debugging.

      I fixed it by modifying root level reuse view expression filter (of PreviousYear projection) to use calculated object PreviousYear.

      Regards,

      Jiten