sqlreportreportingobieeoracle-bi

OBIEE - Filtering Results based on a "historic" table of values

not really sure how/where to ask this so hoping this is the right place.

I have an OBIEE report that combines two tables; a current status and a 'historic' table. I've created an example of my data below:

[Results of report]

1

What I'm looking to do is to filter out any serial numbers that have had the status of "appeal" on the claim history dimension. In my image example, it would filter out serial number 5678 completely as at one point it went through the status of "appeal".

However, just a quick filter for the status of "appeal" just removes the one line from the report - leaving 5678 on the report but only with the two other statuses it went through (created and refunded).

Is there a way with a traditional or SQL filter I can remove a serial number completely if it matches my filter criteria?

Thanks,

M.

Solution

  • You're not saying how you "combine" these data sets but supposing you use SQL queries because those are BI Publisher "reports" as you indicate:

    It's simply 2 SQL queries and if you filter the first - historic - one to not include "appealed" rows and then do an INNER join, all related IDs from the second data set will also be dropped.

    If you do a full outer then obviously that won't work.