Filtering in master-detail report. Not working with UNION !!!

Question

Hello!

So I have a master report with an action link to use a BI content for one of the columns (let's say PRODUCT), which uses a formula with CASE statement.

And I have another column called CATEGORY. And for that column, the detail dashboard has a "prompted" filter.

So I click on a PRODUCT value, and it brings me to the detail dashboard where the CATEGORY prompt is already set with the corresponding value from the master report, and the report is filtering this CATEGORY accordingly. So far so good.

Now I add a union on the master report. The second join is using a slightly different formula for PRODUCT column, however, CATEGORY column in the second join remains exactly the same.

Once the union is added, the CATEGORY value is not getting passed to the detail report. The detail prompt is not being set and the detail report is not filtering by CATEGORY.

What am i missing? Any ideas?

Thanks!

Dimitry F. · Answer

I see. Thank you, Gianni. I might try the building URl manually and see.

Gianni Ceresa · Answer

Did you change the order of the columns, moving "product" to the left of "category" after adding the UNION ? This could maybe also explain things ...

Still the thing is that your "Category" on screen isn't "Category Dimension"."Category" but the merge of 2 columns named like that. So the final name of that merged column could be ... anything to be fair. That's the annoying thing with UNION, names aren't fixed anymore as they are the result of an operation on top of basic columns. OBIEE seem to be a bit confused as sometime it reference the resulting columns of an UNION with the full name of the column of the first subquery of the union. In your case it would still be "Category Dimension"."Category" on screen in various places, but the internal references are different. That's why it's a lot of 'guessing' and trying by excluding any other possible cause

Dimitry F. · Answer

Thank you, Gianni. Very useful info. I could definitely try the BI content to web page approach, but still want to see if I can do anything with the BI content. Also, it seems that I confused you both, so let me explain more clearly my structure. So, in the master report I have 2 joins with these columns: Product Category CASE WHEN END"Category Dimension"."Category" UNION Product Category CASE WHEN END"Category Dimension"."Category" and it shows me something like this: Product Category cucumbers vegetables peaches fruits and what's important here, I will click on the Product, not the Category to go to the detail report, but expect the Category to be passed to the detail. So my detail prompt would have an entry for "Category Dimension"."Category" and then my detail report would have this filter condition: "Category Dimension"."Category" is prompted Hope it is clear now.