Categories
- All Categories
- 15 Oracle Analytics Sharing Center
- 14 Oracle Analytics Lounge
- 213 Oracle Analytics News
- 42 Oracle Analytics Videos
- 15.7K Oracle Analytics Forums
- 6.1K Oracle Analytics Idea Labs
- Oracle Analytics User Groups
- 78 Oracle Analytics Trainings
- 14 Oracle Analytics Data Visualizations Challenge
- Find Partners
- For Partners
Filtering in master-detail report. Not working with UNION !!!
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!
Answers
-
If your receiving prompt is a simple column prompt, and your filter is an "is prompted", it won't work with a union that has different formula in the Category fields.
If your receiving prompt creates a presentation variable, and your target query filters on that presentation variable, it should work.
0 -
Thank you for your reply, Jerry.
Well, the Category is exactly the same between 2 joins. It has no special formula, just a column reference, like "Category Dimension"."Category".
I don't use any presentation variables right now. I'm just trying to set up a prompt by the same "Category Dimension"."Category" in the detail dashboard. If I remove the join in the master report, it works, otherwise - no go.
0 -
Using UNION in analysis you prevent various things to work.
Your column on which you click isn't "Category Dimension"."Category" anymore, it's a 'virtual' column which has been generated by the union of the 2 subqueries.
I suspect that's the only reason why it stop working after you add the UNION.
Navigation in OBIEE sends values by strictly matching them with the original names, that's why your "Category Dimension"."Category" will automatically filter your detail analysis only if it has a filter on something with that same exact formula. You generally see that with columns having a more complex formula: they still send the values, but they match only if both formulas match perfectly (and this is where "save columns as" is useful as it make sure both formulas would be perfectly identical).
In your case the question is: what kind of formula does it match? Because it's maybe still sent to the detail analysis, just with a different name and not really sure on how you could even figure out what it is...
And obviously you also have no way to filter on that later on. You could maybe try to switch from navigate to BI content to web page and build the URL manually adding the filters as parameter in the URL: it would allow you to set what target column to send the value to and use a column of the master analysis as value.
It's clearly much more work than "navigate to BI content" doing everything automatically.
0 -
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 <statement 1> END "Category Dimension"."Category" UNION
Product
Category
CASE WHEN <statement 2> 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.
0 -
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
0 -
I see. Thank you, Gianni. I might try the building URl manually and see.
0
