OBIEE - Selecting column dynamically - part1, column selector

 OBIEE - Selecting column dynamically - part1, column selector

In OBIEE, in Analysis, we can use column selector to dynamically change a column in the analysis. Unfortunately, in complex cases, when you have to use the selected column few times, or you have some complex dependency between selected columns, we have to move the selection to prompts.

In the following 5 posts I will describe:

1. How to use column selectors. (this post)
2. How to use column names in prompts with presentation Variables.
3. How to use better looking column names in prompts.
4. How to remove column from analysis with column selector or prompt. 
5. How to create analysis with one prompt to cover multiple columns.

Lets start with column selector.

I have a simple analysis with "Per Name Year", "Product Type", "Revenue" and "Billed Quantity":

Now I want to let the user select other columns instead of "Product Type" and "Billed Quantity". Al I have to do is add column selector in OBIEE:

After pressing "Edit View" on Column Selector, I can edit it:

Mark the desired column and select other options from the list on the left.

Please note: 

• We can change both the column properties and function of each selection.
• The clear choices option.
• While we can place a non-measure column in a measure column selector it's not always a good idea ("Organization", that replaces "Billed Quantity", in my case).

Now the user can select a column. OBIEE actually replaces the column in the select statement, so the result is efficient. 

In the following case we replaced "Product Type" with "Department". 

But when I try and replace "Billed Quantity" with "Organization", I have problems in places that treat that column as measure, such as Pivot and Graph, but not in other, such as Table:

If we decide to add labels to columns:

The Label Position option becomes relevant:

Unmarking the automatically refresh option, lets the user select few options in column selector and then press OK.

One more logical result: if selected in column selector, the column is not controlled  via the Criteria:

If this is so good, why do I need other options? For complex cases.

I will talk about that in the next posts.