In an analysis, what is the difference between a filter and "Keep Only"?

Question

OBIEE 11g.

I have been experimenting with parent-child hierarchies, and have encountered something interesting.

We have a parent-child hierarchy set up that shows Sponsor relationships. So for example

Agency A is a federal agency, which is at the top of the hierarchy.

Agency B, is a state agency, and a child of Agency A

Agency C is a county agency, and a child of Agency B

Agency D is a local agency, and is a child of Agency C.

Client E, is an actual client of Agency D, and is the fact level.

Agency A

|___Agency B

|___Agency C

|___Agency D

|___Client E

Here is my situation: If I filter Agency B for "Pennsylvania", I get the following message:

The layout of this view combined with the data, selections, drills, or prompt values chosen resulted in no data. Undo drills and view prompt values.

However, if I view the entire list of states, then select Pennsylvania, right click, and select "Keep Only",

then only Pennsylvania is displayed, and I can now drill down through county agencies, and then local agencies, without any issues.

So my question are:

1. What is the difference between doing a filter, and doing the Keep Only?

2. For anyone that uses a parent-child hierarchy, how do you allow users to initially select the value

of a specific level (eg: specifically selecting Pennsylvania) instead of viewing the entire list of all 50 states.

Thanks in advance fro any help anyone can offer.

Jerry S. · Answer

Thank you for the explanation and suggestions.

Gianni Ceresa · Answer

More in detail about the filter: if you set a filter which limits the content of the hierarchy (in your case "Agency B"), the hierarchy will be correctly limited to only the values matching that filter (so you see the parent of Agency B=Pennsylvania, Agency B=Pennsylvania and then only the children of Agency B=Pennsylvania).

You always need to use the preview when testing and drilling in the hierarchy or you end up with an XML giving you the message you first need to undo things.

If you already have it, start an analysis from scratch and avoid clicking in the result tab but do things with the menus and use the preview to check the result.

It's supposed to work just fine.

Gianni Ceresa · Answer

Hi,

A filter is set in the query to the source, the data will be filtered at the source. "Keep Only" is a selection step, it's just a visual (on screen) filter but the query returned all your data (a kind of pre / post query filter).

You better to not play/select/drill/click on the hierarchy while editing an analysis in the result tab directly because everything you do will add pieces in the XML and generate the issue you saw ("....undo drills ....").

You can have an hierarchy in a prompt and then mark your hierarchy as "prompted" (a checkbox in the selection steps for the hierarchy) and the hierarchy will automatically adapt to the prompt.