I have four saved filters I want to apply with "or" conditions as shown below.
I am drilling from one report on an OBIEE dashboard page, to another dashboard page with a report that goes the next level down in a hierarchy. Both reports are identical as far as the four "or" filters. The only difference in filters between the reports is the "is prompted" field.
These are what's in the four filters:
Filter1: (SUPLR_ABBRV in ('BARDAC', 'BD') and ( PROD_CTGRY_LVL2_CD in ('C034', 'C050', 'C119') or PROD_GRP_LVL3_CD in ('G0544')))
Filter2: (SUPLR_ABBRV in ('HALYAR','MEDACT') and PROD_CTGRY_LVL2_CD in ('C070', 'C071'))
Filter3: (SUPLR_ABBRV in ('ETHCON') and PROD_SUB_CTGRY_LVL4_CD in ('S01979'))
Filter4: (SUPLR_ABBRV in ('USSURG', 'USSUT') and PROD_CTGRY_LVL2_CD in ('C024', 'C080'))
There are two separate problems. First of all, OBI is converting them into something entirely different in both reports. I am running them acting as someone, which automatically applies the row level filtering.
This is what the logic of the four should be:
(SUPLR_ABBRV in ('BARDAC', 'BD') and ( PROD_CTGRY_LVL2_CD in ('C034', 'C050', 'C119')
or PROD_GRP_LVL3_CD in ('G0544')))
or (SUPLR_ABBRV in ('USSURG', 'USSUT') and PROD_CTGRY_LVL2_CD in ('C024', 'C080'))
or (SUPLR_ABBRV in ('ETHCON') and PROD_SUB_CTGRY_LVL4_CD in ('S01979'))
or (SUPLR_ABBRV in ('HALYAR','MEDACT') and PROD_CTGRY_LVL2_CD in ('C070', 'C071'))
In the first report it's doing this according to the logs:
(SUPLR_ABBRV in ('BARDAC', 'BD', 'ETHCON'))
and (SUPLR_ABBRV in ('BARDAC', 'BD') or PROD_SUB_CTGRY_LVL4_CD in ('S01979'))
and (SUPLR_ABBRV in ('ETHCON') or PROD_CTGRY_LVL2_CD in ('C034', 'C050', 'C119'))
and (PROD_CTGRY_LVL2_CD in ('C034', 'C050', 'C119') or PROD_SUB_CTGRY_LVL4_CD in ('S01979'))
In the second report it's doing something different both from the saved filters and the first report:
(SUPLR_ABBRV in ('USSURG', 'USSUT') or PROD_CTGRY_LVL2_CD in ('C024', 'C080') or PROD_GRP_LVL3_CD in ('G0544'))
and (PROD_CTGRY_LVL2_CD in ('C024', 'C080') or PROD_GRP_LVL3_CD in ('G0544') or PROD_SUB_CTGRY_LVL4_CD in ('S01979'))
and (SUPLR_ABBRV in ('USSURG', 'BARDAC', 'BD', 'USSUT'))
and (SUPLR_ABBRV in ('BARDAC', 'BD') or PROD_CTGRY_LVL2_CD in ('C024', 'C080') or PROD_SUB_CTGRY_LVL4_CD in ('S01979'))
This is totally perplexing. SUPLR_ABBRV in ('USSURG', 'USSUT') is missing in the first report, and SUPLR_ABBRV in ('ETHCON') is not in the second report. SUPLR_ABBRV in ('HALYAR','MEDACT') is in neither result. The "and" and "or" conditions make no sense in either case.
I initially had the four filters in a single filter. I thought breaking it up might fix it.
When using the default hierarchy drilldown in the first report instead of drilling to the dashboard page, the problem of the filter being applied differently doesn't occur.
