Multiple Dynamic column selection

Selecting columns dynamically” saga, you can create a set of prompts where each prompt controls a column and enables the user to select the column formula or decide not to show it at all.

Something like this:

But the users might ask you to have a single prompt with friendly column names, in which they can select all the relevant columns for the analysis (or 2 prompts, one for attributes and one for measures).
Something like one of those 2:

This is what this post talks about.

What I intend to do?

1. Create the above prompt to allow the user to select desired columns.
2. Create a hidden set of prompts where I split the above prompt to column names and have additional hidden prompts for their actual formula.
3. Create an analysis where the column values are either the selected column or the None / Hidden  column from solution 2 of post 4 of this series.

As a result while user doesn’t select any columns, the analysis is mostly empty and as columns are selected it grows.

For example in my analysis there is only one default measure column, so all I see is:

But if I select few values in the prompt:

I get the following result of the same analysis:

How to achieve it?

To get such a list I will create a table of prompt values, in this case, column names. Since I was lazy, I created it by a set of “select from dual”, instead of actual table (don’t do it in real life):

With 4 columns:

• Column Name – A user friendly representation of the column (example ‘Year’)
• Column Code – The formal Column definition in Analysis formula (example ( “Time”.”T05 Per Name Year”)
• Column Type – In case I decide to split the prompt between attributes and measures and deal with special formats such as date.
• Column Sort  – This will allow me to control the selected values in prompt. (better explanation later in this post).

Next I created a specific Business Mode just for the Prompt table and use one of the options for single table Model described here.  In my case, I duplicated the table at the Business layer and defined the copy as Dimension (after adding a key). Later I used only the “Fact” part in the Subject Area (See presentation layer on the left, Business Model on the right):

With that, I can create a prompt based on the column col_name. It can be a check boxes:

Or  a choice list:

Now I have a Presentation Variable (P_col_list) with all the column “friendly” names I want.
It’s value might be something like this:’Company, Customer Name, Department’.
What I need is to find columns that are in that string and control how do I get them in some controlled method.

The naive approach is treating the P_col_list Variable as one long string and cut it to peaces. I don’t recommend it. The functions are long and unfriendly. It’s much easier to filter the column table by this presentation Variable and then select a specific row, using the Rank function (with different value of Rank for each final Column).
So I need to check for each potential column, it’s in the P_Col_list and select it’s rank to get them in an orderly manner.

To check if the column is in the P_col_list I will use the function Locate: LOCATE(“Prompt List”.”Col_name”,’@{P_col_list}’) .To get the Ranking I will use RANK(“Prompt List”.”col_sort”) . (The “col_sort’ column from above allows me to control the columns. I can set numeric values there and use them. You might notice my values of col_sort column goes from higher to lower. Since the Ranking goes, by default, from larger to smaller values, that is the way to control the order of columns.)

(There is another / better option instead of the Locate above. See P.S 2 why I didn’t use it)

For example, the first selected column formal column name is:

SELECT “Prompt List”.”Col_code” saw_0
FROM “ZZ_Prompt_list”
WHERE (LOCATE(“Prompt List”.”Col_name”,’@{P_col_list}’) > 0)
AND RANK(“Prompt List”.”col_sort”) = 1

and the first “friendly” name is:

SELECT “Prompt List”.”Col_name” saw_0
FROM “ZZ_Prompt_list”
WHERE (LOCATE(“Prompt List”.”Col_name”,’@{P_col_list}’) > 0)
AND RANK(“Prompt List”.”Col_sort”) = 1

In a similar way the second pair would be:

SELECT “Prompt List”.”Col_code” saw_0
FROM “ZZ_Prompt_list”
WHERE (LOCATE(“Prompt List”.”Col_name”,’@{P_col_list}’) > 0)
AND RANK(“Prompt List”.”col_sort”) = 2

SELECT “Prompt List”.”Col_name” saw_0
FROM “ZZ_Prompt_list”
WHERE (LOCATE(“Prompt List”.”Col_name”,’@{P_col_list}’) > 0)
AND RANK(“Prompt List”.”Col_sort”) = 2

So I decided that my case would have up to 5 dynamic columns (you can do as many as you like) and my second (soon to be hidden) prompt is, named one_list_actual:

Where P_C1A is the friendly Prompt name and P_C1 is the formal formula.

P_C1:

P_CA1:

The analysis would be:
1. A set of 5 columns with formula like:IFNULL(@{P_C1}, “None”.”None”) and Column Header like: @{P_C1A}:

2. The original ‘@{P_col_list}’ prompt, defined as hidden in the column properties. (I need it so the analysis “knows” that prompt changed values and refresh).
3. As many columns I want that would be constant in the analysis. It should be at least one measure from a fact for the system to always work properly. It’s “Base Facts”.”2- Billed Quantity” in my case.

This is how the criteria looks:

The last step i did was to put the 2 Dashboard Prompts and the analysis in a single dashboard and see it all works fine and then hide the second prompt (one_list_actual) with all the P_C… variables.

After selecting few values:

Last part is to hide the second prompt.I already explained this step in the post: OBIEE – Creating dependent – implicitly declared presentation variable: In Dashboard Properties, under Filters and Variables I’ll add the Dashboard Prompt one_list_actual.

and delete it from the dashboard.
this is how it looks with no values selected.

thi sis after selecting several values in the prompt:

You might want to split the Prompt that select column to 2 prompts, one for measures and one for attributes. The “Null” column for measures should probably be default as 0.0, to force it to be float.