Oracle Business Intelligence Applications Forum

Home› Oracle Analytics and AI› Oracle Analytics and AI Forums› More Solutions› Oracle Business Intelligence Applications Forum

Categories

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

Using filter in column formula does not work

Received Response
11
Views
2
Comments
3416339
3416339 Rank 1 - Community Starter
in Oracle Business Intelligence Applications Forum

Hello,

I am working on OBI 12.2.1.2.0. I am trying to filter on column formula like,

FILTER("Fact Product"."Total of Policy" USING ("Dim Agency"."Agency Name" = 'Asia Region')) but it is not working.

For example:

pastedImage_1.png

These are two columns return same value but first column should be 12,second column is 56(correct). Also when i am looking physical sql, i can not see any filter.

Please, can you help me ?

Thanks

Answers

  • 3586388
    3586388 Rank 3 - Community Apprentice

    Hi,

    You can use case statement to fitter the condition. Check the value for agency name = Asia Region

    CASE  WHEN  "Dim Agency"."Agency Name" = 'Asia Region'  THEN "Fact Product"."Total of Policy" END

  • Souvik Sarkar
    Souvik Sarkar Rank 3 - Community Apprentice

    Hi,

    Check the level of data in which these figures are displayed. If you are expecting the number at a lower level, you need to group the data by that level. Define the "By level" at the end of the FILTER to achieve the desired result. OBIEE needs to understand the level of aggregation at which the FILTER is applied. What I mean to say is use the filter as FILTER("Fact Product"."Total of Policy" USING ("Dim Agency"."Agency Name" = 'Asia Region') By "Dim Column"). The "Dim Column" should be the level of the Dimension at which the data is to be aggregated.