Search code examples
oracle11ganalyticspivot-tableobiee

Pivot Table: Hide rows where two columns are not null

I have a column called Test Name. Within this column, I have several attributes, of which I am only looking at two (we will call the attributes Red and Blue). This is how the basic table looks:

Basic Table

Now I converted this table into a pivot table. Note that Test Result is not a measure, so I applied a random aggregation rule (in this case Max) to force OBIEE to show data in the measures field. This is how the pivot table looks:

Pivot Table

What I want to do is only keep rows in the pivot table where both Blue and Red have some sort of value in it. In this example I would only keep Sample Number 3304 and 3305.

Remember that Blue and Red are only attributes of the Test Name column, so I can't simply use a filter saying where Test Name or Test Result is not null. Also, these are not metrics, so building custom formulas is quite tricky.

Solution

  • Since I knew my natural key (in this case sample number and org number together), I created a subreport that returned sample numbers and org numbers concatenated together (so they were 100% unique) that had a value for the RED attribute (since the BLUE attribute always had a value, and always will).

    In the main report, I simply filtered my sample number org number combination by the sub report, then hid that column and only showed sample number. This seems to work well.