OBIEE - Selecting columns dynamically - part2, simple Prompt

 OBIEE - Selecting columns dynamically - part2, simple Prompt

In previous post I talked about column selectors. They are not enough in complex cases, for example: 

• Single selection of columns for several reports.
• Using the same column selection in several columns of the same analysis.
• Dependency between selection of columns (for example, I can select "Target Revenue" in measures only if "Year" selected in one of the dimensions.)

In this post I will create an example where selecting a column from prompt would influence 2 columns in analysis.

First I create a Dashboard Prompt:

All I want is to create a presentation variable, I will name P_col1, so I add a new Variable prompt (and not a Column Prompt).

There I will select Presentation Variable and it's name (P_col1) the Label and the user input format. Here you can select whatever you like, just make sure user can select only one value. I selected a "Radio Buttons" option. 

Next I add Custom Values. It is important that those Custom Values will be exactly the same way those columns appear in the column Formula. For example this is the Formula of the "Offices"."Department" column:

and this is the Prompt definition (I selected the Year, Product Type and Department columns):

 

The last thing I did was to select a specific value as default selection (this is important).

This is the resulting prompt:

Now i will create an Analysis that uses the above described presentation Variable and place them both in a Dashboard.

First I will create an equivalent to a column selector:

In the formula I create a presentation variable with a default value:

the result in my case is: @{P_col1}{"Time"."Per Name Year"}

I'll add few more columns:

And this is the result:

I could do exactly the same with column selector. Now, for fun,  lets add something I couldn't do with column selector: I'll add a measure that will show "Discount Amount" if the "Per Name Year" was selected and "Billed Quantity" otherwise. 

So my extra column will have the formula:

 CASE WHEN '@{P_col1}{"Time"."Per Name Year"}'='"Time"."Per Name Year"' THEN "Base Facts"."Discount Amount" ELSE "Base Facts"."Billed Quantity"  END

This part I couldn't do with column selectors.

Now I'll place both in a dashboard:

 You might have noticed the first column name didn't change after I selected the "Product Type". We didn't have this issue with column selectors. 

To fix it I'll place the Presentation Variable in the Column Heading:

Now the result header is dynamic:

While developers might be happy with the described above, users and UI / UX people might not share my enthusiasm with headers and Prompt Values such as "Time"."Per Name Year" and column header such as Measure2.

They tend to express their feelings:

How to make them happier? That would be covered in the next post.

    

OBIEE 11G Creating Column Selector and View Selector Views

 OBIEE 11G Creating Column Selector and View Selector Views

A Column Selector view adds a column selector to the results. A column selector is a drop-down list from which users can dynamically change the columns that display in results. This will allow you to analyze data along several dimensions. By changing the measure columns, you can dynamically alter the content of the analyses you have created.
To create a Column Selector and View Selector views, perform the following steps:

1 .	a. Open the Regional Revenue analysis in the Analysis Editor. The Results tabbed page appears. b. Click the New View icon and select Other Views > Column Selector.
2 .	The Column Selector view appears. Drag the Column Selector view above the Title view. Click the Edit View icon for the Column Selector view. The Column Selector editor appears.
3 .	a. Select the Include Selector C50 Region check box. b. In the Label (optional) Choices text box, enter Choose a column:. c. With Column still selected, double-click the following columns to add to the selector: P4 Brand, P3 LOB, and P2 Product Type. d. Click Done. The Compound Layout appears:
4 .	a. Click the Column Selector drop-down list and select P3 LOB: b. The values change appropriately. Note, however, that because you set a custom heading for the C50 Region column earlier, the custom heading is still displayed for the column. c. Save the analysis.
5 .	Now you will add the View Selector view. A View Selector view provides a drop-down list from which users can select a specific view of analysis results from among saved views. A View Selector view is analogous to a storage container, because it holds other views that have been selected in the editor for display. a. Perform these steps before adding the View Selector view: Delete the Title view from the Compound Layout. Set the Column Selector to display the C50 Region column, which is the default. Then delete the Column Selector view from the Compound Layout. Add a Graph view - Vertical Bar graph. These changes will allow you to showcase the analytic data-driven views. Regional Revenue should look like this:
6 .	a. Click the New View icon on the toolbar and select Other Views > View Selector. b. Drag the View Selector view to the right of the Table view. c. Click the Edit View icon for the View Selector view.
7 .	a. The View Selector editor appears.In the Caption text box, enter Choose a view:. b. In the Available Views list, select the Table and Graph views and click the shuttle icon to move them to the Views Included list. A preview appears at the bottom of the editor. Note that these views are data-driven views, unlike the Column Selector and Title views, which were deleted from the Compound Layout. c. Click Done.
8 .	The Compound Layout should look like this when the Graph view is selected: Do not save your changes to the analysis.

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.